Deleted record - primary key removed? (1 Viewer)

Morphies

Member
Local time
Today, 01:35
Joined
Dec 8, 2021
Messages
30
Morning All,

We have an intermittent issue, which are always fun.

DB is split with the back end residing on a shared network resource.

We have our main table, which records are entered and then updated by users at different operations.

The main table has an auto number field which is normally set as the PK. I've had to reset this as the PK numerous times in the past.

For some reason, when the user was updating some fields in the record, the record changed all fields to #deleted and dropped the PK from the table.
I have now changed the PK field to another field that is our job number field,

I've done a compact and repair and received 2 errors:

MSysCompactError MSysCompactError

ErrorCodeErrorDescriptionErrorRecidErrorTable
-1017​
Record is deleted.
Job Register and Report Log
-1053​
Index or primary key cannot contain a Null value.
Job Register and Report Log
Incidentally, the customer field which is linked to another table and is a required field did not say deleted, but was simply blank.

I should add that certain fields, such as customer, are not editable in the view the users use when updating the records, and the fields they can change are not mandatory.

Any ideas where I could start looking at what is causing this issue? It seems
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:35
Joined
May 7, 2009
Messages
19,169
see "Similar threads" below.
there might be an answer for you already.
 

GPGeorge

Grover Park George
Local time
Yesterday, 18:35
Joined
Nov 25, 2004
Messages
1,776
Morning All,

We have an intermittent issue, which are always fun.

DB is split with the back end residing on a shared network resource.

We have our main table, which records are entered and then updated by users at different operations.

The main table has an auto number field which is normally set as the PK. I've had to reset this as the PK numerous times in the past.

For some reason, when the user was updating some fields in the record, the record changed all fields to #deleted and dropped the PK from the table.
I have now changed the PK field to another field that is our job number field,

I've done a compact and repair and received 2 errors:

MSysCompactError MSysCompactError

ErrorCodeErrorDescriptionErrorRecidErrorTable
-1017​
Record is deleted.
Job Register and Report Log
-1053​
Index or primary key cannot contain a Null value.
Job Register and Report Log
Incidentally, the customer field which is linked to another table and is a required field did not say deleted, but was simply blank.

I should add that certain fields, such as customer, are not editable in the view the users use when updating the records, and the fields they can change are not mandatory.

Any ideas where I could start looking at what is causing this issue? It seems
Typically, this reflects corruption in the table, and perhaps other tables. Access will do this when trying to compact and repair a table with corrupted records in order to preserve as much data as possible. Search for records that have bad or missing values. Look for gaps in sequence of AutoNumbers as well. Sometimes that will reveal the corruption.

Corruption can occur when a save operation over a network is interrupted fleetingly. Perhaps there is a bad router or switch or even a network card in one or more computers used. Also, WiFi is notoriously reliable than a wired LAN, so if you are on WiFi, this is probably inevitable.

The fact that this is an intermittent problem lends support to the suggestion of intermittent network problems.
 

LarryE

Active member
Local time
Yesterday, 18:35
Joined
Aug 18, 2021
Messages
562
Two things I would do is:
  1. Open the Relationships window and make sure Referential Integrity is enforced for deletions.
  2. Open the form in design view where the Primary key is located and make sure the Default Value for the Primary Key does not indicate 'Null'. It should be blank.
The fact that you said, "The main table has an auto number field which is normally set as the PK. I've had to reset this as the PK numerous times in the past.", is a problem that is systemic to your whole database. You should never ever need to do this, so the errors you are receiving are not surprising. I would start at the basic database design level first, then eliminate issues from there.
 

GPGeorge

Grover Park George
Local time
Yesterday, 18:35
Joined
Nov 25, 2004
Messages
1,776
Two things I would do is:
  1. Open the Relationships window and make sure Referential Integrity is enforced for deletions.
  2. Open the form in design view where the Primary key is located and make sure the Default Value for the Primary Key does not indicate 'Null'. It should be blank.
The fact that you said, "The main table has an auto number field which is normally set as the PK. I've had to reset this as the PK numerous times in the past.", is a problem that is systemic to your whole database. You should never ever need to do this, so the errors you are receiving are not surprising. I would start at the basic database design level first, then eliminate issues from there.
Do note that this is a classic symptom of corruption in tables when they are subjected to compact & repair.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:35
Joined
Feb 19, 2002
Messages
42,981
For some reason, when the user was updating some fields in the record, the record changed all fields to #deleted and dropped the PK from the table.
I have now changed the PK field to another field that is our job number field,
So, either you had no relationships to this table or you modified them to refer to the Job number. Which was it? Or did you leave the autonumber as the target of the FK's?

If you are not using an autonumber as the PK, it is important to remove it from the table. But, before you do that, fix up the related tables to add the job number and populate it. Create the new relationships to the Job Number. Then delete the autonumber from the parent table and delete the old FKs that pointed to it.
 

mike60smart

Registered User.
Local time
Today, 01:35
Joined
Aug 6, 2017
Messages
1,899
Morning All,

We have an intermittent issue, which are always fun.

DB is split with the back end residing on a shared network resource.

We have our main table, which records are entered and then updated by users at different operations.

The main table has an auto number field which is normally set as the PK. I've had to reset this as the PK numerous times in the past.

For some reason, when the user was updating some fields in the record, the record changed all fields to #deleted and dropped the PK from the table.
I have now changed the PK field to another field that is our job number field,

I've done a compact and repair and received 2 errors:

MSysCompactError MSysCompactError

ErrorCodeErrorDescriptionErrorRecidErrorTable
-1017​
Record is deleted.
Job Register and Report Log
-1053​
Index or primary key cannot contain a Null value.
Job Register and Report Log
Incidentally, the customer field which is linked to another table and is a required field did not say deleted, but was simply blank.

I should add that certain fields, such as customer, are not editable in the view the users use when updating the records, and the fields they can change are not mandatory.

Any ideas where I could start looking at what is causing this issue? It seems
A little concerned about the following statement:-

"We have our main table, which records are entered and then updated by users at different operations."

Hope you are not allowing users to enter data directly into tables?
 

Users who are viewing this thread

Top Bottom