For the past 3 days, I've been spilling my brains all over my keyboard in an effort to do something that should be ridiculously simple....
What's really driving me crazy is the fact that I've done it before...
Here it is:
I have a datatable in MS Access.
In an effort to copy all of it's records to a table in SQL that I can access and better manipulate, I created an SQL Database in my Visual Basic program and made a comparable SQL Table. I gave all the columns identical names and comparable data values as the columns in the Access table. Now, I just want to copy the records from the Access table directly into the new SQL table.
Here's the catch. I'm doing all of this through the DESIGN interface because the last line of code I typed was:
load "*",8,1
on a Commodore 64.
I remember when I did this before, it required some convoluted, bizarre process of querying and updating fields from the Show Table Data screen (atleast it seemed bizarre and convoluted given the simple task I am trying to accomplish).
At this point, I could have manually typed each of the 96 records into the SQL table 100 times over...
but that would be inefficient.
Both databases (the Access DB and the SQL DB I created in-program) are properly connected from my Visual Basic program. But every time I try to QUERY from my SQL table, it doesn't allow me to ADD the Access Table to the QUERY
I sincerely apologize if this post is not in the 'optimal' forum for the material I'm dealing with, but after 3 days of scouring ADO, ODBC, DOB, ODB, OLEDB help files and forum threads, I am certain my retinas will detach if I see another acronym....
I'm fairly certain I'm losing my mind....
Will Merydith
Use the upsizing wizard in Access to dump it on the Sql Server
Adamus
missbluebar
I don't know what your code looks like but if the column names are the same in both tables a simple INSERT query should work:
Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test Files\db10.mdb")
AccessConn.Open()
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes].[Table1] SELECT * FROM AccessTable", AccessConn)
AccessCommand.ExecuteNonQuery()
AccessConn.Close()
guilhermecvm94558
When you attempted to execute the code provided what was the error that was generated Can you post your version of the code
Damien fromOZ
HopeDreamsComeTrue
Hmm...well I'm still wondering what has happened with the several suggestions that have been offered
One way to quickly lose your mind is not to pay any attention to what anyone else says. ;-)
joseadolfo
Essentially, I should only have to do it once...
If the data transfers from the Access Table to the SQL Table properly, then I will be able to completely remove any connections to Access...
It has been my experience that using SQL Tables and Queries is far better (much more functionality) than using Access Tables and Queries in the Visual Basic/C# environments....
fmatias
I'm not sure if I can advise you as to where the code would be placed since I don't really know anything about your app. Is this a one time deal or do you need to perform the export repeatedly
If it's a one time deal just put the code behind a Command Button, run it and then delete the code and Command Button once it has finished.
You could also probably run it from the Access Query Designer as well.
DayTrader
*Whew*
Okay...new day....I took yesterday off and I'm ready to take this thing on again.
First of all, I apologize for not having updated you on the outcome of the suggestions.
While I'm sure they're both effective and will achieve the desired results, I have been unable to implement them effectively. I'll attempt to summarize the issue without burning the scroll wheels off your meese (meese is plural for mouse...ie. The building was infested with a herd of meese).
Regarding ahmedilyas's suggestion, it seems that I'm connected to both databases simultaneously and regardless of the window I attempt to do the Query in, I simply cannot get both the SOURCE TABLE (Access) and the TARGET TABLE (SQLExpress) to appear simultaneously in the QUERY Designer. If I save the QUERY of my SOURCE TABLE (Access) and then open the TARGET TABLE in SQL Express, the saved QUERY is unavailable (it simply lists the TABLES, VIEWS, FUNCTIONS, etc. and any Fill, GetData() commands specifically relating to the SQL Database).
I've even tried saving both Tables to a unique, standalone DataSet, but the same problem presents. If I try to override a QUERY and manually target the Table that isn't selectable, it always says 'Unable to parse text...' or something to that effect.
As for your suggestion Paul, I have yet to find a place to put that Code and get it to execute without throwing countless Exceptions or simply not doing anything at all.
I tried entering it as a STORED PROCEDURE. Then I tried it as a FUNCTION. Then I read your follow-up suggestion and put it on a temporary button in a throw-away form I made just for the purpose of executing it.
No dice...
As far as the attempt to attach it to the Form, it's likely that I don't have the proper TableAdapters set up or something. When it comes to these DataBindings, DataSets, BindingNavigators, and BindingSources, I get a bit befuddled (I'm in a gray area where I 'kind of' understand the need and functionality of each of them but not well enough to avoid getting them constantly mixed up in my head).
Am I correct in assuming that even if I do ultimately get it coded correctly, I will have to execute the built program for the data to transfer and save in my TARGET TABLE, or is simply Building the Solution all that will be necessary
(Btw, I also tried entering the code in SQL Panes and Queries. Of course, as anticipated, that freaks the whole computer out)
oivay...
Anyhoo, as I stated earlier, it's a new day, and I'm just planning on manually typing EACH record from the Access Table into the SQL Table. That should eliminate any pressure to figure out how I got this to work 6 months ago (although, I'm sure it will bother me for the rest of my life).
Again, allow me to apologize for the delay on the update...
I simply had to get away from the computer for a while (I was on the verge of throwing it out the window when I was typing that last post...oddly enough, typing the post itself kinda gave me a break from the seemingly endless stream of useless Help files).
I really do appreciate all your time and patience with my retarded self...
WV John
here is a thought:
you can develop and application in VB/C# which will:
Make sure that the schema is the same on SQL as it is on Access (field names, relationships, data types, data lengths etc...)
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.insertcommand.aspx
does this help/shed some light
KarlShare
project2n5e0o1
Paul,
Here's a total newb question for you....
Regarding your recommendation that I use the following QUERY:
...where exactly would I punch this in
All the manual QUERYS I've ever done are strictly SQL syntax....
I fully understand every line of the code that you've posted, it's just that, it seems to me that this code would be useless after the QUERY (and INSERT) had completed, so it doesn't seem logical to include in in the body of the program itself, as it would be a pointless operation after the first execution of the program...
(btw, I don't think I mentioned that the data I'll be accessing will be read-only...from the terminology, it seems a VIEW would be more practical than a TABLE persay, but a VIEW seems comparable to mirrors reflecting mirrors in Galileo's telescope ... nothing more than high tech sorcery...
...sorcery, I say!)
ajay_dekavadiya
okay...yep...it's official...I've lost my mind.
It's gone.
I'm cancelling my membership to MENSA because every sentence of the 'official help documentation' that I have read has reduced my IQ by 2 points. That means my IQ is now at -10,045,BB7,P10,537.
4 days.
6 to 8 hours a day, and I'm still trying to migrate one little table's data from MS Access over to SQL.
After a seemingly endless stream of redundant, useless help documentation, a thought occured to me:
What the *%!@ is wrong with Copy & Paste
Why doesn't it just let me CTRL+C all the records on my Access Table and then open the SQL Table and CTRL+V them right into it *
For all the advances mankind has made in computer technology, the reality that we have actually regressed is mocking me from the corner of my room, which, by the way, has grown much smaller over the past 96 hours....
The last time I did this (atleast 6 months ago), I remember it took about 3 days of scouring help files to track down the method for doing it. That's what makes my current search so maddening...I KNOW the explanation is out there. I've used it before. And it worked perfectly and accomplished exactly what had to be accomplished on the first go. All I remember was that it involved selecting the INSERT or UPDATE View from the SQL Query Design Pane. I could then add the 2 tables to the pane, select the column headers from the drop-down boxes, and click 'EXECUTE SQL'. And BAM...it happened.
The problem that I seem to be having now is that I can only get 1 of the 2 tables to appear as selectable at any given time.
I even tried making Derived Tables from the existing ones in a new DataSet to insure they both existed in the same place.... but it still only allows me to select one of the two tables....
Perhaps... maybe, just maybe, someone could give me directions to the cave where the crumbling scroll with the trick to doing this is located.
...Sorry if I'm rambling...but it's just so hot in here....
the butter in my pocket is melting....
I know! I'll try another search of the help files and this time, I'll put SQL before Access in the search string....that'll surely turn something up, right
k10wn
*I actually just tried to Copy & Paste the records (again), this time it copied 105 of the 107 and said it couldn't access the last one (but it appears to me that it couldn't access the last 2).
So I then highlighted all the records and clicked 'DELETE' and it threw this at me:
No rows were deleted.
A problem occurred attempting to delete row 2.
Error Source: Microsoft.VisualStudio.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(3 rows).
Correct the errors and attempt to delete the row again or press ESC to cancel the changes.
Tee-hee-heee...before the paste, I deleted the unique indexes and key restraints from the table I was pasting to because it complained about them in my previous Copy/Paste attempts....tee-he...hehe...ha...heehee they're coming to take me away heehee...they're coming to take me away...
P.S. I like toast. Sometimes I make toast and some of it burns and I throw it to the birds and they look at me and I run away.
Mohan1
Thanks for the input guys....
As far as how my code looks, it looks like whatever the code looks like when Visual Basic generates it from the DESIGN environment...as I mentioned, I avoid hand-coding at all costs....
Both of the suggestions here sound vaguely familiar. I'm tempted to believe one of them is the method I used to accomplish this six months ago...however, in the past four days, I've tried UPDATE and INSERT queries from just about every screen in the designer (an INSERT QUERY from one window (ie. the DESIGN window) in the program does something totally different from an INSERT QUERY in the window right next to it (ie. the Database Explorer))...
I will walk through both of these suggestions meticulously and cross my fingers....
scratch that...I won't cross my fingers (they're too numb from the Carpal Tunnel Syndrome that's set in since I began trying to accomplish this)...
smargroth