Losing primary key

[Kevin Witty]

Well, it's happened again: the primary key on one of my tables in my backend
is no longer a primary key.

This has happened periodically over the past several months. The last time
it happened was just after a "write conflict" message, if that might have
something to do with it. I am not changing in with VBA. It's a primary key
one minute, and not the next (as evidenced by the fact that they suddenly
can't update a record they could a minute ago).

Any suggestions here? My client is getting fed up with Jet. Can't blame him.

(Access 2003, SP1, Jet 4 SP 8, Win/XP Pro)

Kevin

 

[Allen Browne]

Presumably you mean that if you open the table in design view (in the back
end database), the field no longer has the Key icon indicating it is the
primary key, and the primary key index is missing from the Indexes list
(View menu in table design.)

If it is the case that the index is being lost like this, it indicates a
corruption. IME, this is a very rare event, so something is wrong with the
setup. If you are interested in solving the issue, work through the list of
issues - one at at time - in this article:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

If no design changes are being made and the index is still being lost,
concentrate on the first list of issues i.e.
- users
- power
- hardware
- software
- network
- configuration
- disk space
but check out #2 and #3 from the development section also.