Solved

Missing Primary key

Posted on 2003-11-19
11
695 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now