Compact and Repair Access database from VBA


Public Function fnCompactRepairDatabase()

Dim sFilename1 As String
Dim sFilename2 As String
Dim sFilename3 As String

sFilename1 = fnFileDialog
sFilename2 = Mid(sFilename1, 1, InStr(1, sFilename1, ".") - 1) _
	& " Compact" _
	& Mid(sFilename1, InStr(1, sFilename1, "."), Len(sFilename1))

sFilename3 = Mid(sFilename1, 1, InStr(1, sFilename1, ".") - 1) _
	& " Old " & Format(Now(), "yyyymmddHHMMSS") _
	& Mid(sFilename1, InStr(1, sFilename1, "."), Len(sFilename1))

MsgBox sFilename1
MsgBox sFilename2
MsgBox sFilename3

Application.CompactRepair sFilename1, sFilename2
Name sFilename1 As sFilename3    ' Rename file .mdb to Old  .mdb
Name sFilename2 As sFilename1    ' Rename file Compact .mdb to .mdb

End Function