Access to Sql Server, how to keep autonumber

[valeryk2000]

Existing Access database contains a lot of data. I need to upsize all tables to SQL Server. Many Access tables have autonumber field. When I use DTS it would drop autonumber, e.g. it would keep the values of this field but in SQL Server it would not keep the Identity (autonumber) property. If I change it in Edit SQL window poped-up by clicking Edit Mapping... button in DTS it will renumber existing numbers. Question: how to keep both current values and autonumber/identity property?

 

[boblarson]

Don't use DTS. You can either use the SQL Upsize tool in Access or better still use the SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/sql/solutions/migration/access/default.mspx

 

[giovaanni]

or you could do it manually.

 

[valeryk2000]

Bob,
did you use SSMA? I tried and had a wierd experience (may be I did not try long enough and did something wrong). It made changes to the original Access mdb - I was lucky to have a copy.
What I want to try:
1) setting identity in destination table using seed as max(ID)+1 and increment 1 - for example, Identity(1894,1), or
2) make this field not identity, but to create an insert trigger that would assign a Max(ID)+1 to a new record, and make it a primary key.
1) - before and 2) - after the Access table is imported.