Solved

How to insert a value into an AutoNumber column

Posted on 2014-03-07
8
3,011 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 85

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 66

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

Technology Partners: 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

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.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

691 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