Sql – Creating a sql dump of Microsoft Access tables

As well as the SSIS option, you can link directly to an access DB in SQL Server as if the database was any other linked database. You can use this to copy data accross using a task scheduled by the SQL Server Agent or some other task runner. For instance a simple "completely refresh one table from the Access DB" job might look like:

EXEC sp_addlinkedserver
   @server = 'mdb',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'c:\path\to\db\db.mdb'
GO
DELETE table_to_refresh
INSERT table_to_refresh SELECT * FROM mdb...table_to_refresh_from
GO
EXEC sp_dropserver 'mdb', NULL
GO

I've used this as a quick way to copy data from an Access DB when other tools like SSIS were not available. You can use the tables (and, I assume, views) in the DB just like any other, so you are not limited to simple "select all" queries as the example above.

In SQL Server Management Studio right-click your database and select Tasks / Generate Scripts. Follow the wizard and you'll get a script that recreates the data structure in the correct order according to foreign keys. On the wizard step titled "Set Scripting Options" choose "Advanced" and modify the "Types of data to script" option to "Schema and data"

TIP: In the final step select "Script to a New Query Window", it'll work much faster that way.