Sql Server Data to a Jet Database

Ok.  This is another "not rocket science" blog.
But if/when you need it, it is good to know the syntax.

Basically, this will run a tsql query and dump the data into a jet database.  ("access database" is another way to say it, but I prefer to call the program "Microsoft Access" and then call the database file (.mdb) a jet database to make the distinction clear).

The syntax sugar is below.
However, one key thing to know is that you need to have a .mdb file created ~~before running the code.
Aka, you’ll need to manually make an .mdb and add tables/columns which matches your tsql-select query(ies).

The attached zip (zip file coming soon!) file give you the tsql code and a sample jet database (as a complete example).

Here are the key lines.

–Note, the … before "PersonTable" is there on purpose.
DELETE
FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0’,
    ‘Data Source=C:\WUWUTemp\JetDatabaseExportExample\person.mdb;’)
    …PersonTable

INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘C:\WUWUTemp\JetDatabaseExportExample\person.mdb’;’Admin’;”,
‘Select LastName , FirstName From PersonTable’)
SELECT LastName , FirstName
From dbo.SomeRealTable;

Advertisements
This entry was posted in Software Development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s