Primary Key Unexpectedly Gone from Table

[Simon]

Can you tell me if you have ever heard of this happening and why?

The first indication that 'something' is wrong with the db is that users are
no longer able to enter data into forms whose RecordSource contains this
table (Person) and others. If I look at the Person table, I can see that
there is no longer a Primary Key set. I can re-set the Primary Key to the
[id] field. A month later, users cannot enter into the forms where the
Person table is part of the RecordSource, and I find that the Primary Key is
missing from the Person table again. What is causing the PK to disappear??
As the db is split, I cannot run any data definition code to set the PK on
linked tables! (right?)

This is a database that has been in production for several years. The db is
split and both front end and back end are .MDEs. There are absolutely no data
definition statement in the code that 'could' have eliminated the Primary Key
for the table(because the tables are linked) DB is Access 2000.

Any ideas on what is happening and why??
I really appreciate any input you may have!
Thanks
Simon

 

[Allen Browne]

See the last symptom of corruption in this article:
http://allenbrowne.com/ser-47.html#LostKeyRelation

It is actually possible to programmatically create an index on a linked
table, if you OpenDatabase on the back end so you are operating on that file
instead of CurrentDb. You can then CreateIndex on the TableDef, CreateField
and append the fields to the index, set its Primary property ot True, and
append this new index to the tables Indexes collections.

For an example, see:
http://allenbrowne.com/func-DAO.html#CreateIndexesDAO

 

[Simon]

Thanks for the reply, Allen.

I don't understand how the entered data could violate the index when the PK
is an AutoNumber field. The [id] field is not visible on the form and
therefore not a value that is entered by the user.

If the db is split, where does the Function CreateIndexesDAO() in your
example reside?

See the last symptom of corruption in this article:
http://allenbrowne.com/ser-47.html#LostKeyRelation

It is actually possible to programmatically create an index on a linked
table, if you OpenDatabase on the back end so you are operating on that file
instead of CurrentDb. You can then CreateIndex on the TableDef, CreateField
and append the fields to the index, set its Primary property ot True, and
append this new index to the tables Indexes collections.

For an example, see:
http://allenbrowne.com/func-DAO.html#CreateIndexesDAO

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Can you tell me if you have ever heard of this happening and why?

The first indication that 'something' is wrong with the db is that users
are
no longer able to enter data into forms whose RecordSource contains this
table (Person) and others. If I look at the Person table, I can see that
there is no longer a Primary Key set. I can re-set the Primary Key to the
[id] field. A month later, users cannot enter into the forms where the
Person table is part of the RecordSource, and I find that the Primary Key
is
missing from the Person table again. What is causing the PK to disappear??
As the db is split, I cannot run any data definition code to set the PK on
linked tables! (right?)

This is a database that has been in production for several years. The db
is
split and both front end and back end are .MDEs. There are absolutely no
data
definition statement in the code that 'could' have eliminated the Primary
Key
for the table(because the tables are linked) DB is Access 2000.

Any ideas on what is happening and why??
I really appreciate any input you may have!
Thanks
Simon

 

[Simon]

Allen,
I tried to use your Function CreateIndexesDAO(). I put the code in the Open
event for the applications main menu. When I split the db and ran it, the
code errored out saying the "Operation not allowed on linked tables".
So, I'm confused....I'm not sure exactly where to run the code from?? Can
you tell me how to run this?
Thanks,
Simon

 

[Allen Browne]

The code example uses CurrentDb. As Previously suggested, you need to use
OpenDatabase instead.

That is instead of:
Set db = CurrentDb()
use something like this:
Set db = OpenDatabase ("C:\MyFolder\MyFile.mdb")

As to the cause of the problem, it should occur rarely - only when something
goes really wrong. If you have experienced this several times, it may
indicate that there is a problem in the way one of the machines is
connecting to the back end. The most likely cause is a flakey network
connection or bad power - something that can interrupt it in the middle of a
write. A bad network card, or connector, or an unstable connection
(including WiFi) would be your first step to investigate.

Perhaps you could log each user in and out, and see which user/machine is
falling off. You probably want to log both the computer name:
http://www.mvps.org/access/api/api0009.htm
and network user's name:
http://www.mvps.org/access/api/api0008.htm

List of things that can cause of corruption:
http://allenbrowne.com/ser-25.html

 

[Simon]

Thanks very much for the help, Allen!
I appreciate your time.