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

Other SQL Server Topics
1

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?

2
  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?

3

Create a new sql dstabase.Then create tables and load data from access.
Rename access tables and at Access create links to tables at sql database.
Links must have same name that replaced tables.
Some details...
If you change table definition you must recreate /refresh link at access database.
Tables must have a unique primary key.
Create indexes it will improve performance.
Id fields (autonumeric) svould be defined as decimal instead int . And do not include id fields as primary key...you can create a unique index but not as primary key for those fields.
Be careful with fields type...some types,specially text or varchar (max) types may cause problems.
Hope this help.