Appending AutoNumber Field

This tip Appending Autonumber Field is suitable for all versions of Microsoft Access.

This tip explains how to append data from another table or data source to an autonumber field in your Microsoft Access database.

There may be times when you need to import numbers (they maybe of data type AutoNumber or number field) into an AutoNumber field which is also (and usually) your primary key.

This may be the case if, for example, you started assigning client ID numbers which you use consistently in your business in an Access database, but you have built a new Microsoft Access database in which you wish to import these same ClientID’s, and then wish to continue the AutoNumber (primary key) from where the final number of your old system was.

Following so far?

You would best do this using an Append query; ie. using a query to append data from one table or data source to another table. (not sure how to create an Append Query, then see the link below)

When you try to run the query you may find you have trouble appending into an AutoNumber field, especially if it is your primary key field for that specific table.

You may be tempted to change the field type to Number and change it back after the append query, but alas, you cannot change to an AutoNumber data type once you have entered data.

The secret is to temporarily un-assign the autonumber as the primary key. This allows Appending AutoNumber Field to your records without any trouble (just make sure you do not have any duplicates when appending, otherwise when you re-assign the primary key you will get an error).

If you do not know how to create an Append Query then try this tip: Append Query

Return from Appending Autonumber field to Simply Access Home Page

or

Return to Microsoft Access Tables