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!
steven.culshaw 0 Newbie Poster
sknake 1,622 Senior Poster Featured Poster
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 0 Newbie Poster
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 1,622 Senior Poster Featured Poster
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 0 Newbie Poster
Perfect, Thank You!
sknake 1,622 Senior Poster Featured Poster
You're welcome
Please mark this thread as solved if you have found a solution to your question and good luck!
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.