Disappearing Records and other strange occurences

VB2C
VB2C used Ask the Experts™
on
I have recently been tasked with helping a user correct a database problem they have been having with an Access97 database.  Almost all of the tables are linked, most of the tables linked are Access tables and a few are on Sybase. At least one Sybase table has over 2 million records (only select queries are run against this table). The entire application is Macro driven - no code at all. Here are the problems (other than the macros):

1) records are disappearing. 45 records will be added and viewed in a report, then an hour later, the same report will only show 6 of the 45. This only occurs on one table, in access, that has 42,000+ records.

2) file locking problems - Error message "Could not use; file already in use"  The .ldb file is not going away on its own and must be deleted. Repair and compact is then performed.

3) "unrecognized database format" or "<filename> needs to be repaired or is not a database" - I assume users are powering off when the app is open and are corrupting the database, again repair and compact.

4)#ERROR is appearing across rows in a table and "Not a valid Bookmark" errors are occuring. The rows cannot be deleted. We replace the table with a copy and have had instances where these will turn into records. I assume this is another corruption issue.

5) Reserved error (-1601) is occuring - According to microsoft we should just copy the database and import all the objects. It works.

I have been reading a lot on the exchange where people have apps with over 300,000 records and 50 users with minimal problems. Am I missing something? We basically consider Access to be somewhat unstable and best suited to small apps with a few users. This app has 5 users, and many other of our access apps see the need to compact and repair more often (due to problems 2&3) than the nightly process we already have automated.

Sorry about the low pointage, but this is my first time out and don't have many to give.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
What's happened is that the database has become corrupt.  You should create a fresh MDB container and import all objects.

If the problem continues, then you'll need to investigate what is going on.  There are a large number of things that can cause a MDB to become corrupt.  Anything from a user getting a GPF windows, to a network problem. Basically any abnormal disconnect.

The first step in tracking this down is to use LDB View (available from Microsoft's web site).  LDB View allows you to determine which station put the MDB in a suspect state.  After a few go arounds, you can determine if it is one specific station, or is random (which generally indicates a server or noetwork problem).

In the mean time, a couple of things:

1. Ensure that all users are properly shutting down.
2. Ensure that the MDB is not being virus scanned and "fixed".
3. Ensure that all users are up to date on service packs (both Office and JET).
4. If an NT server is involved, turn off OPLOCKS.
5. Ensure that all users are using the same default network protocol.
6. If a Novell server is involved, ensure that the JET engine setting MaxLocksPerFile is under the setting on the Novell server for Maximum Number Of Locks.

  Those are the heavy hitters and may clean up the problem.

Jim.
Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: JDettman  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)

Author

Commented:
This problem has not been resolved. The answer provided was good, but did not solve the issue.

Author

Commented:
No answer provided a solution, so you were as close as anyone.
Points refunded

Computer101
E-E Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial