Moving data from SQL Server to MS Access mdb

Question

I need to transfer certain information from our SQL Server database to the MS Access database. I already have an access table structure structure. I am looking for a clean sql solution; Something that I could run directly from ssms and should not encode anything in C # or vb.

I know this is possible if I set up the odbc data source first. I am wondering if the odbc data source can be dispensed with?

Answer 1

If you want a “clean” SQL solution, my suggestion would be to connect from your SQL server to the Access database using OPENDATASOURCE .

You can then write your INSERT statements using T-SQL. It will look like this:

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=myDatabaseName.mdb')...[myTableName] (insert instructions here) 

The complexity of your INSERT will depend on the differences between SQL and ACCESS databases. If tables and fields have the same name, it will be very simple. If the models are different from each other, you may have to create certain queries in order to “format” your data before inserting them into your MS-Access tables and fields. But even if it gets complicated, it can be looked at using pure SQL.

Answer 2

Consider setting up your access database as a linked server in SQL Server. I found the instructions and sent them in response to another SO question . I have not tried them myself, so I don’t know what problems you will encounter.

But if you can bind Access db, I think you can execute the insert statement from SQL Server to add the selected SQL Server data to the Access table.

Answer 3

Here is a good solution for ur problem

http://www.codeproject.com/Articles/13128/Exporting-Data-from-SQL-to-Access-in-Mdb-File