Access: backup a passworded database

Q:  Andy Walter writes:

I have been using your Function BackupBackend () for a few years now (Many many thanks) but recently I have had to password protect the backend of my database to stop Mr Sticky Fingers.

Unfortunately with the Password in place, I get the following Error.

Not only does it not create the Backup, it gives away the Password!  Is there a solution to this as I love the simplicity of the backup method.  I have disabled the feature temporarily until I find a solution.

Please can you help.

A:  It might be possible to fix this problem using one of these techniques for opening a passworded database:

Public Sub OpenACCDB()

Dim dbe As DAO.DBEngine
Dim dbs As DAO.Database
Dim strPath As String

Set dbe = DAO.DBEngine
strPath = "C:\Users\Helen Feddema\Documents\Orders with Password.accdb"
Set dbs = dbe.OpenDatabase(Name:=strPath, _
Options:=True, _
ReadOnly:=False, _
Connect:=";pwd=test123TEST456")

End Sub

Public Sub OpenMDB()

Dim dbe As DAO.DBEngine
Dim dbs As DAO.Database
Dim strPath As String

Set dbe = DAO.DBEngine
strPath = "C:\Users\Helen Feddema\Documents\Database with Password.mdb"
Set dbs = dbe.OpenDatabase(Name:=strPath, _
Options:=True, _
ReadOnly:=False, _
Connect:=";pwd=test123TEST456")

Set dbs = Nothing

End Sub

Public Sub OpenAnotherDatabase()

Dim appAccess As New Access.Application
Dim strDBNameAndPath As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim dbe As DAO.DBEngine
Dim strSQL As String
Dim strPassword As String

'Change to your db name and path
'strDBNameAndPath = _
"C:\Users\Helen Feddema\Documents\Orders with Password.accdb"
strDBNameAndPath = _
"C:\Users\Helen Feddema\Documents\Database with Password.mdb"
strPassword = "test123TEST456"
appAccess.Visible = True
appAccess.OpenCurrentDatabase filepath:=strDBNameAndPath, _
exclusive:=True, _
bstrPassword:="test123TEST456"

'Run a procedure
'appAccess.Run "PrintOrdersReport"

'Run a macro
'appAccess.DoCmd.RunMacro "mcrPrintOrdersReport"

'Run an action query
'appAccess.DoCmd.OpenQuery "qryDeleteSomeOrders"

'Run SQL code
strSQL = "DELETE tblOrders.ShippedDate " _
& "FROM tblOrders WHERE ShippedDate = #8/4/1994#;"
Debug.Print "SQL string: " & strSQL
'appAccess.DoCmd.RunSQL strSQL

Set dbs = Nothing
Set appAccess = Nothing

End Sub

Or just use the FileSystemObject’s CopyFile method – that is what I have been using recently, though I haven’t tested it with a passworded database.  Here is the syntax (assuming the fso variable has been set as Scripting.FileSystemObject, and the String variables have been set with the appropriate names, including paths):

fso.CopyFile strBackEndDB, strBackEndSaveName

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.