Solved A user corrupted a record by adding quotations into a field and I'm not sure how to correct it. (1 Viewer)

[ThatTransportationPerson]

Hello everyone!

A database user was filling out a form and entered a quotation mark into one of the fields. This caused the record to become corrupted:

It also removed the auto-generated primary key in the LabNum field. I've tried editing the record to remove all the data, replacing the primary key, etc. but I always get the same error: "The search key was not found in any record." I can't even delete the record. Are we stuck with this one corrupted record forever? Or is there a solution for this?

Thank you in advance!

 

[CJ_London]

Suggest try using a query to copy to copy tge data to a new table but exclude the corrupted record with a criteria

 

[Pat Hartman]

Start by turning off Name Auto Correct. I recommend leaving it off and only turning it on when you specifically want it to do something for you AND you understand how it works. Or, you can just turn it back on when this situation is resolved. Your choice.

Make a backup of the database.
Remove any relationships
Rename the table table with the bad data. ie. tblSomething_BAD
Create a new table using the original name to replace the old one include all the fields defined as well as indexes.
Create two append queries. Query 1 appends all the rows BEFORE the bad one and query 2 appends all the rows AFTER the bad one.
Add all the relationships back.

 

[Isaac]

You have fallen victim to the classic "Chinese Characters" problem. I think this is most likely corruption of that record (but probably has nothing to do with a quotation mark).

You may need to simply delete that record and re-create it. Corruption is something you should research to see all the ways it can happen and strive to avoid them.

If you can't directly delete the record, then Copy the table object, then Ctrl+V to paste (structure only), then append query the good records into the new table. Then delete the old table and rename the new table as the desired original name.

 

[GPGeorge]

In MY experience, the most common reason for this particular sort of corruption has been network blips or drops during a write process to the table. That can happen for a number of reasons, ranging from intermittent WiFi to running a network cable across the top of a fluorescent light fixture. Network cards going bad are also in the mix. Once you fix the data problem, turn your attention to reasons for it, such as those mentioned above. Do not look too harshly on users, though. It's remotely possible someone did something, like turn off their computer accidentally during a write process, but it's more likely to be something in the computer or in the network.

 

[isladogs]

I see you are using subdatasheets. Check that the equivalent record for 2022A-1567 in the associated table isn't also corrupted

 

[ThatTransportationPerson]

Hey everyone! Just wanted to provide an update as I was able to correct the issue relatively painlessly.

Firstly, I should explain how the DB is set up, and also specify that I did not build it but I am in charge of maintaining it. There is a "back end" .accdb which contains all of the tables and is saved on our server. Each user (10+) has a "front end" version saved to their computer which links to these back end tables and also contains queries, forms, reports, and VBA.

The reason I stated the record became corrupted is because the user (another database manager who focuses on the front-end) came to me and said that he entered quotations into a field where there is a label that states "Do not enter quotation marks in this field." I believe my predecessor, the creator of the DB, used SQL for this specific field and thus quotation marks had the possibility of corrupting it. I also learned later that in addition to the one corrupt record, all of the drop-downs in the form broke.

I was going to follow the suggestions here, but just for the hell of it I decided to perform a C&R on the back end (which required all users to close their DBs on their personal computers). Once this was completed, the corrupted record moved to the bottom of the table and I was able to successfully delete it without experiencing the error I had previously. Like magic, the drop-downs in the form worked again and so far everything is working perfectly! Hopefully it stays this way.

Thank you all for your suggestions!

 

[Gasman]

Best allow for quotation marks ASAP then.

 

[gemma-the-husky]

Maybe in the before update event for that field/control test the entry, and cancel the update if the text includes a double quote. Chr(34) if that helps. The easiest way is to prevent that character getting into the data.

If you are using the data for a CSV file, say, the presence of double quotes can often cause misalignment of data. Maybe they are inserting commas within the quotes. That may be the issue.