How to insert a value into an AutoNumber column

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...

Customers-Main.mdb
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 Customers.id.

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.
Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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.

mx
0
 
PatHartmanConnect With a Mentor Commented:
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.
0
 
PatHartmanConnect With a Mentor Commented:
The append query works because without it, conversions would be a nightmare.  It is not likely that future versions would stop supporting it.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can INSERT into an AutoNumber field, assuming that INSERT does not conflict with existing Autonumbers. For example:

Table1:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Pretty much the same thing as the SQL I posted, right ?
0
 
clarkscottConnect With a Mentor Commented:
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
0
 
PatHartmanConnect With a Mentor Commented:
clarkscott,
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.
1
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.