To Query Or Not To Query...

[DabblingDan]

I have an ongoing problem with a front-end/back-end database arrangement. The front-end contains the user interface while the back-end has the actual tables. Both are run from the same network directory by less than 20 users at a time.
The front-end continually gets corrupted necessitating a REPAIR. Since I am not writing anything to tables in the front-end, I am assuming the issuing is somehow related to how Access itself continues to increase the size of that datbase.
I have made sure the code has been compiled and saved. Also, I have run the various saved queries. I have replaced some SQL queries with temporary QueryDef.
The database growth rate has been radically reduced - increasing only by 2 one day and sometimes going a couple of days without any change (the about 150 records added daily and several hundred updated).
However, some activity (which I have been unable to identify) still will suddenly cause the data base to grow more quickly and then cause it to become corrupt.
Finally, to the question -
Which query approach has the least amount of impact on the database? Saved queries (parameters taken from fields on an open form), Recordsets created from SQL, or Recordsets created from a temporary QueryDef?
- Just an aside, the corruption problem occurs more frequently after a COMPACT Database is performed.

Sorry for taking so long. I will appreciate any insight anyone can offer.

Thanks

 

[JerryDennison]

Since it's a front end and contains no tables, have you tried giving every user their own personal copy to run from?

 

[Geekmomz]

We had a similar problem here, but solved it by giving each user his/her own copy of the front end to run. That created its own problem as to how to update the database on every workstation without having to copy to each one. I got around that by creating a login script that copies the most current production version of the front end to each user's workstation as they log on in the morning. This also solves the problem of the front ends bloating. This has turned out to be both very simple and very effective.

Marla

 

[lewatkin]

One other thing you may try, convert the front end to a .MDE file. That worked for me. Any of the above should work as well.

Lee