Missing Primary key

We keep losing primary keys.
The situation appears to be this:

We have got a small network (30 users) with all our MS Access 2000 SR-3, Jet4,  databases in one shared folder on our NT server.
The databases the users access mainly contain link tables. Our main tables are located in ‘parent’ databases, never accessed directly by the users.

Last Friday morning, users couldn’t update any records because the link table they were trying to update had lost its primary keys.  We put the primary keys back into the original parent table and it seemed to work fine (see. Microsoft Knowledge Base Article 304179) …. till this morning when the same thing happen again.

After checking backup copies of the parent databases it appears that the primary key disappeared overnight.

Don't know if it's directly related to our problem but maybe you need to know these extra points:
- Databases are compacted directly from the NT server (via a scheduled batch file) every night.
- We have been manually compacting and repairing the databases quite a few times over the last weeks since we migrated to Access 2000 and we keep crashing (‘Unrecognised database format’, ‘Action reset current code in break mode’, ‘ Invalid Argument’ etc.). I believe that the primary key problem was there before the migration (after checking our backups one of the primary keys missing last Friday was not there before we changed from A97 to A2000).

Any idea why the primary keys keep disappearing?

Does that ring a bell to anyone – Anyone having the same problem?
Any help is appreciated,
Thank you in advance,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I can only recall one similar issue that I have read about but I can't find the reference at the moment.
I'm pretty sure the data loss problem was eventually diagnosed as a problem caused by the virus checker running when compacting but that's as much as I can recall.

However, if you have now established that the problem existed in the unconverted version you really have to make sure that this is not an application problem.


jadedataMS Access Systems CreatorCommented:
Hey BenoitPistre!

  Create a new database in the version desired.
  Import all the objects from the MDB that is not feeling well.
  This will recreate ALL the indexes in all the table, including the system tables where this issues might be taking place.

BenoitPistreAuthor Commented:
Thank you peter57r and PFJ8nzRk!

Indeed Sophos anti-virus is running when compacting our Db. I am going to try to change the time of the scheduled scan.

I am also going to rebuild the databases from scratch as recommended and import all the data again.

This should hopefully make things better. I’ll let you know.
Thank you for your help.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.


you mentioned "backup copies of the parent databases"
how do you create them exactly?
do you occasionly restore them?
Will the 'lost primary key' being detected immediately or maybe delayed?

I also experience 'lost primary keys' I have a suspicion
jadedataMS Access Systems CreatorCommented:
backup software will usually fail and log an error when attempting to backup a database that is "in use" according to windows.  If however your backup software is forcing the copy while there is database activity, this could conceivably cause a corruption event.
BenoitPistreAuthor Commented:
jadedata,inox > Indeed, TapeWare, our backup software, fail and log an error when trying to backup databases that users have left open. This doesn't happen often (hasn't for the last month) since we have a script closing the Databases at a certain time before the backup starts.

jadedata > I have rebuild our main databases last night as you suggested...let's see what happen now (fingers crossed).

peter57r > I have checked the anti-virus scan schedule, it runs hours before the compacting script starts.

Thank you all for your help!

This question has been abandoned and needs to be finalized.
 You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)


Cleanup Volunteer

BenoitPistreAuthor Commented:
The problem occureed because of a bug in Microsft Access 2000 (SP3  - Jet4).
Server's registry settings need to be changed to avoid the problem. Oportunistic locking need to be disabled on the server.

Here is the scritpt I used:



Thank you everyone!

BenoitPistreAuthor Commented:
Moderators please close question. Refund?

jadedataMS Access Systems CreatorCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next seven days.

EE Cleanup Volunteer
PAQed, with points refunded (250)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.