sql - Anyway to anticipate and handle corrupted records in Access SQL statement?

Question

I have an split front end/back end Access database that has a userform with a lot of things going on.

Specifically, a listbox that populates from a union SQL query is not working and I have isolated the issue to a corrupted record in the back end database. I made a copy of the database, compact & repaired it, and removed the record.

I made a front end copy, relinked it to the backend database copy and vwalla everything's fixed.

Now of course, I can't fix the actual database because dozens of people could potentially be on it. I'm going to try to sneak an update tonight at midnight.

I have a few questions, and of course I'm also open to any other design recommendations. I'm going for the easiest fix I can. My question is, is there a SQL statement that might be able to handle a corrupted record? Here is the current SQL statement

SELECT DISTINCT CaseInfo.reviewerID
FROM caseInfo
WHERE caseInfo.reviewerID IS NOT NULL
UNION SELECT 'All' AS 'reviewerID'
FROM caseInfo
ORDER BY reviewerID;

Just for reference that one corrupted record has the Chinese language stuff in the reviewerID field (and crazy numbers in other fields). Anything I could do that could corrupt-proof this query? FYI when I run it right now I get

"Reserved error (-1524); there is no message for this error."

If that's not possible. Any other ideas? The root of the problem is figuring out why that record got corrupted, but I'm not sure. The way the database works, someone is assigned to load the records directly into the backend database, with the ReviewerID filled with an employee's name, the initial details of the case, and then a lot of blank fields. The employee's then pull up their front-end copy, use the listbox to sort for their name, and then proceed to use the userform to complete the case review.

Perhaps directly loading the cases into the back-end makes the database vulnerable to corruption. Also, I'm not sitting next to the guy who loads the cases when he does it to know if he could be doing something tricky. The userform has a bunch of bounded fields, and some unbounded field's that update via a SQL update. When I was designing the database and form this was a real pain to avoid lock-errors, but I eventually sorted them all out. But if I could do it over again, I would probably not uses any bounded fields and operated the entire userform with update statements (but alas it's not really possible for me to redo the database.)

I'm also going to try to implement this How to determine who is logged in so that if I need to do a mid-day update I can figure out who is on and ask them to log-off so I can make the necessary corrections.

thoughts people? :p

标签： sqlms-accessdatabase-design