Copy data from Access to SQL

Question

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?

Answer 1

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.

Answer 2

Not that you or I have any say on the matter but I'd push for getting the Access app upsized to SQL Server. Then develop just the functionality required for Web access which may not be a lot depending on the requirements. Leave as much functionality in the Access app as possible simply because it's a better RAD tool.

Answer 3

You may use SSIS packages and save them to disk. See http://msdn.microsoft.com/en-us/library/ms141209.aspx as a starting point and also check out http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx

Answer 4

Another easy way to import data from a MS Access source is to import the data directly through the management studio. Right click the database > Tasks > Import.

I found this worked a lot easier than using SSIS or the migration tool.

Answer 5

Access to MSSQL might be useful: http://www.bullzip.com/products/a2s/info.php