I have an Access database, multi-user, which normally is about 180MB in size. Daily operations by the company users cause this DB to increase on a nominal rate, and so we compact the database periodically. However, I’ve recently added a couple of processes which link data through an ODBC link, and use action queries to place the data in local tables. It replaces the data on each action query. This causes the database to “bloat” in as much as 45MB per each event, and the DB has grown to 450MB in just a few days. A compaction always brings it back down to normal.
On a google search, I found someone who suggested changing the queries from Access queries to direct VB/SQL code and do ADO closes. So I did that, and testing in a single user test database showed me that it kept the bloat from occurring as much. However, when I put the code out into the multiuser live environment, where there are many other normal select queries or maketable queries occurring on a daily basis, my new SQL query code wasn’t any different than before. The bloat continues to occur.
What are suggestions on how to stop or prevent this huge increases from repeatedly occurring?