We help IT Professionals succeed at work.

Access Append Table data with same AutoNumber values

Murray Brown
Murray Brown used Ask the Experts™

I have a table that I need to transfer data to from another table. The key values held in an AutoNumber column need to stay the same as the source table. How do I do this?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer
In a few cases i needed that i just made append queries and use them to fill the table
e.g. a table running from 1 - 10
it has
I would reset autonumber and delete the 1st entry...append from 2-4...delete 5 ..append 6..delete 7 append 8 -10
There should be a better way but if not it would be easy to code it.
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
You can insert to an Autonumber field just like any other field. Of course, if the AutoNumber field is also has a unique index you have to be sure you're not duplicating a value in that field.
Most Valuable Expert 2015
Distinguished Expert 2018
An append query is the only method.

That said, using an AutoNumber field is not right here, as you exactly don't want an autonumber. So, modify it to Long and - if you append records from other sources, adjust the to include a value for the Id that won't collide with the Ids you expect to append from the other table.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

If you want that AutoNumber field to continue to increment automatically AFTER the append (i.e. there are going to be other, new records inserted into ti) then the AutoNumber is the right data type.
Mark EdwardsChief Technology Officer
First of all, one of the characteristics of an autonumber field is that each value must be unique  - there can be no duplicate values.  (However, there can be missing values.)
A characteristic of a key is that it can not have duplicate values.
A second characteristic of an autonumber is that the field increments the value by 1 when a new record is appended WITHOUT a value for the autonumber field included in the append.
If a value is included in the append, and that value already exists, you will can an error.
If a value is included in the append, and that value is missing in the autonumber field, no problem - the record will append.
If you INSISTS on appending records with autonumber values in them into another table with autonumbers, and it would create duplicate values in the autonumber field, then you MUST change the autonumber field to a Long Integer number field, which does NOT have a unique index on it which would allow it to accept duplicate values.

Trying to keep it an autonumber field with duplicate values and with the characteristic of a key field is just nonsense - you're going to have to do some restructuring, like it or not.  In short, you've painted yourself into a corner with this one.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

As long as you know the values would not duplicate one already in the field, you can safely append records to the table, and you can safely write values to the Autonumber field.

A simple query would tell you this:

SELECT * FROM YourDestinationTable WHERE YourAutoNumberField IN (SELECT YourAutoNumberField FROM YourSourceTable)

If that query returns no results, you're safe to insert data from YourSourceTable into YourDestinationTable. If you do return results, you'd have to take steps to handle this issue.

I do a LOT of data imports, and we often insert to Autonumber or Identity fields. It's a common practice, and as long as you take (very simple) steps to ensure you're not going to end up with duplicate values, you're fine.
Distinguished Expert 2017
Typically I use this method when archiving data.  That way if I have to restore it, I can restore it with the original autonumber.  There may be other reasons for doing this but I haven't run into any.

Gus' suggestion to use an append query is the only way you can insert data to a table with an existing value included for the autonumber and as the others have said, NO DUPLICATES are allowed.

Do this first on a copy of the table so if something goes wrong, you don't end up messing up your table.  Or, as Scott suggested, run a select query to find any potential duplicates before running the append query.  Scott suggested a query with a subquery.  I prefer a simple inner join which is easier to build using QBE.
Murray BrownASP.net/VBA/VSTO Developer


Thanks for the advice
Distinguished Expert 2017

I didn't expect my comment to be accepted as the "solution" and it shouldn't be.  I referenced Gus' response as THE solution.  Awarding EVERYONE with the "solution" doesn't help anyone who finds this thread later.  Most of us are from a generation that didn't expect a trophy for simply participating so we are not offended if  you think someone else provided a better option.  "helpful" awards are always appreciated.

Just to summarize, the actual solution is to run an append query which is what Gus said.  The rest of us were either embellishing that or were just plain out in left field and shouldn't have gotten any award at all.