May be the there is a very simple solution for my problem, but I am stuck on this for 2 days already. May be I had been looking at the wrong places...I would really appreciate any kinds of helps....feeling really frustrated right now.
You see, for my project at first I have to convert a MS Access table to SQL Server (2008). No matter how I am searching, I am only getting results for converting a MS Accss 'database' to a SQL 'database'. But our company already has an existing SQL database. I just want to convert one MS Access 'table' to SQL Server (2008) 'table'.
I found some very helpful tips for my problem in other threads posted here.
However, I found a very simple solution for my problem and that was....
Open the MA Access 2007
Find the table that you want to convert/merge from the left side Navigation bar
Select the Export-->Text File option by right clicking the table
After selecting where you want to save the file, leave the "Specify export option" blank as we DO NOT want to format the data
Select the "Delimited" option as export format
Select "Comma" as your delimiter, make text qualifier "None" and leave the 'Include fields name on first row' check box un-makred
in the next page pres the "Finish" button to complete the exporting
Then go to your SQL Database and create an empty table with all the fields that were in the original MS Access table. "Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted." -- Pinal Dave
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
On 2007 Access select External Data from the ribbon and in the Import section select more. From there select ODBC database and a wizard will pop-up.
Select from the wizard that you want to link the table and not import it and follow the steps (If you don't have an ODBC connection to your SQL you have to create one).
For the append query, create a query as you normally would - with or without the wizard - enter design mode, right click on the area that shows the tables (but not on a table ) and from on the menu that appears hover your cursor on query type. Select Append Query from the submenu and you'll be asked what table to insert the data.
PS: I just saw that Access 2007 has a move data wizard that transfers data to SQL server. It's Database Tools in the ribbon. And has the option to keep existing database.