Microsoft Access automatic repair and compact with database password RRS feed

  • Question

  • Once a week I need to automatically repair and compact an access database.  I can use the WINDOWS TASK SCHEDULER under Control Panel > Administrative Tools > Task Scheduler to do this and simply add the /repair /compact command line switches to the shortcut.  

    However this does not work because the database has a database password (not security but "Set Database Password found under File > Info).  The /pwd command line switch does not work because that switch is for Access security related to the workgroup information file.  

    I have been able to create some code in one Access database that will open the password protected database and enter the password info automatically (see below) however I cannot get the database to repair and compact close both databases and then quit Access.  

    I need this to happen without the end user ever knowing it occurred.  Therefore if you open one access database file with opens the password protected file, when it is done repairing and compacting they both need to close and quit Access.

    Below is the code that opens the password protected database without user intervention.  db1.mdb is the password protected database.  ABC123 is the password.

    Option Compare Database
    Option Explicit

    Sub OpenPasswordProtectedDB()

       'Define as Static so the instance of Access
       'doesn't close when the procedure ends.
       Static acc As Access.Application
       Dim db As DAO.Database
       Dim strDbName As String
       strDbName = "C:\Folder1\db1.mdb"
       Set acc = New Access.Application
       acc.Visible = True
       Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=ABC123")
       acc.OpenCurrentDatabase strDbName
       Set db = Nothing
    End Sub
    Saturday, February 27, 2016 1:53 PM


  • Hi. Not sure why you need to regularly compact and repair your database but you should be able to modify this backup routine to do what you want. The CompactDatabase method has a password argument. Hope that helps...
    • Marked as answer by wirejp Saturday, February 27, 2016 3:27 PM
    Saturday, February 27, 2016 3:21 PM