BenoitPistre
asked on
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,
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,
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.
regards
Jack
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.
regards
Jack
ASKER
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.
Benoit
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.
Benoit
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
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.
ASKER
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!
Benoit
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!
Benoit
--------------------------
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/)
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
ornicar
Cleanup Volunteer
--------------------------
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/)
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
ornicar
Cleanup Volunteer
--------------------------
ASKER
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:
REGEDIT4
[HKEY_LOCAL_MACHINE\SYSTEM
"EnableOplocks"=dword:0
"CachedOpenLimit"=dword:0
"EnableOplockForceClose"=d
Thank you everyone!
Benoit
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:
REGEDIT4
[HKEY_LOCAL_MACHINE\SYSTEM
"EnableOplocks"=dword:0
"CachedOpenLimit"=dword:0
"EnableOplockForceClose"=d
Thank you everyone!
Benoit
ASKER
Moderators please close question. Refund?
Regards,
Benoit
Regards,
Benoit
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
jadedata
EE Cleanup Volunteer
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
jadedata
EE Cleanup Volunteer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Pete