Frequent But Irratic Need to Repair Database 1

[DabblingDan]

I have a rather simple Access 97 for logging and tracking certain type of correspondence. The user interface is contained in one mdb and linked to another mdb with the data tables.
The application is being run on NT server (both mdbs are on the server nothing is on the client's machine).
There are about 100 records added each day and a least that many are updated.
The number of users varies from 2 - 16 at any given time, although there have only been 6-8 users actually adding records (the rest would perform searchs and update memo fields.
The problem is the mdb with the source code (call it C) will periodically give an error message requiring a repair. I have not been able to identify a pattern. It has occurred immediately after a COMPACT, with as few as 2 users utilizing the application and has also gone for days with no problems at all. When the error is encountered, all users exit the program (many then receiving the error message) and the database is repaired. And things are again fine. This has happened as much as 3 times in the same day and has not occurred at all for up to 10 days.
Recently, we have also seen similar behavior with the data mdb (D).
There are no bound forms, recordsets are created to fill local variables and addnew and updates are performed after confirming the desire to include the data in the table.
My system's group feel that there are too many users (more than 10). That has been the extent of their resolution.
Any insights? I need some knowledge assistance.
Thanks

 

[RickSpr]

I can't say I've seen a problem like this, but I have a suggestion. The more troublesome database has the code in it. I'm assuming it has no data, or only read-only data (reference tables or the like). Repair shouldn't be necessary unless some kind of updating has failed. But what kind of updating would occur in a code-only database?

Answer: Compiling. If some of your code isn't compiled, then each user who accesses that code will cause it to be compiled. If two users happen to access it concurrently, the runtime on each user's machine will compile it separately, and both will try to store the compiled code back in the database. That could cause a lost pointer, lost data page, or the like, and that would require repair.

Solution: Compact and repair the database, then do a Compile and Save All Modules before turning it back over to the users.

That may not be it, but it's worth a try. Rick Sprague

 

[DabblingDan]

Thanks,it is a good idea. I have implemented your suggestion first thing this morning. I'll let you know how things go.

Thanks again!!!!

 

[DabblingDan]

Well, it is back to the drawing board for me. As in the past, right after COMPACTING (and recompiling) the "C" database needed to be repaired 3 times.

More confused than ever - any suggestion gratefully received and attempted!!

 

[RickSpr]

Dang! Sorry, it's beyond my experience.

You might want to post a fresh thread to get attention from other guys. Once a thread gets this long, the gurus figure it's already being resolved and are less likely to read it. (This forum just has too many posts to follow them all. I can spend 4 hours here and only hit a fraction of them.) Rick Sprague

 

[KellyK]

Has anything on your network changed recently? We are having the same problems, except after we repair the database our Main form is then missing. We have not determined the source of the error, but we always had the MSysCompactError table present after reopening the db, so we've now taken off "compact on close" and that seems to have helped. We were told there were too many users as well, but each db was built for only ONE user at a time. Coincidentally the problem occured the same week network services made some changes which they failed to inform us of.

 

[DabblingDan]

My network folks are not admitting to anything.
I tried a few things and the problem has gotten better (2.5 weeks no problems). However, I can't say which one it was since I was fervorishly making adjustments I didn't follow the rule of only one change at a time.
Here are some of the things I did.
I used temporary querydefs where ever possible.
Made sure every stored query that was used to change was a snapshot
And, the steps I think had the most impact - After a single compact 1. I made sure that the code was compiled and saved
2. Ran a macro that opens every query in design mode and then closes and saves ite
3. I opened the application and ran the various searches.
After I did this, on the first day the database grew only about 50k(I assume for the add functionality which I did not run since I did not want to put bad data into the tables). Since then, there has been NO database bloat. It has not changed size for 18 days.