Access 97 - Constantly repairing

Check into Decompile -- use it as a keyword here on Tek-Tips. I think it applies to Access 97 as well as newer versions but I may be wrong.

Check your tables. If a core table is missing a Primary Key that may incite corruption.

I've seen Memo fields with large contents make a problem.

I've seen a form-gone-bad cause this. When you import the form into your Pristine New Version, it brings in the problem. This is hard to troubleshoot of course.

In general Access 97 is more prone to corruption issues than the 2000-onward versions. Also, database design is critical -- issues with design can suddenly emerge and wreak havoc.

Running Tools > Analyze > Performance could yield a clue.

[purple]_______________________________
_{Never confuse movement with action -- E. Hemingway} [/purple]

Check with your system administrators to see what Active Directory is doing with your db. That seems a much more likely culprit.

traingamer

Are users experiencing desktop lockups and forcing hard booting a lot more? That's a good way to mess up an Access database too.

Corruption is an indication of...
- interrupted transactions
- duplication of primary keys

Was the database moved to a new server, or the OS upgraded on the server.

Specifically, ensure the server and the workstation clients are not caching writes. Another known issue is "Opportunistic locking" on the server.

Also, you may wish to upgrade jet engine...
How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine
And make sure the Network Admins have patched everything for the OS, Internet Explorer and Office.

For example, to prevent "Opportunistic locking", ensure the Server with the networked database, if running Windows 2000, to install SP3 or newer, and install the latest service packs on the desktop. Upgrade any workstation still running Windows NT. (The "Catch 22" here is disabling Opportunistic locking will degrade performance, so you have to apply the patch to accommodate Access databases where do not want opportunistic locking, but opportunistic locking is supported for the other applications.

BTW...

Mircosoft said:

Opportunistic locking allows clients to lock files and locally cache information without the risk of another user changing the file. This increases performance for many file operations but may decrease performance in other operations because the server that grants the opportunistic lock must manage the breaking of that lock when another user requests access to the file.

Click to expand...

...imagine that. You are sharing a database, and caching information that needs to be written back to the database. And other users are trying to access the information but can not because it is cached to your workstation.

Now, consider autonumber primary keys, or incremental keys generated by code. Your application is generating a primary key, but the key is not written to the database. So the next user generates a primary key, and generates the same primary key. Eventually both workstations will want to write the data to disk, and Access will get so confused on trying to write two records which are using the same primary key.

...Moving on
Your culpret could also be a workstation. A workstation drops its connection to the server which messes up the record being written.

...So
If the service packs do not work, you will need to isolate the problem. Is it a user, a workstation? Is one table more likely to be corrupted? Things to look for are users dropping connections, a high transaction table being the primary area for corruption.

- poll your users what they were doing when the database started acting up
- look at the LDB file. (There are some LDB viewers if Notepad does not work) This will give you an idea of who was in the database
- add some simple code to track which user is doing what in an external text log file.

...Also
- Do you use a front end / back end database?
- Do you back up your database (and test the backups)
- Ensure users update the database through the application front end, and not through ODBC access such as via a spreadsheet program.

Good hunting

Richard