INSERT INTO linked table with AutoNumber resets counter

[David L]

In Access 2003 SP2, when a record in a linked table that has an
incrementing AutoNumber primary key field has been deleted and I
retrieve the record from an earlier backup using:
INSERT INTO tblTable
SELECT * FROM tblTableBackup
WHERE ID = IDBackup;
the next record to be manually inserted in the table gets assigned a
primary key value of IDBackup + 1 rather than the expected
IDLastAllocated + 1.

I can reproduce this problem in a test database as well as the working
system.

Is this a known problem? I've not been able find anything on the web.

 

[Tom Wickerath]

Hi David,

I cannot reproduce the situation you are reporting. I carried out the
following test, using the Northwind sample database:

1.) Copied the Orders table (structure + data) to two new tables:
tblTable and tblTableBackup

2.) Opened the tblTable table and deleted the first (7) records. This would
be OrderIDs 10248 though 10254.

Then I used the following INSERT query, where I substituted your "ID" with
"OrderID", and "IDBackup" with an actual value. For example:

INSERT INTO tblTable
SELECT * FROM tblTableBackup
WHERE ORDERID = 10249;

After doing this insert, the next record I tried to manually enter was 11078.

This leads me to two questions:
1.) Are you absolutely sure you have the latest service pack installed for
the JET database engine? Use the instructions shown in this KB article as a
guide:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528

Follow the link that reads "Verify that the latest Microsoft Jet service
pack is installed". Also, while you are at it, it would be a good idea to
follow the links for verifying that the latest service packs are installed
for your version of Windows and Office.

2.) Why delete the records in the first place? Why not just add a numeric
field, named something like ActiveRecord, set the default value to -1 (true),
and then when you want to inactivate a record, change this value to 0
(false). You can even have a command button on a form with the caption that
reads "&Delete Record", along with a confirmation message (ie. IF intResponse
= vbYes Then....) so that the user *thinks* they are deleting a record, but
they're actually just flipping a bit. I do this in my own applications, and I
record the NTUserID and Date (=Now), so that I know exactly whodunit.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Allen Browne]

Yes, it is a known problem, David.

See the 2nd cause in this article:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

Compacting the database fixes the bad autonumber seed in some versions of
Access. Of if you want to fix it programmatically, the link above contains
the code to do that.

 

[David L]

Yes, it is a known problem, David.

See the 2nd cause in this article:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

Compacting the database fixes the bad autonumber seed in some versions of
Access. Of if you want to fix it programmatically, the link above contains
the code to do that.

Thanks Allen for the putting me right on this problem.

 

[David L]

Just for posterity the solution I used for this problem used the
Resolution in the MS KB Article (http://support.microsoft.com/?
id=884185), referred to in Allen's web page on this problem (http://
allenbrowne.com/ser-40.html), that recommends using the 'Another
Database' option in the Query Design screen.

As I'm creating the SQL in VBA and using DoCmd.RunSQL to execute it,
this equates to adding the IN clause into my previous example:
INSERT INTO tblTable IN '<full pathname to back-end mdb>'
SELECT * FROM tblTableBackup
WHERE ID = IDBackup;