Disclaimer: I have never used ADO - I use DAO. But it looks like there are some errors in your code.
Originally Posted by
Sck
I have a password protected 2016 Access database (set to legacy encryption) that contains a query that I need to PULL the results of into Excel.
It looks like your connection string is wonky. Looking at The Connection Strings Reference, main page, there is an option to connect to Access.
Click on ACCESS , then, on the right side, click on the button "Access 2013". You should now be on the page Access 2013 connection strings
I think you need the connection string in the "With database password" sub header (There is more info if you click "With database password" sub header)
The general syntax of the connection string is
Code:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Christos\Desktop\Sample.accdb;Jet OLEDB:Database Password=MyDbPassword;
This line will give a wrong path:
Code:
AccessFile = ThisWorkbook.Path & "" & "Sample.accdb" ' Missing a backslash between the path and the dB file name.
This one should be OK.
Code:
'AccessFile = "C:\Users\Christos\Desktop\Sample.accdb"
After the ADO connection is working, you could use FSO to pick the file you want.
You first should get the ADO connection working on a dB without a password before you try to open a password protected dB.
I found this code. Maybe it will help
Code:
Sub ADO_Connection()
'Creating objects of Connection and Recordset
Dim conn As New Connection
Dim rec As New Recordset
Dim DBPATH As String
Dim PRVD As String
Dim connString As String
Dim sQuery As String ' Query is a reserved word, so I used sQuery
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\Christos\Desktop\Sample.accdb"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPATH & ";Jet OLEDB:Database Password=MyDbPassword;"
'opening the connection
conn.Open connString
'the query I want to run on the database.
sQuery = "qrRegions"
'running the query on the open connection. It will get all the data in the rec object.
rec.Open sQuery, conn
'clearing the content of the cells
Cells.ClearContents
'getting data from the recordset if any and printing it in column A of excel sheet.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
'closing the connections
rec.Close
conn.Close
End Sub
Also see How to connect to Access Database – ADO Connection String
Look at the example for "Connection with Access 2007/2010 Database"
Or maybe Connecting to a Database