How to get data from an encrypted 2016 access database in excel 2016

Question

How to get data from an encrypted 2016 access database in excel 2016

Anonymous

I am trying to get data from a 2016 access database into excel 2016. If I apply no encryption to the access database it's very easy to access tables in excel simply by selecting Data->get-data->from-database->From Microsoft access database then browse to the location of my accdb file. When I encrypt my access database and follow the same procedure, not surprisingly the connection fails with the error “Not a valid password” as at no stage have I been asked for one. I would have thought Excel would have just prompted me for the access encryption password, but it does not and as far as I can see there is nowhere to specify an access database password in excel. Is there a simple way to get this working without resorting to VBA scripting?

***Post was moved to the appropriate forum category.***

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous

Hi Tom,

Thank you for your reply and suggestion I will have a look at that path. I had hoped it would be a question of modifying a connection string buried somewhere in Excel to provide the password, but no luck yet in finding one. At the moment I have started on a VB solution and this code has successfully returned the data from a table called Stocks in an encrypted MS access file called Stocks.accdb.

Sub Get_stocks_table()

Dim strSQL As String

Dim adoRecSet As New ADODB.Recordset

Dim connDB As New ADODB.Connection

connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; Data Source=D:\Stocks.accdb; Jet OLEDB: Database

Password=xxxxxxxx"

strSQL = "SELECT * FROM Stocks"

adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic

Sheets("Stocks").Select

ActiveSheet.Range("A1").CopyFromRecordset adoRecSet

adoRecSet.Close

connDB.Close

End Sub

But it would be much easier if Excel handled this directly as it does for non-encrypted access databases as I can see a lot of tidying up will be needed for this to work (e.g. deleting all of the spreadsheet data before query is executed)

Paul

0 comments

1 additional answer

Answer 1

Tom van Stiphout 40,216 MVP Volunteer Moderator

I don't know. Good question for an Excel forum.

Would it help to populate the Excel file from Access using DoCmd.TransferSpreadsheet? Look it up in the Help file.

0 comments