Convert a MS Access table into SQL Server 2008

[Walahh]

Hello,

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'.

Any suggestions, please? Thank you in advance!!!!

[Walahh]

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

BULK
INSERT YOUR_TABLE_NAME
FROM 'c:\YOUR_FILE_NAME_WITH_FULL_PATH.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Hope this helps someone :)

[adam_k]

If you create the table manually, then it is much easier to link that table to Access and create an Append Query. This way doesn't need export or bulk insert.

[Walahh]

I am new here, so don't have much knowledge about database either. If you don't mind, can you please tell me how I can link the SQL (server 2008) table to Access and create an Append Query?

I think I might have seen an article on how to link SQL to Access....but they were linking the whole SQL database...i only have to link one sql table to another Access table.

Thank you!!

[adam_k]

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.