No more primary key

[Guest]

I have made previously that Inv.No. is a primary key, but later my clerks
said that there is no more message even the invoice number is duplicate. Than
when I see the table which is already in the Back End/Server. We see that
Invoicse No. is no longer a primary key, no more primary key in the invoice
table.

My question is, what caused it changed. Is it because I keep improving the
front end such as form and create the additional reports?

We need your help. Can anybody change it from the frontend, I think none of
my subbordinates understand well about the access.

Thanks in advance.

Frank

 

[Allen Browne]

Hi Frank

There are rare circumstances under which Access can remove the primary key
index. Typically this happens if a table becomes corrupt. When you run a
compact/repair, Access discovers that the index is corrupt. Instead of
removing the bad data (i.e. destroying records), it removes the primary key
index (not the field.)

When this happens, it creates a table that lets you know about the compact
error. You can tell when this occurred from the creation date of this table.

Solving the problem then means examining the table to locate the bad data,
and removing the bad record. You can then make the field the Primary Key of
its table again.

If there were relationships to other tables that depended on this field
being the pk, you will need to create those relationships again.

The original corruption is almost always the result of a failed write. For
details on how to avoid this situation, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

 

[David W. Fenton]

There are rare circumstances under which Access can remove the
primary key index. Typically this happens if a table becomes
corrupt. When you run a compact/repair, Access discovers that the
index is corrupt.

I used to see this frequently in a couple of clients' databases
pre-Jet 4 SP6. Since then, none at all.

 

[Allen Browne]

In his blog, michka posted a scenario that demonstrates how to get Access to
do this:
http://blogs.msdn.com/michkap/archive/2007/04/17/2159574.aspx

 

[David W. Fenton]

In his blog, michka posted a scenario that demonstrates how to get
Access to do this:
http://blogs.msdn.com/michkap/archive/2007/04/17/2159574.aspx

But in the buggy Jet 4 versions, it happened spontaneously, with no
language issues at all.

The Explanation Michael gives seems to me to represent a correct
decision on the part of the Jet programmers.