Access 2000 Autonumber Corruption

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jim Rodgers (JimRodgers_at_discussions.microsoft.com)
Date: 01/13/05 

Date: Thu, 13 Jan 2005 08:17:06 -0800

I've been using MDB files for things like this for nearly a decade. But this
is something new — and quite troublesome.

I have a small workgroup application written in VB6 using ADO where the mdb
file is on a WinXP/Pro and the clients once were all Win98SE. Everybody had
MDAC 2.8 and every other update I could think of. Things were mostly good.

Then XP/SP2 comes along, and to confuse matters completely, one of my W98SE
client machines is replaced with XP/Pro/SP2 right when the server was also
updated to SP2. Then another, and then one more is upgraded to XP/Home.

"About" the same time as these changes were made, I started getting
corruption of the autonumber primary key field in one of the tables.

It always happens in the same table. It is possible that it only happens
when the XP/Pro client is using the application, but this also might not be
true. It's hard to tell with these particular users because they don't
always report the problem promptly (because they think it's their fault and
they don't want to get in trouble).

The corruption is this: the autonumber field rollsback to an already used
value, thus causing prinary key errors on subsequent .AddNew/.Update
attempts. It's the damnedest thing!

Repair or Repair/Compact (either from Access 2000 or through VB6 using JRO)
does not fix the corruption. One must copy the data to a new table, delete
the old one, and rename, etc.

I am using ADO transactions around each critical step in the procedure where
corruption occurs. The .BeginTrans and .CommitTrans/.RollbackTrans "pairs"
are well-understood: I don't believe I have any sneak paths here. It is
POSSIBLE that the corruption occurs only subsequent to a .Rollback from the
XP client, but this is not certain and may be a red herring.

Typically, there is only one user running this procedure at any time, and
the problem is known to occur when this is the case.

********* HHHeeeeeeeeelllllllllppppp! *********

- Jim 

-- 
James W. (Jim) Rodgers, P.E.
Senior Consultant
General Consulting Engineers, LLC
Atlanta, GA

Relevant Pages

  • Re: Access 2000 Autonumber Corruption
    ... I no longer include MDAC in my install programs. ... >> GlobalSign digital certificate is a forgery and should be deleted without ... >>> client machines is replaced with XP/Pro/SP2 right when the server was ... >>> corruption of the autonumber primary key field in one of the tables. ...
    (microsoft.public.data.ado)
  • Re: Access 2000 Autonumber Corruption
    ... The latest Jet update should fix it. ... is to get each of the users to visit the Windows Update site and ... > client machines is replaced with XP/Pro/SP2 right when the server was also ... > corruption of the autonumber primary key field in one of the tables. ...
    (microsoft.public.data.ado)
  • Outlook Web Access, RWW, partitions
    ... >When you try to access OWA with the premium client, ... IIS 6.0 Gzip Compression Corruption Causes ... Clear the IIS server files follow these steps: ... Files directory ...
    (microsoft.public.windows.server.sbs)
  • Re: Intermittant 3167 error/indexing problem
    ... I have a client with a replicated back-end database ... who sporadically suffers from data corruption. ... replication fields) before converting the new back end into a ...
    (microsoft.public.access.replication)
  • Intermittant 3167 error/indexing problem
    ... I have a client with a replicated back-end database ... who sporadically suffers from data corruption. ... I created a new back-end data file ... replication fields) before converting the new back end into a ...
    (microsoft.public.access.replication)