How to Migrate ODBC Connections from Access to SQL Database

Posted by · October 30, 2013 8:38 am

accepted solutionWhen Anthony6890 wanted to migrate his Access front-end, IBM back-end database to SQL Server, he knew he needed help. He didn’t have much experience using SQL Server directly and wasn’t sure how to handle the links from Access to IBM and SQL.

So, Anthony turned to the Access and SQL Server experts on Experts Exchange, asking them for an answer to his question as well as a few resources to give him a better understanding of the migration process overall.

The first expert to chime in was Access Ace and SQL Server Genius jimhorn:

“Are you going to continue to use Access as a front-end application? The reason I ask is because SQL Server does have linked servers, but Database Administrators  in many companies discourage using this.”

Anthony wanted to continue to use Access for the front-end, so Jim supplied him with information on importing all of his local tables into SQL Server, then re-linking them into his Access app and leaving the linked tables to the non-SQL Server sources alone.  Anthony was also provided a list of benefits to having his data in SQL Server.

Access Prodigy LSMConsulting also shared his knowledge based on years of experience with data migrations, confirming that Access can support linked tables from multiple sources in the same file, and provided further information about modifications that need to be made during the migration and including resource links to help Anthony learn more about the migration process.

For exclusive access to over 3 million technology solutions–including hundreds of thousands of Access and SQL Server solutions–join the Experts Exchange community today!