Access 2002 Table Structure Primary Key

  • Thread starter Thread starter Mickie
  • Start date Start date
M

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?
 
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
 
For some reason the
primary key gets removed, don't know how, when, where.
Click to expand...

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]
 
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
 
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
 
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.

J. Goddard said:
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
Click to expand...
[quoted text clipped - 10 lines]
Click to expand...
 
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.
 
Back
Top