Consulting

Results 1 to 7 of 7

Thread: Compact & Repair

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Posts
    29
    Location

    Compact & Repair

    How to Compact & Repair DB through VBA Code?

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    2,870
    Location
    If you mean the currently open database it is
    Application.SetOption "Auto compact", True

  3. #3
    VBAX Newbie
    Joined
    Dec 2018
    Posts
    2
    Location
    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

  4. #4
    VBAX Regular
    Joined
    Nov 2018
    Posts
    29
    Location
    Quote Originally Posted by OBP View Post
    If you mean the currently open database it is
    Application.SetOption "Auto compact", True

    Thanks a Lot! GOD BLESS YOU!!!

  5. #5
    VBAX Regular
    Joined
    Nov 2018
    Posts
    29
    Location
    Quote Originally Posted by ikanni View Post
    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!!!

  6. #6
    VBAX Regular
    Joined
    Nov 2018
    Posts
    29
    Location
    Quote Originally Posted by ikanni View Post
    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

  7. #7
    VBAX Regular
    Joined
    Nov 2018
    Posts
    29
    Location
    What will be the code in "Err_CompactBackEnd" ????

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •