Sql – Copy data from Access to SQL

microsoft accessmigrationsql

I have a an access DB that is to be eventually be replaced by a SQL DB with a web front end. The web application is currently in development. I have copied the Access DB to SQL using the Migration Assistant, unfortunately we can't just link the Access front-end to the SQL DB and use only the SQL DB, they must remain separate until we can switch from one to the other. Therefore there is data being added to the Access DB that I need to regularly copy to the SQL db.

Is there a script or easy process that will copy just the data from the Access tables to the SQL ones, that I can run as a scheduled task once a week?

Best Answer

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.

Related Topic