Automate Compact/Repair

[RogerCooper]

I have large database that I have run many automated queries running overnight, starting at midnight. I compact the database manually each day. I would like to compact it automatically before I run each night, due to database corruption issues.

I tried using Task Scheduler by running with the path name in quotes followed by " /compact", but it opens up Access and says that it is an invalid path. (I have verified the path).

Is there any other way I can automate compact/repair?

 

[pbaldy]

In Options/Current Database there's a Compact on Close option.

 

[theDBguy]

Hi. This should be possible using Task Scheduler, but is your database split? If so, there are other options if you can't get the C&R to work.

 

[The_Doc_Man]

See this link:

https://stackoverflow.com/questions/1460129/ms-access-how-to-compact-current-database-in-vba

Create a very small database.

Run THAT through task scheduler using the /X:macro-name feature.

Write a macro to support this action. It will only require a few lines.

Run some code (via RunCode option) in a FUNCTION (because RunCode won't run a SUB).

Have the function apply the method described in the link. If you have more than one file involved, remember to C&R each file.

Don't forget to include an Application Quit in the macro.

 

[isladogs]

In Options/Current Database there's a Compact on Close option.

Compacting will reduce file size and perform various other actions but it will not solve most corruption issues.

You should be aware that compact on close has been known to cause corruption on occasions. I would recommend that you run a backup first if you decide to switch this feature on.

More importantly I recommend you identify the cause of your corruption issues and then deal with them.

 

[RogerCooper]

In Options/Current Database there's a Compact on Close option.

I need to go in and out of the database during the day, so Compact on Close takes too long.

 

[bastanu]

Please feel free to use my free utility that you can schedule with the Windows Task Scheduler.
http://forestbyte.com/ms-access-utilities/fba-ms-access-back-end-compacter/
Cheers,
Vlad

 

[Rx_]

Is this a Shared Database for multiple users using Linked Tables?
If the application and DB are just on a single PC the solutions is probably much different than if for example you have 50 concurrent users on Citrix front-end connected to the Back-End database.
Agreed compact on close was good in theory, but can lead to other issues.

There is some point when multiple users are connected that migration to SQL Server is really worth it. If just for the automatic backups, it might be justified for just a few users.

 

[RogerCooper]

I am doing it with this simple function

Function CompactAndCopyBack(FileName As String)
Kill "s:\temp.accdb"
Application.CompactRepair FileName, "s:\temp.accdb"
FileCopy "s:\temp.accdb", FileName
End Function

 

[bastanu]

I would recommend against running the compact and repair on a network drive, you might end up with a corrupted file.

Cheers,
Vlad

 

[RogerCooper]

I would recommend against running the compact and repair on a network drive, you might end up with a corrupted file.
Vlad

I have no choice but to run on network drives. I have never had compact & repair cause corruption.

 

[The_Doc_Man]

As long as the network drive is in-house and has a robust backbone (carrier) at the physical layer, C&R is not an issue. In fact, where the file is actually located is almost NEVER an issue. It is (a) can you get there via SMB protocol, and (b) is the connection robust enough to be reliable for minutes at a time without an outage?

With the Navy, I had Gigabit Ethernet for some drives, and an 8 Gigabit Fiber network for the Network-Attached Storage drives. Nary a hiccup! But that was all in-house stuff, so never had connection issues.

 

[irish634]

I wrote this several years ago.... might help
https://www.access-programmers.co.uk/forums/showthread.php?t=221398

 

[cajulien]

Tool to let your clients compact themselves
I couldn't find a general tool to let clients perform compact/repair on their own, so I made one that I'm happy to share and get your feedback. I've got it running at one client's site but there surely somethings I could improve. Hope this helps.

Admin Compact DB tool.accdb
This db is an admin tool that should be copied to any folder.
The tool allows users to select Access files from current folder to compact, folder can be switched and refreshed,
it performs the compact(s) of selected accdb files, and logs the event(s) in a local table.

Features:
Let clients manage their compacting, they can choose which file(s) to compact on their own time
Manages hidden and locked files, back-ups, and attempts to recognize "data" files
Log feature permits auditing of past compacting so they can keep track
Limitations:
No recursive tree crawling, only one folder's Access files are
managed, the current db folder by default, users can switch folder.
Password protected databases will ask for psw.
Client must schedule running the tool using their own enterprise scheduling;
e.g., I've told my user to set an appointment in Outlook every 3 months

Tiny bug: ordering of file list box columns is somehow reversed, wish someone could tell me why

 

[isladogs]

Tool to let your clients compact themselves
...
Tiny bug: ordering of file list box columns is somehow reversed, wish someone could tell me why

Thanks for providing this tool for others to use.
Your tiny bug is easily solved: change the scroll bar align property from Left to Right or better still to System

 

[cajulien]

Thanks for providing this tool for others to use.
Your tiny bug is easily solved: change the scroll bar align property from Left to Right or better still to System

View attachment 82546

View attachment 82545

Gosh, I have to imagine I'd tinkered with that without noticing it's impact. Thanks so much.

 

[isladogs]

Easily done!

 

[Pat Hartman]

Running a C&R on a network drive FROM code running on the same drive is not a problem. The problem arises when you try to compact a db on a network drive FROM a different computer. This causes an enormous amount of network traffic, is very slow, and is what leads to corruption. If you must run the compact from a different computer, best practice is to rename the network file, copy it to your local drive, compact it locally, and then put it back with the original name.

If the bloat is caused by using temp tables, you can get around that problem by using a template database that includes ONLY the temp tables, properly indexed and compacted. Call the db Temp_mytempdb.accdb. Copy it to the production directory and remove the Temp_ prefix. Link the production process to the tables.

To start the process, delete the existing copy of the mytempdb.accdb. Copy the Temp db to your production folder and remove the Temp_ prefix by renaming the db.
Run the code to populate them and then run the reports and batch process.
Then run all your batch process.

Since you replace the temp db at the start of each process and you don't need to relink the tables since you didn't change their names or the name of the temp db, there is no compacting needed.

That should get you back to a more normal weekly/monthly compact of the production BE.

Since you replace the

 

[cajulien]

Running a C&R on a network drive FROM code running on the same drive is not a problem. The problem arises when you try to compact a db on a network drive FROM a different computer.

I hear you, that's why by default the tool looks into the current folder and that's the way it should be used. But since users can change the folder I can see how the cross-computer issue might happen; in hindsight I'm thinking I shouldn't let users change the folder and this issue would never happen. I.e., the tool ONLY allows you to C&R something in the current folder.

 

[Pat Hartman]

I.e., the tool ONLY allows you to C&R something in the current folder.

Makes sense to me but they also shouldn't be compacting shared databases either and I'm not sure how you will control that.

It is really better to just buy a tool like the one sold by www.fmsinc.com It doesn't cost much and you can automate it for them.