compact a backend database from a button on the frontend form

compact a backend database from a button on the frontend form

Post by Steve » Sun, 15 Feb 2004 08:59:07


you can with some code, as long as the link is not an
active/open link to it (like connected to your form).

Using code to compact, you will compact it to another
name, then delete the original file, and rename/copy the
compacted one back to the original.

to copy a file use: FileCopy 'FROM' 'TO'

to Delete: Kill 'path & file name'

to compact: DBEngine.CompactDatabase 'FROM', 'TO'

Note: the FROM and TO includes the path & file name as a
string.

I use this concept to create a back-up of the data(back-
end) & to compact the data when the user(single) exits. I
add a timestamp to the back-up file. It works out pretty
slick.

need more to get you going?, just ask.

SteveD



linking to 3 separate backend files. One of those files
is used for regular import, delete and re-import of large
data tables. This means the file gets huge.
front-end database that can compact the back-end database
file on the fly. My only solution right now is manual
compacting it regularlly.
 
 
 

compact a backend database from a button on the frontend form

Post by Naresh Nic » Sun, 15 Feb 2004 14:43:58

Hi:

This is code I normally use. It compacts database and then renames back to
origianl name.

Public Function CompactMaster() As Boolean
Dim cnMaster As ADODB.Connection
Dim i As Integer
Dim strMasterPath As String
Dim strMasterFolder As String
Dim strDest As String
Dim strBackUp As String
Dim fs As Scripting.FileSystemObject

On Error GoTo errHandler
'this is the database to compact
strMasterPath = "DatabasetoCompactFullPath.mdb"
Set fs = New Scripting.FileSystemObject
If Not fs.FileExists(strMasterPath) Then
MsgBox "Could not locate database at " & strMasterPath, vbInformation
Set fs = Nothing
Exit Function
End If

strMasterFolder = fs.GetFile(strMasterPath).ParentFolder.Path
strMasterFolder = strMasterFolder & "\"
strDest = strMasterFolder & "DatabasetoCompactFullPath_AfterCompact.mdb"


On Error Resume Next
Err.Number = 0
DBEngine.CompactDatabase strMasterPath, strDest

If Err.Number <> 0 Then
MsgBox "Compact and Repair failed. Database could be in use in another
by another user. " & Chr(13) & Chr(10) & "Error description is " &
Err.Description, vbInformation
Set fs = Nothing
Exit Function
End If

On Error Resume Next
Err.Number = 0
fs.DeleteFile strMasterPath
If Err.Number <> 0 Then
MsgBox "Compact and Repair failed. Database could be in use in another
project (or) by another user on network. " & Chr(13) & Chr(10) & "Error
description is " & Err.Description, vbInformation
Set fs = Nothing
Exit Function
End If

On Error Resume Next
Err.Number = 0
Name strDest As strMasterPath
If Err.Number = 0 Then
MsgBox "D-Tools Mastertable database compacted", vbInformation
CompactMaster = True
Else
MsgBox "Compact failed. Error description is " & Err.Description & ".
Will attempt to use backup database -- " & strBackUp, vbInformation
On Error Resume Next
Err.Number = 0
fs.CopyFile strBackUp, strMasterPath
If Err.Number = 0 Then
MsgBox "Backup sucessfully restored", vbInformation
Else
MsgBox "Could not restore Mastertable database from backup -- " &
strBackUp & Chr(13) & Chr(10) & ". Please rename " & strBackUp & " as " &
strMasterPath, vbInformation
Exit Function
End If
End If

Set fs = Nothing

Exit Function
errHandler:
'Call modGlobal.GenericErrorHandler("CompactMaster", Err.Description,
Err.Number)

End Function


Naresh Nichani
Microsoft Access MVP



backend files. One of those files is used for regular import, delete and
re-import of large data tables. This means the file gets huge.
that can compact the back-end database file on the fly. My only solution
right now is manual compacting it regularlly.

 
 
 

compact a backend database from a button on the frontend form

Post by TWNMZWFuIE » Sat, 21 Feb 2004 02:06:08

Thank you very much for that section of code...
I inserted it into my code (changing the file names appropriately) and this line of code is giving me an error...

Dim fs As Scripting.FileSystemObject

When I compact I get "User-defined type not defined"...
Is there an Add In I need to make this work?