AutoNumber problem - is this an Access bug?

[Jim Franklin]

Hi,

I have two identical tables, which each have an Autonumber field,
Movement_ID as the primary key. I have a query which should simply append
the records from tbl2 into tbl1. I have left the Movement_ID field out of
the query, so Access should generate the numbers automatically for tbl1.

However, when I run the query, I get a key violation. Investigating, I
removed the tbl1 primary key index, allowing duplicates in my Movement_ID
field and re-ran the query. It appended the records, but put the values 46 -
49 (there are 4 records in this example) in my AutoNumber field, even though
there are some 2000 records in tbl1 and ID's 46-49 already exist.

If I run the query again and again, the Movement_ID's of the appended
records increase accordingly, e.g. 50-53, 54-57 etc.

The existing records for tbl1 are created programmatically using VBA, rather
than manually input, although the Movement_ID is still generated by Access.
Could this be something to do with it?

As always, any help is greatly appreciated!

Cheers,
Jim

 

[Allen Browne]

There was a bug in JET 4 (the database engine used in Access) that allowed
it to mis-set the autonumber seed, causing duplicates.

Go to support.microsoft.com, the Downloads section and get Service Pack 8
for JET 4. That will save the problem recurring.

To fix the existing problem, see:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://members.iinet.net.au/~allenbrowne/ser-40.html
The article contains the code you need to fix the problem in all affected
tables of a database.

 

[Jim Franklin]

Thanks Allen, I have run your code and it seems to have fixed the problem.
One question though - I have checked both my machines and according to
support.microsoft.com they are using the latest version of Jet 4 (version no
4.0.8618.0)

The database was originally created on another machine about a year ago. The
problem has never occurred until now (doing some modifications to the
original application.) Could this still explain the problem?

Thanks again Allen,
Jim

 

[Allen Browne]

Yes, the new machine could have had a different patch than the previous one.

Or if you converted from an old version during the change, that can do it
also.