Missing Primary Key

[XidGarcia]

I am creating a database using the JET provider and .NET. I make a
copy of a template database and insert rows into various tables. The
database is closed, copied to another location and used my my
application. In a few instances we have noticed that the primary key
on some of the tables is missing and duplicate rows exist. It is
possible that this is being caused by the compact database tool, but I
am not sure just yet. Has any one else had this problem? I need some
suggestions on where to look for the source of the problem. Thanks.

 

[Douglas J. Steele]

I'd be shocked if compacting caused the problem.

You say that you've got a copy of the modified database. Does the original
have the same problem?

 

[XidGarcia]

Ok, so I have tracked the problem down further.

Somehow my Access database is ending up with duplicated records in a
table. When the compact & repair utility is ran, the primary key is
dropped and the corrupt records that are reading #error now read
#####...

 

[Allen Browne]

Yes, a compact/repair will drop the index rather than the data if it find
duplicates in your table.

This should be an extremely rare event. If you have seen this more than
once, there is some problem solving to be done.

First step would be to make sure that every machine is up to date with its
Office and JET patches. You can determine the Office patch through Help |
About. To determine the JET patch, track down the file msjet40.dll
(typically in windows\system32), right-click and choose Properties. On the
version tab, you should see:
4.0.8xxx.0
The xxx digits don't matter.
On Windows 2000 the minor version might start with a 9.
If it is less than 8, download the service pack from:
http://support.microsoft.com/gp/sp
You need to to this in each machine that opens the database.
What I personally do is to put this information on a form in the database.
Explanation of how:
Display system and version information
at:
http://allenbrowne.com/ser-53.html

The other alternative is that there is a faulty network connection or some
other issue that is causing in interrupted write. To follow that through,
work through each of the issues listed here:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html