SQLTeam.com | Weblogs | Forums

Migrate access latest version to sql 2017

Good evening, I'm trying to migrate a db access into sql. I used the procedure import data that I find inside activity by right clicking with the mouse in the name of the db.

My question is: does it only import mdb files? Can't import newer login versions?

I have a newer access version and saving it in mdb loses some things.

In addition, when I do this procedure, I have no way to import the keys and indexes too?

Thank you.

It's been a while since I used MS Access, but it used to have an Upsizing wizard to migrate a database to SQL Server. Did you use such a thing, or did you use an ETL/SSIS type of migration? The latter would just move data, the upsizing wizard would also include indexes and keys/constraints.

Logins/users may or may not be migrated with the wizard, and it may not be worth migrating them unless you have very few. The security and permissions model in SQL Server is a bit different than Access, not everything will migrate properly.

I used the data import procedure that is inside SQL by selecting the database. But it didn't bring me the keys, indexes and constraints

Seems like Microsoft is focusing on the SQL Server Migration Assistant (SSMA) for these kinds of things now:

There are multiple links within that page that have more details. SSMA is a download package/executable and has a wizard like UI where you can select what you want to migrate.

You may need to drop the SQL Server database you already imported, or drop all the tables in it, for the migration to work. It's possible that it would import the data again and you'd have duplicates. Either that, or use SSMA to migrate to a new SQL Server database (my recommendation). Afterwards you can decide what to do with the data you migrated earlier.