I have a database that is split and is set to make a backup copy of itself when it is started. This database loads multiple spreadsheets into a temporary table in the backend, summarizes the data via queries, and appends the summarized data to permanent tables. Before the database is closed, I would like the user to be able to compact the backend of the database. Is it possible to compact the backend of a database from the frontend? Or is this something that just needs to be done manually?
Frontend Compact Backend
- Thread starter LadyDi
- Start date
Or copy a static empty database file to a run-time file, use that file while the application processes, and when the app starts again it refreshes the run-time file from the read-only master.
That is what I have done for an automated replication process which downloads a lot of data from an iSeries (AS/400) and publishes that data to SQL Server.
RuralGuy
AWF VIP
- Local time
- Yesterday, 23:01
- Joined
- Jul 2, 2005
- Messages
- 13,825
As to your original question: Yes, you can compact a BackEnd db. You need to be sure you have closed all of your connections to it first. Then something like the following will do it.
Code:
Public Function Compact(stDataFile As String) As Boolean
'This function will backup and compact the mdb file given by the stDataFile path.
'Manually backup and test debug it first!
Dim stOutFile As String, stBackUpFile As String
If LCase(Right(stDataFile, 4)) <> ".mdb" Then
'-- Incomplete FileName string
Compact = False '-- Set Failure code
Exit Function
End If
stOutFile = Left$(stDataFile, Len(stDataFile) - 4) & ".CMP"
DoCmd.SetWarnings False
DoCmd.Hourglass True
'Delete Temporary OutputFile if exists
On Error Resume Next
Kill stOutFile
On Error GoTo Err_Function
'Backup
stBackUpFile = Left(stDataFile, Len(stDataFile) - 4) & ".BAK"
On Error Resume Next
Kill stBackUpFile
On Error GoTo Err_Function
FileCopy stDataFile, stBackUpFile
'Compact
DBEngine.CompactDatabase stDataFile, stOutFile, DB_LANG_GENERAL
'Delete Uncompacted Version
Kill stDataFile
'Rename Compacted Version
Name stOutFile As stDataFile
Compact = True
Exit_Function:
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Function
Err_Function:
Compact = False
Call LogError(Err.Number, Err.Description, "Compact() in basUtilities")
Resume Exit_Function
End FunctionSimilar threads
