Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72

    how to compact and repair a batch of files ?

    Hi,



    I use several hundreds of access files which are all located in one same folder, and i'd like to run a "compact and repair" on all of them.
    To make sure I get understood properly : I'd like to avoid having to manually open each one of these files, pressing "compact and repair", shutting it down, and opening the nect files, etc...

    Is there any application which automatically can do that?
    Or maybe a VBA macro? I guess it's possible with a VBA macro, but I don't know how to do that.

    Thank you

    Nicky

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a blank access db, then put in the code to scan the folder and compact each db....

    Code:
    '--------------
    Public Sub CompactAll()
    '--------------
    cycleThruAllFilesInDir "c:\Data\Access apps\"
    End Sub
    
    
    '--------------
    Public Sub cycleThruAllFilesInDir(ByVal pvDir)
    '--------------
    Dim FSO, oFolder, oFile, vFile
    Dim sTxt As String, sFile As String
    Dim acc As Access.Application
    Dim control As Office.CommandBarControl
    Dim bRun As Boolean
    Dim i As Integer
    
    
    On Error GoTo errGetFiles
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder(pvDir)
    Set acc = New Access.Application
    
    
    For Each oFile In oFolder.Files
         bRun = False
         If (InStr(oFile.Name, ".accdb") > 0) Or (InStr(oFile.Name, ".mdb") > 0) Then
                  'verify its a db file
              If (InStr(oFile.Name, ".accdb") > 0) Then
                i = InStr(oFile.Name, ".accdb")
                If i = Len(oFile.Name) - 5 Then bRun = True
              Else
                i = InStr(oFile.Name, ".mdb")
                If i = Len(oFile.Name) - 3 Then bRun = True
              End If
              
                  'compact file here
              If bRun Then
                vFile = pvDir & oFile.Name
                acc.OpenCurrentDatabase (vFile)
                acc.Visible = True
                acc.SetOption ("Auto Compact"), 1
                acc.Quit acQuitSaveNone
             End If
         End If
    Next
    
    
    endit:
    Set oFile = Nothing
    Set oFolder = Nothing
    Set FSO = Nothing
    Set acc = Nothing
    Set control = Nothing
    
    
    MsgBox "Done"
    Exit Sub
    
    
    errGetFiles:
      MsgBox Err.Description, , Err
      Resume endit
    End Sub

  3. #3
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Hi ranman256,

    Thank you for helping me.

    So I created a new access file, created a module and pasted your code.
    Then I replaced "c:\Data\Access apps" with my folder path.

    Did I do it right?
    Can this access file be located anywhere on my computer?

    When i run the CompactAll() macro, I get an error. In french it says "Erreur de compilation : Type défini par l'utilisateur non défini", and when I press "OK", this part of the code is selected : "control As Office.CommandBarControl"

    What should I do?

  4. #4
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Can anyone help me please?

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sorry, we don't speak French. What is the error you are getting? It looks like a compile error. These must be sorted out BEFORE trying to use the database in any fashion. Open the VBA editor and click on Debug>Compile and fix any errors.

  6. #6
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Here is the screenshot :



    I don't know what a compile error is, but yes "compilation" means "compile".

    I guess there's a mistake in the code. Or some "definition" is missing.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It is a reference that is missing. Go to Tools>References and select the Microsoft Office 14.0 Object Library (it may not be 14, that is Access 2010).

  8. #8
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by aytee111 View Post
    It is a reference that is missing. Go to Tools>References and select the Microsoft Office 14.0 Object Library (it may not be 14, that is Access 2010).
    Thank you Aytee111,

    I selected Microsoft Office 15.0 Object Library
    The previous issue doesn't occur anymore.
    But now when I open the module and launch the macro (F5), it seems to work a few seconds, and then this message pops up :



    What should I do?

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Don't speak French.

  10. #10
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Aren't these error messages common access ones?

    Basically, it says : Automation Erro. The object is disconnected from its clients.

    No idea what this means. I don't know what the number means either.

  11. #11
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Are there any references that say MISSING?

  12. #12
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    I have no more information but what can be seen in the screenshot.
    I could try to attach the file, but it's just an empty file with the macro which is pasted in post#2 inside.

    Does it work on your PC?

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I copied two lines and it seems to work now for me.

    Code:
              If bRun Then
                vFile = pvDir & oFile.Name
                acc.OpenCurrentDatabase (vFile)
                acc.Visible = True
                acc.SetOption ("Auto Compact"), 1
                acc.Quit acQuitSaveNone
             End If
         Set acc = Nothing
         Set acc = New Access.Application
         End If
    Next

  14. #14
    nicoboss is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Sep 2017
    Location
    http://www.webagency321.com
    Posts
    72
    Quote Originally Posted by aytee111 View Post
    I copied two lines and it seems to work now for me.

    Code:
              If bRun Then
                vFile = pvDir & oFile.Name
                acc.OpenCurrentDatabase (vFile)
                acc.Visible = True
                acc.SetOption ("Auto Compact"), 1
                acc.Quit acQuitSaveNone
             End If
         Set acc = Nothing
         Set acc = New Access.Application
         End If
    Next

    Hello Aytee111,

    Sorry, I don't understand what you suggest to add/remove from the original macro.

    Can you paste here the whole new macro please?

  15. #15
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This is not a macro, it is code as copied from post # 2. You need to set the path to the correct value right in the beginning.

    Code:
    '--------------
    Public Sub CompactAll()
    '--------------
    cycleThruAllFilesInDir "C:\...\...\...\"
    End Sub
    
    
    '--------------
    Public Sub cycleThruAllFilesInDir(ByVal pvDir)
    '--------------
    Dim FSO, oFolder, oFile, vFile
    Dim sTxt As String, sFile As String
    Dim acc As Access.Application
    Dim control As Office.CommandBarControl
    Dim bRun As Boolean
    Dim i As Integer
    
    
    On Error GoTo errGetFiles
    
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder(pvDir)
    Set acc = New Access.Application
    
    
    For Each oFile In oFolder.Files
         bRun = False
         If (InStr(oFile.Name, ".accdb") > 0) Or (InStr(oFile.Name, ".mdb") > 0) Then
                  'verify its a db file
              If (InStr(oFile.Name, ".accdb") > 0) Then
                i = InStr(oFile.Name, ".accdb")
                If i = Len(oFile.Name) - 5 Then bRun = True
              Else
                i = InStr(oFile.Name, ".mdb")
                If i = Len(oFile.Name) - 3 Then bRun = True
              End If
              
                  'compact file here
                Debug.Print oFile.Name
              If bRun Then
                vFile = pvDir & oFile.Name
                acc.OpenCurrentDatabase (vFile)
                acc.Visible = True
                acc.SetOption ("Auto Compact"), 1
                acc.Quit acQuitSaveNone
             End If
         Set acc = Nothing
         Set acc = New Access.Application
         End If
    Next
    
    
    endit:
    Set oFile = Nothing
    Set oFolder = Nothing
    Set FSO = Nothing
    Set acc = Nothing
    Set control = Nothing
    
    
    MsgBox "Done"
    Exit Sub
    
    
    errGetFiles:
      MsgBox Err.Description, , Err
      Resume endit
    End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Compact and Repair
    By cwitt11 in forum Access
    Replies: 4
    Last Post: 12-08-2015, 11:52 AM
  2. Compact & Repair Code
    By aamer in forum Access
    Replies: 6
    Last Post: 11-04-2014, 03:51 PM
  3. Compact and repair
    By data808 in forum Access
    Replies: 4
    Last Post: 03-30-2014, 03:22 AM
  4. Compact and Repair
    By Cyberice in forum Access
    Replies: 3
    Last Post: 03-18-2014, 03:22 PM
  5. Compact & Repair, Will I Run Into Problems?
    By robsworld78 in forum Access
    Replies: 1
    Last Post: 01-10-2012, 05:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums