Access 9.0 backend growing by leaps & bounds!

[qu1ncy]

Hi,

I've got a split Access 9.0 application with 79 users. 31 of these folks do actual data entry and/or updates to existing records. The remaining users perform inquiries only.
Here's my problem:
The backend grows by 1.5 to 2.5 megs per day. This morning the db had grown from 6.3 to 7.4 megs in 1.5 hours. I reviewed the activity and very few records had been added in that time frame, only 120 updates.
In the past year I would archive records every 3 months to keep the size low. 3 months activity would generally result in the backend growing to 18 - 20 megs by the end of that period. Now, if I do nothing it will grow to 18 - 20 megs in a week to 10 days.
There's no way that we're adding that amount of data to the backend!
I've had to repair this db numerous times in the last 2 months. Prior to that it had been stable for over a year.
I've not been able to pinpoint the source of damage which forces a repair and have assumed a conflict arising with so many users. In an attempt to deal with the growing size I've opened the apps (front & back) in MSAccess /decompile
and recompiled in a VBA window.

Anyone have any suggestions as to what else I should be looking for? Anyone had a similar experience?

Any advice or suggestions will be welcome!

Q

 

[JeremyNYC]

Q,

Tons of questions.

Do you compact regularly? You should.

Do you use temporary records or temporary tables much? You shouldn't.

Do your users kick their powercords or otherwise shut down sloppily? They shouldn't.

I'm sure many others will follow with many more questions.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

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

 

[qu1ncy]

Hi Jeremy,

Thanks for the response.

1.) Prior to these recent calls to repair the backend, I would compact monthly. Now I have to compact weekly, and the amount of space recovered when compacting is astounding. A 15 - 18 meg size will compact to between 6.5 to 7.5 meg!

2.) There are no temp tables in use.

3.) Most of our users would never admit it if they did. They would just plug stuff back in. We try to educate as best we can. Shutting down 'sloppily' is also a matter for education.

While on the latter topic, I would love to find an effective way to quit the front end of idle users. 35 to 40 of our users are sales people who leave the app open all day long, querying repeatedly to watch their sales rack up. Imagine the effect on the backend when even half of those folks query at the same time!

Thanks for your input Jeremy,

Regards

Q