Identity Insert in Access

October 17, 2007
Keywords

Equivalent of Identity Insert in Access
AutoNumber Insert
Access 2007

Problem

You are copying rows from one table in Access to another. The destination table has an AutoNumber column which ignores the ids from your source table. You need Identity Insert, as in SQL Server, but where is that option?!

Solution

Don't copy and paste! Use an Append Query. (In Access 2007, look under Create -> Query Design) The Append Query will respect your ids.