Solved

How to insert a value into an AutoNumber column

Posted on 2014-03-07
8
2,927 Views
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...

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
0
Comment
Question by:Jim Horn
[X]
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
8 Comments
 
LVL 37

Assisted Solution

by:PatHartman
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.
0
 
LVL 75

Accepted Solution

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

mx
0
 
LVL 37

Assisted Solution

by:PatHartman
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.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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:

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
 
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 ?
0
 
LVL 20

Assisted Solution

by:clarkscott
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
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 120 total points
ID: 39916374
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.
0
 
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

739 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