Compact & Repair

[Kundan]

How to Compact & Repair DB through VBA Code?

[OBP]

If you mean the currently open database it is
Application.SetOption "Auto compact", True

[ikanni]

Hi Kundan,
If you work with BackEnd and FrontEnd. I left you one code that compct and repair the backEnd and you should call form FrontEnd.


Sub CompactBackEnd()
On Error GoTo Err_CompactBackEnd
Dim DBPath As String
Dim DBPathTmp As String

' obtenemos la ruta de la base de datos vinculada
DBPath = DFirst("Database", "MSysObjects", "Database <> Null")
If Len(Dir(DBPath)) <> 0 Then
' construimos una ruta y nombre temporal para la base
' de datos a compactar
DBPathTmp = Left$(DBPath, Len(DBPath) - Len(Dir(DBPath)))
DBPathTmp = DBPathTmp & "~tmp" & Dir(DBPath)
' compactamos la base de datos
DBEngine.CompactDatabase DBPath, DBPathTmp
If Len(Dir(DBPathTmp)) <> 0 Then
' eliminamos la base de datos antigua
Call Kill(DBPath)
' renombramos la nueva base compactada
' al nombre de la original
Name DBPathTmp As DBPath
End If
End If

DBPath = ""
Exit_CompactBackEnd:
Exit Sub

[Kundan]

If you mean the currently open database it is
Application.SetOption "Auto compact", True


Thanks a Lot! GOD BLESS YOU!!!

[Kundan]

Hi Kundan,
If you work with BackEnd and FrontEnd. I left you one code that compct and repair the backEnd and you should call form FrontEnd.


Sub CompactBackEnd()
On Error GoTo Err_CompactBackEnd
Dim DBPath As String
Dim DBPathTmp As String

' obtenemos la ruta de la base de datos vinculada
DBPath = DFirst("Database", "MSysObjects", "Database <> Null")
If Len(Dir(DBPath)) <> 0 Then
' construimos una ruta y nombre temporal para la base
' de datos a compactar
DBPathTmp = Left$(DBPath, Len(DBPath) - Len(Dir(DBPath)))
DBPathTmp = DBPathTmp & "~tmp" & Dir(DBPath)
' compactamos la base de datos
DBEngine.CompactDatabase DBPath, DBPathTmp
If Len(Dir(DBPathTmp)) <> 0 Then
' eliminamos la base de datos antigua
Call Kill(DBPath)
' renombramos la nueva base compactada
' al nombre de la original
Name DBPathTmp As DBPath
End If
End If

DBPath = ""
Exit_CompactBackEnd:
Exit Sub




The code is great!! If you could translate the comments in English it would help me a lot to understand. GOD BLESS YOU!!!

[Kundan]

Hi Kundan,
If you work with BackEnd and FrontEnd. I left you one code that compct and repair the backEnd and you should call form FrontEnd.


Sub CompactBackEnd()
On Error GoTo Err_CompactBackEnd
Dim DBPath As String
Dim DBPathTmp As String

' obtenemos la ruta de la base de datos vinculada
DBPath = DFirst("Database", "MSysObjects", "Database <> Null")
If Len(Dir(DBPath)) <> 0 Then
' construimos una ruta y nombre temporal para la base
' de datos a compactar
DBPathTmp = Left$(DBPath, Len(DBPath) - Len(Dir(DBPath)))
DBPathTmp = DBPathTmp & "~tmp" & Dir(DBPath)
' compactamos la base de datos
DBEngine.CompactDatabase DBPath, DBPathTmp
If Len(Dir(DBPathTmp)) <> 0 Then
' eliminamos la base de datos antigua
Call Kill(DBPath)
' renombramos la nueva base compactada
' al nombre de la original
Name DBPathTmp As DBPath
End If
End If

DBPath = ""
Exit_CompactBackEnd:
Exit Sub

[Kundan]

What will be the code in "Err_CompactBackEnd" ????