CODEWITHSUNDEEP
sql-serverms-accessodbc
Brandon Moore
Brandon Moore

Reputation: 8790

Move data from SQL Server to MS Access mdb

I need to transfer certain information out of our SQL Server database into an MS Access database. I've already got the access table structure setup. I'm looking for a pure sql solution; something I could run straight from ssms and not have to code anything in c# or vb.

I know this is possible if I were to setup an odbc datasource first. I'm wondering if this is possible to do without the odbc datasource?

Upvotes: 4

Views: 10798

Answers (3)

Philippe Grondier
Philippe Grondier

Reputation: 11148

If you want a 'pure' SQL solution, my proposal would be to connect from your SQL server to your Access database making use of OPENDATASOURCE.

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

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

The complexity of your INSERTs will depend on the differences between SQL and ACCESS databases. If tables and fields have the same names, it will be very easy. If models are different, you might have to build specific queries in order to 'shape' your data, before being able to insert it into your MS-Access tables and fields. But even if it gets complex, it can be treated through 'pure SQL'.

Upvotes: 4

HansUp
HansUp

Reputation: 97131

Consider setting up your Access db as a linked server in SQL Server. I found instructions and posted them in an answer to another SO question. I haven't tried them myself, so don't know what challenges you may encounter.

But if you can link the Access db, I think you may then be able to execute an insert statement from within SQL Server to add your selected SQL Server data to the Access table.

Upvotes: 1

groovekiller
groovekiller

Reputation: 1122

Here's a nice solution for ur question

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

Upvotes: 0

Related Questions