SQL-Server - Microsoft Access (linked server)

Question

I am trying to import tables and data from a Microsoft Access database into SQL server, but I have a problem:

At the company where I work, they use an Access Application to store data in an Access database. I would like to automatically import the data from this database in SQL Server without reprogramming the Access application. (I didn't build it).

I think there are a few ways to do this, but neither one of them really worked:

Creating linked tables using the Microsoft Migration Assistant disables me to edit or add new tables or columns (without rerunning the Assistant each time I made an adjustment.

Create a data import and schedule this is not working, because SQL Server keeps saying that I have to upgrade to SQL Server standard, developer etc version. (I also think that this is not the right way to do this.)

Create a linked server to my Access database, but in this situation I can't figure out which ODBC driver, bit version and rights I have to use. (I keep getting errormessages)

I think the linked server option is the best option, but maybe you guys have some other ideas? Or maybe does someone has experience importing Access db data? Please let me know!

Tom Smits

Answer 1

The SQL Server Migration Assistant (SSMA) is the recommended tool.

You'll find a link for download of the Access version at that page.

It's a comprehensive tool, so you don't face a 15 minutes task, but it will handle everything you can imagine.