Database Growth

[renenger]

I have been reading through previous posts regarding database bloat and have not been able to find anything that might fix my problem. We have been using this database for many years. We have the same amount of people accessing the database.

The problem is now we are pulling the information from a .csv file rather than inputting it. All the information is put into an AS400 system and then a .csv file is exported every 15 minutes. I have a front-end interface that runs all the append and update queries, make table queries, etc.. None of this is done on the backend. When no users are accessing the information this update occurs every 15 minutes and the database does not grow in size.

However, when the updates are occuring and there are users accessing the database, the within a few hours it grows to 2 GB and is unusable. I have to boot everyone off and noon and compact it. This is not working very well. Is there a way to slow the growth? As I said, no transactions are being done on the backend. All the tables are linked to user interfaces.

Any help would be greatly appreciated.

 

[RuralGuy]

Have you tried creating a Temp.mdb and import into that db and do all of your manipulation there. Then when all is complete, move the table to the real db. It may not work for you because of the real time nature of the data in the table but it is worth looking into.

 

[ghudson]

I advise against updating the tables while a user is in the db [front end]. The odds of a table lock are high and the data might not be updated.

I also advise against using make table queries. Just empty the table before you import the data. I understand that you are doing all of this in the front end. Are there any error tables being created during the import process? Can you turn the warnings back on so that you can watch what is happening when a new csv file is imported and updated while the users are in the front end?

Also, does each user have their own front end copied to their hard drive. All of the front ends should be linked to the one back end.

 

[renenger]

Database growth

Yes, each user has the interface on their hard drive and it is linked to the tables on the server.

I moved the Admin interface (the one creating all the queries and doing all the updating) to a different machine. So far, the database is not growing as quickly as it was.

The users are not really updating any information in the tables. We have about 10 fields that are being updated periodically throughout the day. The information is coming from our AS400 system and we are basically running custom reports against that information.

 

[The_Doc_Man]

The problem with Access (any version so far) is that it uses its own db space to build "lists" of things - like queries and such. Whether you open the file remotely or locally makes no difference. When you have lots of users sharing the db and they open the queries or reports, they create lists of records that match the query. Because the list will be opened for an unknown amount of time, Access builds the list in the db. But because it is a dynamic, demand-memory-paging system, garbage collection is not possible (well, not really guaranteed to be feasible) while the DB is open. So your bloat source is the bunch of dynamic query lists.

Which makes me ask these questions: How big is a freshly cleaned DB and what percent of the DB is involved in these "custom" reports that your 10 users can access simultaneously?

The bigger those query lists I mentioned, the faster the bloat growth rate.