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
LVL 35

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 - Access MVP) 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 35

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.
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 35

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

806 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