SQLTeam.com | Weblogs | Forums

Best way to migrate Access 2010 database to SQL Server Express 2016


I have an Access 2010 database that has forms and reports. I have been tasked with migrating the data over to a SQL Server 2016 database. I would like to maintain all the data entry forms and VBA code in Access as a front end data entry point. What is the best way to do this?

I tried creating the links using the SQL Server option under Database Tools in Access. The forms were not linked to the SQL database (for linking or creating a new project)

I tried the SQL Server Import/Export tool. It did not retain the link to the Access table.

I tried the Microsoft SQL Server Migration Assistant for Access tool but got the 32/64 bit error. (Access is 32 bit. The MS tool is 64.) Ran the Microsoft Access Database Engine 2010 Redistributable file but it did not fix the problem so I could not use the tool.

Am I trying to do something that can not be done and I will have to recreate the forms? Will the underlying VBA code still work?

This is all new to me and I am trying to get my head around it all but honestly I have been struggling with this for two days and still have no idea of what I am doing, or how to do accomplish this.

Suggestions on the best approach anyone?

  1. can you try linked server and openquery to insert into destination or staging tables
    2.;ssis package to stage data to a staging db
    then use tsql to move to destination tables

how many tables?