Code to connect to encrypted / password-protected database

Question

At start up, my front end front.accdr database links to a back end back.accde using:

DoCmd.TransferDatabase acLink, "Microsoft Access", "back.accde", acTable, "aTable", "aTable"

The back end really needs to be encrypted and so I need to use a password to connect to the encrypted DB. How would I do this?

Answer 1

If you can't find a way to include the database password with TransferDatabase, you can create the table link as a new member of the DAO.TableDefs collection.

I confirmed this code works in an Access 2007 ACCDR file.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Dim strDbFile As String
Dim strLinkName As String
Dim strPassword As String
Dim strSourceTableName As String

strDbFile = "C:\share\Access\PasswordEquals_foo.accdb"
strPassword = "foo"
strSourceTableName = "Contacts"
strLinkName = "link_to_contacts"

strConnect = "MS Access;PWD=" & strPassword & _
    ";DATABASE=" & strDbFile
Debug.Print strConnect
Set db = CurrentDb
Set tdf = db.CreateTableDef
tdf.Connect = strConnect
tdf.SourceTableName = strSourceTableName
tdf.Name = strLinkName
db.TableDefs.Append tdf

Beware that, even with an ACCDR, anyone who can read the link's TableDef.Connect property will be able to see the stored database password. For example, the following code displays "MS Access;PWD=foo;DATABASE=C:\share\Access\PasswordEquals_foo.accdb" in the Immediate window.

Dim dbRemote As DAO.Database
Dim objWorkspace As Workspace
Set objWorkspace = CreateWorkspace("", "admin", "", dbUseJet)
Set dbRemote = objWorkspace.OpenDatabase("C:\share\Access\Database2.accdr")
Debug.Print dbRemote.TableDefs("link_to_contacts").Connect
dbRemote.Close
objWorkspace.Close

So the link compromises the security of an encrypted db file.