Access 2002 Table Structure Primary Key

[Mickie]

I'm having a problem with a table in Access 2002. For some reason the
primary key gets removed, don't know how, when, where. But all of a sudden
the forms don't work and the users get weird error messages. When it happens,
I put the primary key back on the table, compact and repair the database, and
everything is Ok. But then it happens again. It is always the same table in
the database. I've never experienced this with any other clients, but of
course most of my clients are using SQL Server 2000. Any suggestions or help?

 

[Ray Cacciatore]

Unless the file is corrupted, Access does not remove primary keys on its own.
Do your users have access to the tables? Maybe they open a table in datasheet
view, and delete the field from there.

Is there a related table whereby the primary key is related to a foreign key
in another table? If there is a related table, referential integrity won't
allow this to happen.

Try adding records to a related table and see if you can delete the primary
key. Don't forget to add the relationship.

This is very strange.

Ray

 

[John Vinson]

For some reason the
primary key gets removed, don't know how, when, where.

Is the value stored in the primary key field being removed? Or is the
table design actually changing, removing the field from the table
altogether? Neither *should* be happening, of course!

John W. Vinson[MVP]

 

[Mickie]

The value is fine, it's the primary key on the table that is being lost. So
I end up with a table without a primary key, but the data is still in the
table.

Mickie

 

[Jerry Whittle]

Very strange. Either a user is getting to the table and removing the PK or
you have a table corruption problem. Tony Toews has an excellent web page on
database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[J. Goddard]

Do you have any code in your database that might delete the table and
recreate it, perhaps with a file import? Or, could there be a
make-table query that recreates it, with warnings off?

Just a thought!

John

 

[Mickie via AccessMonster.com]

No, I've checked all of that. This is a customer name & address table. It's
always the same table that loses the key. The client uses a third party
daily backup system, but I can't imagine that would do anything to a table in
Access. It should just back up the .mdb file. This has been a problem for
for a while. We even converted from Access 2000 to Access 2002 to help
resolve the issue. But it just happened again over the weekend. I think I
have the client convinced to conver the database to SQL Server 2000.
Hopefully that will take care of the problem.

Thanks for all of the advice.

Do you have any code in your database that might delete the table and
recreate it, perhaps with a file import? Or, could there be a
make-table query that recreates it, with warnings off?

Just a thought!

John

The value is fine, it's the primary key on the table that is being lost. So
I end up with a table without a primary key, but the data is still in the

[quoted text clipped - 10 lines]

 

[Gertrude]

I have the same problem. I have eleven Access 2002 databases running, all
with the same structure. They are split FE/BE with approx. 20 users on a
Windows 2003 server network. About every 2 or 3 months, some PK is lost on a
table. When this happens the problems start: duplicate records, forms don't
work anymore, etc.
Anyone knows why this happens? I have the idea that it is because of network
problems, it happens more where the network is unstable and where more users
are accessing the BE.
Another question, does anybody know how I can check programmatically if the
PK is lost and how to set it again?
Any help is greatly appreciated.