compressing a database

[jimdan]

I have a ms access database that I have split into a front and backend database.

After the user enters data into the database (stored in the backend), the DB grows to about 50 MB.

The user then runs the application (VB module stored in the front end DB) and the backend database grows to about 1.6 GB. When the database is closed it returns to a size between 50-100 MB



The performance of the application slows as the database grows in size which is why I need to compress it.



If the application is stopped part way and the DB is closed and re-opened and then the application is restarted it performs well until the DB grows again.



What I would like to know is how do I close the backend database when it is open and linked to the front end database to allow me to run the compress and then allow me to re-connect it to the front end database. Currently when I run the compression code it tells me I do not have exclusive access to the backend DB



Or does someone have a better suggestion on how to free up the space in the DB without doing a compress?

 

[Newman]

It sounds like there is a problem with these modules of yours. It is normal that it grows a little when playing with the datas, but it is not normal that modules make it top at 1.6Gb.
What are those modules doing exactly?
Can you send us these modules?
If you don't know which modules does make it grow, you could add breakpoints and refresh the file where the backend reside to see when it grows.

 

[jimdan]

compression

The modules create recods in temporary tables to do the analysis and then delete the data when done. The data is multi-dimensional and I create several entries determine the value and then store the summary and blow a way the detail. Since this is repeated for thousands of data point per iteration the number of times a table is filled and delete would be as I expect. The problem resides in the delete records. The table gets cleared out but the space in the mdb file is not release.

Only 1 modules creates the bulk of the data, all other modules are for set-up.

What I am need of is how I can close the DB, then do the compress and then re-open it. I found code to do the compress but it fails on a need of an exclusive lock. Do you know how to close the backend DB and then re-open it with a front end DB linked to it?