How to insert a value into an AutoNumber column

Posted on 2014-03-07
Last Modified: 2014-10-31
Hi all

Q:  Is there a way to insert rows into a table with specific values to insert into the AutoNumber field?

aka, what's the Access equivalent of SQL Server's SET IDENTITY_INSERT OFF?

Reason I ask is because I'm working on an Access automated archive/restore functionality.  So say I have the below data...

id     name   date 
1      Bob    1/1/2005
2      Jack   6/1/2005
3      Fred   1/1/2007
4      Jerry  12/31/2010

Open in new window

Not shown are bunches of other tables with a foreign key of

My VBA code will move any rows with the date column less than a specific amount into an 'archive' database, schema matches but the AutoNumber columns are just Longs.  So, if I run my function with a date of 1/1/2006, my table now looks like this:
Customers-Main.mdb           Customers-Archive.mdb
id     name   date           id     name     date 
                             1      Bob      1/1/2005
                             2      Jack     6/1/2005
3      Fred   1/1/2007
4      Jerry  12/31/2010

Open in new window

All is well.  BUT, I'd like to also be able to do a restore as well, meaning in the above example if I restore all rows beginning 1/1/2015, the id=1 and 2 rows move back to the Main.mdb, and because of the foreign key rows I need to specifically insert the values 1 and 2 into the AutoNumber ID field.

Thanks in advance.
Question by:Jim Horn
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 36

Assisted Solution

PatHartman earned 120 total points
ID: 39912974
The only way to get a value into an autonumber column is with an append query.  Create a query that selects the rows from your archive table and appends them (including the autonumber) to the current table.  You cannot do it any other way.  Jet/ACE don't have the equivalent of Identity insert on/off because it is always allowed but only in an append query.
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 190 total points
ID: 39913087
Also, IF ... you wanted to fill in a missing Autonumber (EmpID)  ... for example  ...18,19,21,22 ... and you wanted to add back in 20 ... you can do this:

INSERT INTO tblEmp ( EmpID, EmpName ) SELECT 20 AS Expr1, "JAMMER" AS Expr2

As I see it, this only works because of a long standing bug in the AN .... since in theory ... and AutoNumber cannot be reused :-)

So ... no guarantee it will work in future versions. It still works as of A2013.

LVL 36

Assisted Solution

PatHartman earned 120 total points
ID: 39913489
The append query works because without it, conversions would be a nightmare.  It is not likely that future versions would stop supporting it.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 39914687
You can INSERT into an AutoNumber field, assuming that INSERT does not conflict with existing Autonumbers. For example:

ID (AutoNumber, PK)
Field1 (Text)

If I use this code:

CurrentDb.Execute "INSERT INTO Table1(ID,Field1) VALUES(95,'scott')"

A record with an ID value of 95, and Field1 value of scott is inserted.
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 190 total points
ID: 39914910
Pretty much the same thing as the SQL I posted, right ?
LVL 20

Assisted Solution

clarkscott earned 40 total points
ID: 39915808
Why would you want to do this?
An autonumber has a very specific purpose.  You don't ever want to use it as a "count of records" or any other purpose than to produce a unique primary key.

If you want to do something else - create another field for it.

Scott C
LVL 36

Assisted Solution

PatHartman earned 120 total points
ID: 39916374
The reason you would want to maintain an autonumber is to simplify a conversion.  If you cannot maintain the original autonumber, you must expend extra effort to convert dependent tables and the more levels you have, the more complex the conversion becomes.  In this case, the OP was asking because he wanted to restore some archived data.

If there are no dependent tables, there is no reason to make any effort to retain the original autonumber.
LVL 65

Author Closing Comment

by:Jim Horn
ID: 40415654
Guys - I'm not at this gig anymore, and remember that being able to insert with #'s previously deleted worked fine, so I'll spread the wealth and close the question.

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question