use DAO to open a password-protected Access 2010 database

I want to write an Excel 2010 VBA code to open a password-protected Access 2010 database.

 

I wrote the following code:

--------------------------------------------------

Dim db as DAO.database

Dim rec as DAO.Recordset

Set db = DAO.OpenDatabase("c:\ProgramData\MyProgram\MyDatabase.accdb",False,True,"MS Access;PWD=MyPassword");

Set rec = db.OpenRecordset("select * from MyTable")

while not rec.EOF

    debug.print rec(0),rec(1),rec(2)

wend

rec.Close

db.Close

----------------------------------------

The code works as a VB6 program and as an Access 2010 VBA, but not as an Excel 2010 VBA. Can someone help? Thanks

 

 

Question Info


Last updated September 19, 2019 Views 13,810 Applies to:
Answer
Answer

Hi c_cheng,

 

In order to get this to work within Excel VBA, I used code similar to the sample code below.  Before doing this, you will need to set a reference to the Microsoft Access 14.0 Object Library and the Microsoft Office 14.0 Access Database Engine.

 

Public Function OpenEncryptedDatabase()

    'Need to reference Microsoft Access 14.0 Object Library

    'Need to reference Microsoft Office 14.0 Access Database Engine

    Dim DB As DAO.Database

    Dim rec As DAO.Recordset

    Dim wrkspc As DAO.Workspace

   

    Set wrkspc = Access.DBEngine(0)

    Set DB = wrkspc.OpenDatabase("<your database here>", False, True, "MS Access;PWD=password")

   

    Set rec = DB.OpenRecordset("select * from table1")

   

        Do Until rec.EOF

            Debug.Print rec.Fields(1).Value

            rec.MoveNext

        Loop

   

    rec.Close

    DB.Close

 

End Function

 

 

 

Hope that helps.

 

 

Best Regards,

Nathan Ost

Microsoft Online Community Support

14 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.