?
Solved

Missing Primary key

Posted on 2003-11-19
11
Medium Priority
?
724 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
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
Technology Partners: 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

621 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