Microsoft Excel VBA connect to multiple Microsoft Access DB

Question

I'm currently trying to connect to 2 separate .mdb file and perform an INNER JOIN.

So, I have 2 .mdb (Sample1.mdb & Sample2.mdb). Both are password protected.

When I tried to connect, it showed an error stating that "it's already opened exclusively by another user, or you need permission...".

Did the error occurred because I did not insert the password property in this statement? If yes, how do I insert password property into this statement?

[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & Application.ActiveWorkbook.Path & "\Sample1.mdb]?

Here's my entire sample code:

Dim Conn As ADODB.Connection
Dim resultSet As ADODB.Recordset

Set Conn = New ADODB.Connection

sqlStatement = "SELECT * FROM [Excel 8.0;HDR=YES;IMEX=2;DATABASE=" &
Application.ActiveWorkbook.Path & "\Sample1.mdb].[SampleData$] a INNER JOIN 
[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & Application.ActiveWorkbook.Path &
"\Sample2.mdb].[SampleInfo$] b ON a.Index = b.Index WHERE a.idCode = 2"

     With Conn
            .Provider = "Microsoft.ACE.OLEDB.12.0"
            .ConnectionString = Application.ActiveWorkbook.Path + "\Sample2.mdb"
            .Properties("Jet OLEDB:Database Password") = "password"
            .Open

             Set resultSet = .Execute(sqlStatement)
         End With

Erik A · Accepted Answer · 2018-09-30 10:13:59Z

1

There are some strange things about this.

[Excel 8.0;HDR=YES;IMEX=2; is a connection string to connect to Excel files, not MDB databases. Since you're passing it an MDB database as a parameter, there are bound to be strange errors. Since connecting to Access is native to Access, you just need to specify the path.
I'd specify all connection parameters in the connection string, including uid.

Dim Conn As ADODB.Connection
Dim resultSet As ADODB.Recordset

Set Conn = New ADODB.Connection

sqlStatement = "SELECT * FROM [" &
Application.ActiveWorkbook.Path & "\Sample1.mdb].[SampleData$] a INNER JOIN [SampleInfo$] b ON a.Index = b.Index WHERE a.idCode = 2"

     With Conn
            .Provider = ""
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source="Application.ActiveWorkbook.Path + "\Sample2.mdb;Jet OLEDB:Database Password=""password"""
            .Open
             Set resultSet = .Execute(sqlStatement)
         End With

To query off of an Access database with a password, specify the password in the ISAM connection string:

"SELECT * FROM [MS Access;PWD=password;DATABASE=" &
Application.ActiveWorkbook.Path & "\Sample1.mdb].[SampleData$] a INNER JOIN 
[MS Access;PWD=password;DATABASE=" & Application.ActiveWorkbook.Path &
"\Sample2.mdb].[SampleInfo$] b ON a.Index = b.Index WHERE a.idCode = 2"

Of course, you need to make sure you use proper connection strings to both MDB databases: with the current code, Sample2 is protected but Sample1 is not, and Sample2 is protected by encryption (only prompts password), not user-level security (prompts password and username).

edited Sep 30, 2018 at 10:13

answered Sep 30, 2018 at 5:52

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Arane Over a year ago

Oh no... then is there any possible method I can implement to connect both Sample1 & Sample2 database in order to use 1 SQL statement to perform the innerjoin and get the result set?

Erik A Over a year ago

Huh? You can supply the password in the query, but first verify you can open the first database.

Erik A Over a year ago

@Arane I've edited the code to include the password in the SQL statement

Arane Over a year ago

Just a curious question... are there better practices for this kind of scenario whereby VBA connect 2 separate mdb and perform an INNER JOIN?

Erik A Over a year ago

Not really afaik. I tend to use DAO instead of ADODB because it's native to Access, but that's not very relevant.

Collectives™ on Stack Overflow

Microsoft Excel VBA connect to multiple Microsoft Access DB

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related