AutoNumber problem - is this an Access bug?

  • Thread starter Thread starter Jim Franklin
  • Start date Start date
J

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