Export data from SQL Server 2005 Express to Microsoft Access

SQL Server 2005 Express is the best free edition of SQL that Microsoft has ever released. That being said, the few features it omits from the Standard version can be a source of frustration. For example, I wanted to setup an automated task to dump a SQL table into a MS Access database table every night. I realize that Express doesn't have a job agent, no big deal. We can use Task Scheduler for that. But how to move the data?

After experimenting, I found a great solution. I created a new "Linked Server" pointing to the Access database, then did a simple INSERT INTO / SELECT FROM statement in a stored procedure. Here are the basic steps:

1. Create a new Linked Server

Open Management Studio Express. Expand "Server Objects", then "Linked Servers". Right-click "Linked Servers" and pick "New Linked Server". Give it any name you want at the top, let's say TEST_MDB for sake of example. Provider should be "Microsoft Jet 4.0 OLE DB Provider". Product name is "Access". Data source should be the full path to the .mdb file. Leave Provider String empty. (Note: i think that's where you'd set user name and password if your mdb file is protected). Click Ok. If you refresh your Linked Server list, it should be there now.

2. The query syntax . . .

When referring to a table in your Access database, use the following syntax. Assuming a linked server name of TEST_MDB and a table name of Customers it would be:

TEST_MDB...Customers

Those three dots are very important.

You should now be able to write any query you want against that database. INSERT, SELECT, UPDATE, DELETE, whatever. Joins work as you would expect with a local table.

Posted under the influence of [[Ulver :: Untitled II]]