Import MS Access data to Sql Server via code

Asked

Viewed 648 times

1

I need to import data that is in an Access database via Sql Server code. My software that is made in C# for many years has been integrated into an Access database. From now on we are migrating to Sql Server and the intention is that when upgrading to the next version the system will only work with Sql Server. I think in principle to create an SQL database with the same names of tables and columns and by software to read the Access tables and save the data in the Sql database. Is there any other more practical way to do this via code?

I’ve been researching and found only tools ready, but nothing via programming.

c# sql-server winforms ms-access tsql

  • Your intention is to actually do only via code?

  • Yes! Because there is already a user base using the old software with Access database. The intention is that in the update to the next version the system will only work with Sql Server and we have no way to access machine by machine to migrate from database.

  • You can use Access as the front end for SQL Server; see linked table. Or, in T-SQL programming, you can open the Access tables directly in SQL Server. There are several options, via code.

  • @Josédiz, in this case would not have to access machine by machine of each user and install the Sqlserver to make the migration? The intention is that by the software, in the form created by visual studio, when updating the software to the new version, the user can even make the migration to the new database just by clicking on a button Ok, for example.

1 answer

0

What I would do, imagining that your application is local: Create a stored synchronization database in the SQL database and a Linkedserver for the Access database.

In this Stored Procedure would synchronize the tables taking from your access and playing inside SQL, and this Procedure would be called through your C application#.

Example of creating Linkedserver for Access in SQL Server.

exec sp_addlinkedserver @server='Access',
@srvproduct='Access',
@provider='Microsoft.Jet.OLEDB.4.0',
@datasrc='c:\test.mdb'

Browser other questions tagged c# sql-server winforms ms-access tsql

You are not signed in. Login or sign up in order to post.