Compact & Repair

How to Compact & Repair DB through VBA Code?

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

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.

Code:

---

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

---

Quote:

---

Originally Posted by OBP

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

---

Thanks a Lot! GOD BLESS YOU!!!

Quote:

---

Originally Posted by 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.

Code:

---

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!!!

Quote:

---

Originally Posted by 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.

Code:

---

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

---

---

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