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!

 

[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.

 

[cajulien]

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

What do you mean by "shared" in this case? Shared across front-ends? Across users? Across back-ends? I'm not sure I see the issue you're referring to so please educate me pls

 

[cajulien]

Ha yes, I understand. I had assumed each user has a local FE that links to server BE i.e., no one is sharing FE (I should stipulate this architecture assumption). Agreed that there's generally no reason to C&R FE.

FE users DO NOT use such an admin tool: thus the name "Admin Compact DB tool". I suppose I could setup up a password to make sure end users never stumble onto it and try to use.

It may indeed be worth it to buy such a tool as you mention. I always try to see if some simple tool might do an adequate job first; we'll see how it goes with this client.

Thanks so much for sharing your knowledge