Microsoft Access to SQL Server - synchronization

Question

I have a client that uses a point-of-sale solution involving an Access database for its back-end storage. I am trying to provide this client with a service that involves, for SLA reasons, the need to copy parts of this Access database into tables in my own database server which runs SQL Server 2008. I need to do this on a periodic basis, probably about 5 times a day.

Is there an easy programmatic way to do this, or an available tool? I don't want to handcraft what I assume is a relatively common task.

I am running this on SQL Azure, so there's no way for me to run prepackaged software on the server. It would either have to be open source and portable to Azure or executable on the client's computer.

I'm unfortunately thinking I'm going to have to roll my own tool to do this. Any suggestions or more tools that are out there that can do this themselves before I go ahead?

Answer 1

There are two things to consider:

• connectivity
• ETL tool

For connectivity, you will need to establish VPN tunnel of some sort between the client server and your server.

Then use SSIS to connect to MS Access, to create packages to pull data from MS Access to SQL Server database. On SQL Server, you will need to create new schema, to mirror or be close MS Access

On connectivity side, another option - since MS Access db is in the file, you may be able to FTP the file to your server and point SSIS to the file

Answer 2

David, I looked at multiple solution for a similar problem: converting from dbf to mysql, here are 3 solutions (all commercial - but relatively inexpensive) that can work for you:

• Full Convert
• SQL Manager
• ESF

Other than that I couldn't find a good robust data conversion tool that would be open source or free. At least not for DBF to MySQL conversion. There might be something out there for SQL/Access. You could roll out your own solution, but is it worth your time?

DISCLOSURE: I ended up using Full Convert.

Also all of these products generate some sort of batch file, that can be scheduled using Task Manager.