Frequent database crash after Compact

[MrMMM]

Hi,
We have a database with a large number of users, up to 20-30 at a time.
Also, there can be many reads and a decent amount of writes in to the
database at the same time. The DB is split inot a front and back end.
The total size of the Backend DB (after compact) is about 5Mb.
I have recently compacted the backend using the access menu (before
compact and repair the size was around 136Mb.)
Now, during the busy time of the day, the database crashes! (sometimes
once a day sometimes a few times an hour). Basically, it looks like
the backend gets corrupted! When new users try to log in they get a
message saying the the ***.mdb file is not recoginzed as a access
database. Most of the time, the backend can be opened separetly, with a
message that it is corrupt, and then Access fixes (compcts and repairs)
it automatically.

From the people working here, it seems that this used to happen, so

they stopped compacting. And after a while (once the DB file got fairly
large) the problem went away. Now that we compacted it, the problem
came back.

Anyone has any idea what could cuase this? Any ideas in fighting it?

Thank you.

 

[Allen Browne]

The root issue here is going to be identifying what is causing the database
to corrupt.

There's a list of things to work through here:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

You may also want to add a table so you can log users into and out of the
database. If there is a particular user/machine that logs in but does not
log out, it will help you identify the culprit.

 

[MrMMM]

Allen,
Thank you for the suggestion. In theory, I understand the possible
causes. Do you by chance have an idea on how to create such a table?
Another question is, why does this stop happening after the DB groes to
the enormous size (without compacting)?

Thanks.

 

[Allen Browne]

Logging is achieved by specifying a start up form that can be hidden. You
read the user's network name from this API call
http://www.mvps.org/access/api/api0008.htm
or their computer name from this one:
http://www.mvps.org/access/api/api0009.htm
so you can insert a record into your log table.

Keep this form hidden, and use its UnLoad event to simulate an Application
Quit event (which Access doesn't have.)

Since you have now logged everyone in and out, you can later determine which
machine or user is sometimes logging in but not out. That one may have a
faulty network card (falling off the network), a hardware problem
(overheating), a software problem, a power problem, a user who just shuts
down without logging out, or ...

 

[Tony Toews]

they stopped compacting. And after a while (once the DB file got fairly
large) the problem went away. Now that we compacted it, the problem
came back.

Interesting. And rather bizarre.

One thing I'd suggest doing is importing all the objects into a new
MDB. Occasionally there can be mild corruption which compact and
repair doesn't fix up.

Use the sysrels utility at the following location to copy the table
relationships layout window
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=12#12
Or use Save Restore Modify Relationship Window at
http://www.lebans.com/saverelationshipview.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[MingSu]

I had a similar problem after compacting the db back-end .... what I
found was that I had to compact the front-end as well. If I compacted
the front and back ends, I didn't have any problem. I also found that
importing the objects into a new database was helpful ... it let's you
know if/what is corrupting ....

 

[Tony Toews]

I had a similar problem after compacting the db back-end .... what I
found was that I had to compact the front-end as well. If I compacted
the front and back ends, I didn't have any problem.

Hmm, that's a possibility. Access does cache table settings. A97
had the obscure -1517 error message.
http://www.granite.ab.ca/access/reservederror1517.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm