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
db.Close
Set db = Nothing
End Sub
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 bywirejpSaturday, February 27, 2016 3:27 PM