Reading data from access 97 into Sql

[steven.culshaw]

Hi all,

What is the best method to move data from an access database into sql server database with different table and field names?

If possible could you give me an example?

Thank you so much!

[sknake]

Use DTS or right click on your database and click "Import" in SQL Server Management Studio. I don't know if these options are available for the express/free version.

If that is the case then you need to recreate the tables in MSSQL by hand and write insert queries to move the data over.

[steven.culshaw]

Thank you for your reply.

My problem is it needs to be done programatically as the 97 database is still in use by legacy software. I have wrote some reporting software for this database which runs extremly slow when querying access so what I would like to do is upload the data to sql on the click of button so that the report can be done against the SQL database.

Thank you in advance.

[sknake]

Use an OleDbConnection to open a connection to the access database and bring the data forward in a C# DataTable then iterate through the table's rows and insert any new records, or update existing records, in the sql server by using an SqlConnection and SqlCommand . You can then execute the code on demand to sync the data or schedule it.

[steven.culshaw]

Perfect, Thank You!

[sknake]

You're welcome

Please mark this thread as solved if you have found a solution to your question and good luck!