Automate Compact/Repair (1 Viewer)

RogerCooper

Registered User.
Local time
Today, 06:34
Joined
Jul 30, 2014
Messages
277
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

Wino Moderator
Staff member
Local time
Today, 06:34
Joined
Aug 30, 2003
Messages
36,118
In Options/Current Database there's a Compact on Close option.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:34
Joined
Oct 29, 2018
Messages
21,357
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

Immoderate Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 28, 2001
Messages
26,999
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

MVP / VIP
Local time
Today, 13:34
Joined
Jan 14, 2017
Messages
18,186
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.
 

Rx_

Nothing In Moderation
Local time
Today, 07:34
Joined
Oct 22, 2009
Messages
2,803
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

Registered User.
Local time
Today, 06:34
Joined
Jul 30, 2014
Messages
277
I am doing it with this simple function

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

bastanu

AWF VIP
Local time
Today, 06:34
Joined
Apr 13, 2010
Messages
1,401
I would recommend against running the compact and repair on a network drive, you might end up with a corrupted file.

Cheers,
Vlad
 

RogerCooper

Registered User.
Local time
Today, 06:34
Joined
Jul 30, 2014
Messages
277
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

Immoderate Moderator
Staff member
Local time
Today, 08:34
Joined
Feb 28, 2001
Messages
26,999
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.
 

cajulien

New member
Local time
Today, 09:34
Joined
Feb 3, 2020
Messages
25
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
 

Attachments

  • Admin Compact DB tool.accdb
    584 KB · Views: 220

isladogs

MVP / VIP
Local time
Today, 13:34
Joined
Jan 14, 2017
Messages
18,186
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

1591128355238.png


1591128255480.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:34
Joined
Feb 19, 2002
Messages
42,970
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

New member
Local time
Today, 09:34
Joined
Feb 3, 2020
Messages
25
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

Super Moderator
Staff member
Local time
Today, 09:34
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom