Table record has #Delete for all fields

[briansmithdanisco]

I have a table that is populated with a front end form. The DB is on a network. Operators process batch materials and update the tables with data about the batch process. Each plant location has its own table and front end.

The tables have been working fine for months, but now random records that have been entered, and are in the process of being edited, are turning into "deleted" records. The operators will make an initial record entry to get the record created, and as the process goes forward they update the record with more data. Sometimes the form is left open for the length of the batch that is being run. Each plant has its own forms, but you can access other plant locations from different PCs on the network.

When a problem is detected I open the table and see all the fields in the problem record have changed from valid data to all having the value of "#Deleted".

It seems to happen when the user has the entry form open and, all the suddend they get a message box that says the record has been deleted. Their front end is locked up and I have to reboot the PC.

I have tried to delete the record with all the "#Deleted" values, but it never goes away.

To fix it I have to do a compact and repair. After that the record in question goes from values of "#Deleted" to values of all "####". I then delete the record that is now all "####", and have the user re-enter the data.

I did a test when the record was still in the state of saying "#Deleted", and tried to enter the same record again on a new line. I got an error message saying I was about to create a duplicate record. The leads me to think even though the record has "#Deleted" in the field, the table still thinks the original data is still there.

The only thing I can think of is while someone has the form open with the record displayed, someone else is deleting the record from a different connection to the database.

Has anyone experienced the same problem?

 

[PHV]

Does the FE's linked table recognize the PrimaryKey on the BE ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[briansmithdanisco]

Hi PHV,

Yes. If the FE is adding a new record and the primary key (BatchNumber) is not filled in, it triggers the message that it is a required field. If the user attempts to enter a duplicate record, the primary key error is triggered and a message is sent back telling them a duplicate record would be created.

 

[PHV]

Is the BE an access DB too ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[briansmithdanisco]

Yes it is. The back end is access, and only holds the tables. The front end has links to the BE tables.

I did a test where I opened a record on a FE on my PC. I had another person open a different FE on their PC and delete the record I had opened. On my pc I saw all the fields go to "Deleted". My PC did not lock up and the record being deleted did not cause any problems with the tables. From my side, I saw a message "Record in table 'xxx' was deleted by another user".

I closed my FE and recreated the record without any issues. I can't seem to recreate the "#Deleted" issue the other users have seen.

Do you know of any timeout issues with linked tables? The usere tend to leave the FE open for hours at a time. I never though this to be a problem since each FE is making hits against a specific table that has record locking as the default.