Compact & repair MS Access database

Call this function by using the following statement... Replace the My... variables accordingly

'=====================================================================================
'COMPACT AND REPAIR DATABASE WITH PASSWORD PROTECTION IF PROVIDED
'=====================================================================================
'    Call CompactRepairAccessDB(MyDatabasePathAndFile, MyPassword)
Public Sub CompactRepairAccessDB(ByVal sDBFILE As String, _
            Optional sPASSWORD As String = "")
Dim sDBPATH As String, sDBNAME As String, sDB As String, sDBtmp As String
sDBNAME = sDBFILE 'extrapulate the file name
Do While InStr(1, sDBNAME, "\") <> 0
        sDBNAME = Right(sDBNAME, Len(sDBNAME) - InStr(1, sDBNAME, "\"))
Loop
'get the path name only
sDBPATH = Left(sDBFILE, Len(sDBFILE) - Len(sDBNAME))

sDB = sDBPATH & sDBNAME
sDBtmp = sDBPATH & "tmp" & sDBNAME

'Call the statement to execute compact and repair...
If sPASSWORD <> "" Then
        Call DBEngine.CompactDatabase(sDB, sDBtmp, dbLangGeneral, , ";pwd=" & sPASSWORD)
Else
        Call DBEngine.CompactDatabase(sDB, sDBtmp)
End If
'wait for the app to finish
        DoEvents
'remove the uncompressed original
        Kill sDB
'rename the compressed file to the original to restore for other functions
        Name sDBtmp As sDB
End Sub

You might also like...

Comments

Mike J

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Brevity is the soul of wit” - Shakespeare