Fixing corrupted records in table

[leeroi]

Hello all,

This is going to be a rather lengthy "question".

I have an Access 2k database, separated front end/back end. Front end copies are on about 30 workstations and used frequently during the work day. The backend has a table called CLIENTS with approximately 6000 client records. Changes to data in the table are made via a frontend db Form which has CLIENTS as its record source.

During the past week, approximately 6 records have become corrupted in this table. The table itself can still be opened and closed. In most cases, data can be updated without a problem.

But at least once a day, a user will be updating a record, and all fields associated with that record become corrupted. This may happen when the user is actually entering data, or may happen if she has entered data and then left the record (form)open. There has typically been no warning or error message.

I've opened the backend db and found these corrupted records in the CLIENTS table. They are obviously corrupted. When I try to delete them I get the error message about "search key was not found". If I copy the backend database onto my local hard drive, I've had pretty good sucess deleting these corrupt records.

I did a lot of searching for answers, including Google groups and the MS Knowledge base. So far I've tried the following:

First, I uddated the msjet40.dll file on all computers

Next, I used a MakeTable query, to deposit all the good records into a new table, deleted the original CLIENTS table, and renamed the new one CLIENTS.

I then created a new blank database, and imported all the objects from the old database, renamed the new database to the old name, deleted the old database, and copied the new one back onto the server.

Everything seemed fine for a day. Then yesterday I opened the database and received an error message that the backend database was in an unrecognizable format or had been damaged. When given the option to try and repair it, I clicked "Yes". Compact/repair started. Abouthalf way through the process, Access stopped responding entirely.

I then tried using Jetcomp.exe to repair. That also hung up about 1/2 way through.

Unsure what else to do, I restored the backup copy of the database that was saved last night on the server.

This morning everything was working fine until I was making changes in record. When I clicked to close the form, I got a "write conflict" message that another user had made changes in the records since I opened it- did I want to save changes, copy to clipboard, or not save changes. If I clicked NO, the form closed fine. I could reopen and access the record. If I clicked YES, the record became corrupted. I purposely chose this particular record because it was a very old one that I knew noone else would be working on.

So the bottom line is that I still have a sick database. Our agency depends on this database daily to track referrals, evaluation, and treatment for special needs children. It would be disastrous to lose it, but I am absolutley out of ideas as to what I should do next. By the way, I do not have a Memo field in this table.

If you have any suggestions beyond what I've tried, please respond.

Lee

 

[BNPMike]

You said this happenned during the last week. How many back-ups are affected? Have you got any clean copies from your daily cycle or are you going to have to use a prior weekly or monthly back-up?

Is the problem just the handful of records showing errors? If so just take the last clean backup and do the updates again.

 

[cheerio]

If you search the FAQs for Access corruption you will see that one likely cause of regular corruption is that you have too many users for your database design. The critical issue is not the absolute number of users but the number who are updating the tables - eventually they get in each other's way.

Access is not intended to compete with the MS upmarket database engine, SQL Server, and one of its limitations is the record locking/multi-user capabilities.

It's a particular problem if you have linked tables and allow users to browse them. Think of a paper based system where you allow people to take whole drawers out of the filing cabinet.

If you want to stay with an Access backend look at changing your forms to unbound forms. In effect users take a copy of the data which they alter and database access is limited to taking the copy and then updating the data. Instead of locking tables for minutes at a time they lock for fractions of a second.

 

[BNPMike]

It is important, with any commercial application, to take frequent back-ups. Also when building a system you must consider your business continuity plan. In this case you consider what happens if the database gets corrupted. If you can't think of an acceptable strategy then it may point to not using Access but to a more robust database that provides journalling.

Access Jet is prone to corruption in multi-user update situations as the mangement of the database is under the control of distributed clients. There is a much higher chance therefore that a process can be interrupted mid-way due to network problems or workstation crashes.

 

[leeroi]

Several points come to mind in thinking about the multi-user update situation as a cause for corrupted records. First,I'm sure for most if not all of the corrupted records, there was only one user accessing these particular records at the time corruption occurred. Secondly,we have had a stable number of database users for at least a year. Yet I've only encountered these corruption patterns in the past 10 days.

Would either/both of these points argue against a multiuser situation as the cause for record corruption?

Thanks,
Lee

 

[JeremyNYC]

Certainly moving to a more robust database engine would be a good way to fix this. But it would be quite expensive. But Access is plenty capable of handling this kind of workload. I have a database with this many users and far more data that has been in use for three years with very rare corruption problems. I'm not at this client site more than once every few months, and the database is in daily use.

Some of the forms _are_ unbound, though, so that may be part of why I've experienced less corruption. If it's just one form that people are using most of the time for manipulating data, you might as well build an unbound version of that form. It shouldn't take you more than a day, even if it's really complex and you've never done it before. It certainly couldn't hurt anything, as long as you test the heck out of it.

You don't want to hear this, but you may have a sick network card in one of your PCs. Or there may be one of a whole bunch of other problems going on. This page:

http://www.granite.ab.ca/access/corruptmdbs.htm

has a bunch of great information about Access database corruption.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[leeroi]

Where should I look to learn how to create an unbound version of my input form?

By the way, I've found that most of the workstations are woefully behind in office 2k updates- some don't have SR1 on them. My understanding is that this could also be related to some of my data corruption issues.

 

[Jackvmae]

Leeroi -
If some of your stations only have SR1 for Access 2000 then I am really surprised you have not had more corruption problems. Updating to the current (SR3) is absolutely necessary.

I am still learning, so others may have more experience than me with regards to unbound forms. However I use unbound forms extensively in my app to allow users to add new records. This does not need much code (if I can do it anyone can), and it allows users to take their time entering new records and not worrying about locking the recordset. When the user is ready to add the new record to the table they simply click the Update button which triggers code that checks a few things and then uses DAO to update the new record to the table.

HTH.

Jack

 

[BarbaraC]

I was very excited to read your reply indicating that only having SR1 instead of SR3 could be causing the "Search key was not found" problem (which has been occurring several times a day recently). I was recently given a new laptop at work (about 2 weeks ago) and just discovered that it has Access 2000 SR1 on it (and OS Win XP Professional). The users have Access 2000 SR3 with OS Win 2000. If I have been creating DB's on my laptop using SR1 and then put them on the network for the users could that still cause the problem - even if the users have SR3?

Bottom line is that I will now upgrade to SR3, but wanted your opinion if that could be my problem.

Barbara

 

[leeroi]

It took some time, but I've updated all workstations to Office SP3 and installed the latest jet file (msjet40.dll, version 8). Since doing this I've had no more problems.

Lee