MS Access DoCmd.Transferdatabase when source has a password

Question

I need to import tables from various databases on a monthly basis. Once the tables are imported, the databases are archived and not looked at again.

I have the following VBA code which works fine when a DB is not password protected:

Private Sub ImportTheData(ByVal dbImport As String)

DoCmd.SetWarnings False 'Turn OFF display alerts

'Import the full activity & comments table from the Import DB to a temporary table
DoCmd.TransferDatabase acImport, "Microsoft Access", dbImport, acTable, "tbl_Activity", "tbl_TempActivity", True
DoCmd.TransferDatabase acImport, "Microsoft Access", dbImport, acTable, "tbl_Comments", "tbl_TempComments", True

'code continues ...

The last parameter (storelogin) is set to true, but there seems to be no way to programmatically set those login parameters (password).

When I run the code, the user is prompted to enter the password (despite the SetWarnings = False). As I'm importing dozens of files each time this is not a viable solution.

Is there a way to programatically import tables using DoCmd.TransferDatabase when a file is password protected and if so how?

Answer 1

Open the database with DAO, supplying the password, then you can import the tables.

Public Sub ImportEncr()

    Const dbImport = "D:\DbEncr.accdb"
    Const sPassword = "foobar"

    Dim DB As DAO.Database
    Set DB = DBEngine.OpenDatabase(Name:=dbImport, Options:=False, ReadOnly:=False, Connect:=";PWD=" & sPassword)

    DoCmd.TransferDatabase acImport, "Microsoft Access", dbImport, acTable, "tblEncr", "tblEncr", False

    DB.Close
    Set DB = Nothing

End Sub

StoreLogin applies to linking tables from ODBC databases.