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!

Gord Thompson · Accepted Answer · 2013-12-10 10:18:34Z

8

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.

edited Dec 10, 2013 at 10:18

answered Dec 10, 2013 at 8:43

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kris Van den Bergh Over a year ago

Hi Gord, thanks for guiding me here! I notice the SQL Server button is not present under the "move data" section for me. I'm on Access 2013... Any workaround for this?

Collectives™ on Stack Overflow

Database export Access to SQL server: indexes and keys?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related