Move data from SQL Server to MS Access mdb

Question

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?

Solution 1

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'.

Solution 2

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.

Comments

[Brandon Moore]

Oh, I wish you had answered before I already implemented the linked server solution. I'll probably switch to this method though since it's one less point of failure on not having to worry if the linked server was created or not.

[Brandon Moore]

+1, but looks like I might have lied about marking yours the answer... hope you'll forgive me :) I have to try Philippe's solution out since it doens't require setting up a linked server at all. But fyi I did already implement this and it worked great!

[HansUp]

No worries here, Brandon. I liked that one, too. I actually tried that one once, but don't recall clearly. There may have been an issue about trusting OPENDATASOURCE. You'll figure it out. :-)

[Fionnuala]

This can get quite complicated if you move to Access 2010 and / or you have a mixed 64bit / 32bit environment. Also you will probably need to allow Ad Hoc Distributed Queries and a couple of other things. Otherwise, it is quite good fun.

[carveone]

#remou you are right about this 'had oc distributed queries' parameter to update. I forgot it , but it's a single straightforward TSQL statement , and the error message you get from the server is explicit.