To Query Or Not To Query

[DabblingDan]

(I have posted this same question in the VBA Coding area)
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

 

[MichaelRed]

I would offer the suspicion that the SECURITY is not complete / correct. SOME user is creating objects in the db. W/o regard to WHAT type of Object, Ms. A will always add the objects' space usage to the db, and will NOT release the space until a compact is done. This is true even if the object is deleted in the same session.

Another issue with your problem -at least as I read it- is that you have only the single (server) copy of the front end. This means (implies) that each time a user 'starts' the app, the entire front end is transfered over the net. This is generally NOT a good thing. Net police will NOT approve of this, AND it can easily compound your problem.

The 'recommended' aproach is for each user to have their own copy of the front end on their local workstation. Of course, this presents its' own issue(s) - like maintenance, but there are reasonable soloutions - discussed in these forums.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over