Compacting a Database (1 Viewer)

[BillBee]

I created a DB, size approx. 7.5MB. It is used for an annual event and each year data must be deleted from the previous year. Sometimes all fields in a table, others just certain fields in a table. All deleted with their respective Update Query At present I have set in Options to have the database to Compact on closing (i.e. each time the file closes). I am wondering if it would be preferable to have the file open, clicking on the File tab and using the Repair and Compact control on the Info Page. Any help would be appreciated.

 

[The_Doc_Man]

As a general rule, when you are going to meddle with a file in a way that changes its internal layout (which is one thing done by a C&R), you generally should make a copy of the file, do the C&R on the copy, and if it appears to work, overwrite the original and keep the copy as a backup. BUT - that is my OPINION, not based on hard Access rules. It comes from my experience as a systems administrator for a system running a large shared database. You may also (correctly) infer that when it comes to some things, I am a "belt and suspenders" man who NEVER wants to get caught with his pants down due to negligence or accident.

 

[BillBee]

Thanks for the reply. What is the best procedure then, for a file when a lot of data is deleted annually. The file grows in size and I get concerned it may crash. I do not employ the file with a FE and BE. Would this be a better option.

 

[The_Doc_Man]

If this is a private file, there is no particular reason to split unless you have sizing issues that are not addressed by the C&R. If this is a file shared among multiple users, it really should be split primarily due to bad effects introduced by file locking and destructive interference.

The reason Access grows in size is of course that it never deletes anything when you tell it to. It just marks the deleted data for deletion. Then when you do a C&R, it implements all pending deletions and thus reclaims the space. That is why it is called a "Compact & Repair." It is treated your MDB / ACCDB as though it was a trash compactor. Which in a sense is exactly what is happening except it is done digitially.

If you have data you want to remove from the Access file, you have to first decide on its disposition. Before I retired I had a process that would take my audit table and my working status table and search for items older than X where X was typically six months (but if I had to expel a little extra for some reason, I could.)

So every record that was subject to this process had an "Archive" flag and a "Date of Last Touch" flag. I had a query that if the "Last Touch" date was greater than X, the "Archive" flag would be set to TRUE. Then I had a second process that made an external file out of the tables in question. It wrote the files in text form because our security people said it was enough to do that. So once all the marked records were written to the formatted output file, we deleted those records.

We always coordinated some down time so that the first part could occur while users were up. You see, what I just described could be run with shared users in the system. Marking the records based on age? Didn't bother anyone and besides, it was a really quick query. Making a printable/text file as the archive for the marked records? Not much of a system strain at all. Deleting the marked files? Again, since I could do that with a query, it was a fast operation. The slowest part was formatting the records for the archive file.

For the system I was using (Ac2010), archiving one month's worth of audit logs took about 2 minutes for the marking, reporting, and deletion steps for a table with 80k to 120k records in it. Of course, the C&R had to be scheduled at an odd time since you don't want users in a database while it is actively being compacted.

 

[BillBee]

Thanks for the very informative answer. No this is not a shared file. I "developed" a program for an annual Bird Show for my Club. Many other clubs requested a copy for their own shows. As I said earlier I set the data base, through the Access options page Current Database to have the file Compact on close. I may reading between the lines and say the better option would be to untick the Compact on Close and ask other users of the file to use the C & R periodically to give a cleaner file.

 

[The_Doc_Man]

It is in fact "reading between the lines" but there have been commenters who suggested that the auto-C&R was sometimes not fully successful. Doing it manually, you have an easier time of it if something goes wrong, and you are right there to see the error in a situation where you could take remedial action.