- adding many new records
- large number of user edits
- running action queries (insert, update, delete)
- design changes, especially to forms & reports
- temp tables that are emptied & repopulated
- using the Name AutoCorrect options
- Access replication
- WorkGroup Security with encryption
NOTE: It is essential for all users to exit the database, and to make a backup copy of the file before performing the following...
One solution is to do regular maintenance on the database. In Access versions thru 2003, you can use
Tools ~ Database Utilities ~ Compact & Repair Database
which will in most cases shrink the file considerably. The typical reduction can be 10-20%, but can much greater if the Compact has not been done for a long time. In fact, I've seen one case where there was 100:1 reduction for a file that had never been compacted.
An alternate solution is to turn on the option for Auto-Compact, using
Tools ~ Options ~ General, and check the box Compact on Close
...however this option may crash the app if you are running on Terminal Server and/or using Access replication and/or the Access runtime version.
So, how often to perform this task? Good question. It depends on the list of reasons above. You could actually write some code to check the file size, compare it to some arbitrary limit, and then alert the user if the size exceeds that limit. Here is some sample code to check the file size:
Function GetMyFileSize() As Long
' this requires a reference to
' Microsoft Scripting Runtime
' c:\windows\system32\scrrun.dll
Dim fso As FileSystemObject
Dim oFolder As Folder, oFile As File
'
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(CurrentProject.Path)
Set oFile = oFolder.Files(CurrentProject.Name)
GetMyFileSize = oFile.Size
Set oFile = Nothing
Set oFolder = Nothing
Set fso = Nothing
End Function