Database export Access to SQL server: indexes and keys?

Question

I have exported a number of Microsoft Access database tables to a SQL Server 2012 using ODBC. Subsequently, I have linked to the data sources by creating linked tables.

Now here's the issue.

When I verify the tables in SQL Server itself, I notice only the database tables, columns and their respective datatypes are present. There are no key or indexes to be found. Still, in my Access database they were all defined. Also, I noticed that Access requests to choose a field(s) that uniquele identify each record to ensure data integrity and to update records. These then become the Primary Keys I understand, but why not use the PK that are already present?

What would be the easiest and most efficient way to also migrate the other field properties like indexes, keys, constraints? As otherwise, I would need to define all those manually and this would be very time-intensive.

Many thanks for your help!

Answer 1

As you have discovered, the keys and indexes and not copied over if you simply export an Access table to SQL Server using External Data > Export > More > ODBC Database:

However, the indexes, keys, and relationships are copied over to SQL Server if you use the "Upsizing Wizard", which is invoked via Database Tools > Move Data > SQL Server:

Note: The "Upsizing Wizard" was removed from Access 2013, so users of Access 2013 (and newer) will need to use the "SQL Server Migration Assistant for Access" instead. For more information look here.