Question Database auto compact on idle. (1 Viewer)

[bokarinho]

Hello, I would like a piece of information from you experts. In my access applications i have used the database split built in functionallity to split my frontend and backend tables. But i have a problem with databases that grow very bit, to 1.5 GB over the day. I have created a mail mechanism and when the database grows to that level i recieve a mail, ask users to get out of the database and compact the database. But this is tiding. I have a scheduled job that compacts databases early in the morning before users logged on. But during day work databases grow enormous again. Well is there a way by VBA code to create an auto compact function that on idle will log off all users in the database and compact it?Thanks in advance.

 

[MarkK]

That's too much bloat. Do you create and destroy a lot of temporary tables? One solution is to programmatically create a temporary database file, create temporary tables in that file, link to those results as required, then delete that temp database.

 

[bokarinho]

That's too much bloat. Do you create and destroy a lot of temporary tables? One solution is to programmatically create a temporary database file, create temporary tables in that file, link to those results as required, then delete that temp database.

Thanks for your answer, very helpful.

But my tables are fixed, i do not create and destroy anything. When you say create and destroy you mean load data and delete them? For example read some records from a file and later on docmd.runSQL "DELETE * FROM ... ";
Good thought with the other database.