Compact and Repair - initiate from a Ribbon Menu item or by timer RRS feed

  • Question

  • What is the easiest way to provide an administrative user with the option to Compact and Repair the backend database ?

    Do I need to employe Shell32.exe to do this ? In other words, close the front-end, fire off a shell command to start-up a special script stored on the backend database ?

    Looking for ideas here....the backend is shared by 10 users....and they are automatically disconnected from the backend daily. Ideally, the C&R should be done at night when all users are logged I am also thinking about a timer-based execution.

    Wednesday, May 15, 2019 2:36 PM

All replies

  • Use a windows Scheduled Task to execute a simple batch file, in the middle of the night when no one will be in the database, that invokes the /compact command line switch.

    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support:
    MS Access Tips and Code Samples:

    Wednesday, May 15, 2019 3:49 PM
  • You can automate it by running a scheduled program that execute un access database containing something like this:

        Dim backend As String
        Dim copia As String
        Dim Msg As String
        backend = "c:\database_be.accdb"
        Msg = backend & vbCrLf & vbNullString
        DBEngine.CompactDatabase backend, backend & ".new"
        copia = Dir(backend & ".bak")
        If copia <> vbNullString Then Kill backend & ".bak"
        Name backend As backend & ".bak"
        Name backend & ".new" As backend
    Marco Dell'Oca

    Wednesday, May 15, 2019 4:14 PM
  • Thanks Daniel.....This is probably the safest and most reliable method.....kicking this off from within Access is what happens if the administrator is not in the front-end ?

    Greatly appreciate this advice. 
    Attempting to allow the admin to do this "on demand" is not a good idea.....too many problems with concurrent users.

    • Edited by Syswizard Wednesday, May 15, 2019 4:30 PM
    Wednesday, May 15, 2019 4:24 PM
  • Thanks Marco - but won't the CompactDatabase method fail if users are still logged on to the front-end ?

    Wednesday, May 15, 2019 4:26 PM
  • The program return an error if user are still logged on but you can run a loop and try to do it until no error occour.

    Marco Dell'Oca

    Wednesday, May 15, 2019 5:01 PM
  • Before compacting always see if a lock file exist, if so abort, otherwise proceed.

    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support:
    MS Access Tips and Code Samples:

    Wednesday, May 15, 2019 8:06 PM
  • Yep - I'll have to script this logic...good idea. Maybe PowerShell is the way to go here.

    Note: In the front end, I have a piece of timing logic which closes all forms and exits Access every day at midnight....thus preventing the lock-out.

    Wednesday, May 15, 2019 8:52 PM
  • It's very simple you have to make a .bat file with inside:

    "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "D:\backupdatabase.accdb"

     And then schedule it.

    Marco Dell'Oca 

    Thursday, May 16, 2019 10:20 AM