?
Solved

Missing Primary key

Posted on 2003-11-19
11
Medium Priority
?
710 Views
Last Modified: 2008-03-03
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,
0
Comment
Question by:BenoitPistre
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 9778578
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.

Pete

0
 
LVL 32

Expert Comment

by:jadedata
ID: 9779412
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
0
 

Author Comment

by:BenoitPistre
ID: 9779494
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:inox
ID: 9781166

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
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9781938
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.
0
 

Author Comment

by:BenoitPistre
ID: 9785827
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
0
 
LVL 9

Expert Comment

by:ornicar
ID: 10064384
----------------------------------------------------------------------------------------
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

---------------------------------------------------------------------------------------------
0
 

Author Comment

by:BenoitPistre
ID: 10069114
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\CurrentControlSet\Services\LanmanServer\Parameters]
"EnableOplocks"=dword:0
"CachedOpenLimit"=dword:0
"EnableOplockForceClose"=dword:01

Thank you everyone!

        Benoit
0
 

Author Comment

by:BenoitPistre
ID: 10069123
Moderators please close question. Refund?

Regards,
  Benoit
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10082457
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
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 10131798
PAQed, with points refunded (250)

GhostMod
Community Support Moderator
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question