Compacting Access Databases on an NTFS file system

[Carl Schroeder]

We have a situation where 25% of our server space is consumed by Access
databases because users do not take the time to compact them. What is the
best way to automate the compacting of databases on an NTFS file system? I
saw a note posted by an Microsoft MVP saying that a recipe for db corruption
is to use compact on close.

 

[Jerry Whittle]

1. Buy more hard drives.

2. Take a serious look at all those Access databases.

a. Maybe they could be revised or combined with other databases to be more
efficient. It might be time to upgrade them to something like SQL Server.

b. See how many are backups or unused. Move them off to tape storage.

 

[Tony Toews [MVP]]

a. Maybe they could be revised or combined with other databases to be more
efficient. It might be time to upgrade them to something like SQL Server.

Only if they have duplicate data.

b. See how many are backups or unused. Move them off to tape storage.

The following utility will assist in this task.
Access 2003 Conversion Toolkit
http://www.microsoft.com/downloads/...76-5D89-450A-B977-980A9841111E&displaylang=en

We were told by Microsoft that IT in Fortune 500 companies were very
surprised to see how active Access was used in the Enterprise. Real
eye opener for them. Hehehe

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Ken Snell]

I have VBA code example for how to do backup/compacting of ACCESS file from
another ACCESS database:

CompactBackendDatabaseFile_Custom
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#CompactBE

You could use code similar to this to run automatic compacts of those
database files.

 

[Daniel Pineault]

You could easily create a bat/vbs file and automate the server to execute it
on a weekly basis (Sunday night at 4am - for instance).

Your bat/vbs file should make a copy and then perform the compact.

Realistically thought, Jerry's answer of get more hard drive space is very
valid as well. What about user accountability/training. You could also
implement quota forcing them to keep their databases clean/compacted...

No easy answer, it depends on your corporate culture, management support,...
but you have a few valid solutions to address your problem.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.