Question "compact on close" taking a long time

[innocent_smoothie]

Access 2000

I have produced a database which provides management reports for a sales operation.

It involves about 8 make-table queries, then ends up with a report which is lnked to a select query. Note: the first 3 queries retrieve data via ODBC from an external data source which is a much bigger SQL database.

Everything works fine, but I noticed that the database kept growing in size, so I set it to "Compact on Close" in Tools/Options/General. Its size is now stable.

When closing the database, the compacting is taking over a minute. Can anyone tell me how I can reduce this? Is the compacting time a factor of the amount of data or of the number of make-table queries.

ALl the data in my database is fairly tidy, but I can't say the same about the SQL database which I am linking to at the start of the process.

 

[boblarson]

This should be a split database (frontend/backend) with a copy of the frontend on each users machine. If it is on their machine it should go fairly quickly. If it is on the network then that is bad and it will take much longer to compact (and is much more dangerous to do as corruption can occur).

 

[innocent_smoothie]

Thanks Bob for your late-night reply to my early morning question
Currently the whole of this database is on the user local machine. The only contact with the server is when the SQL tables in the external database are accessed via the ODBC routines

 

[boblarson]

Thanks Bob for your late-night reply to my early morning question
Currently the whole of this database is on the user local machine. The only contact with the server is when the SQL tables in the external database are accessed via the ODBC routines

Have you tried just deleting the data from the tables and appending it new instead of doing make table queries for those tables. It might work faster.

 

[innocent_smoothie]

I'll give that a try and I'll post again. But I think it might make the compacting take longer, because when you do the delete query it doesn't shrink the file, so you end up with lots of empty space. I thought that the make-table route might stop that from happening

 

[boblarson]

And then again, due to the nature of your data, it may take that to compact. I have, in the past, used a single field in a table to keep the count of how many times the database has been opened and I check the table and if the count is maybe 5 then I run compact. That way they don't have it compact every time but only after so many uses.