Excel import query from encrypted Access DB

[setis]

Dear all

I have a query in a protected access DB that I need to import into excel.

I've searched the forum and on the internet, but I couldn't find a comprehensive answer explaining how to do it.

I know how to import the query from a non-encrypted db.

Thanks

 

[Ranman256]

the EASY way is to be in the Access db, and EXPORT the query to excel.
Is this not possible?

 

[setis]

the EASY way is to be in the Access db, and EXPORT the query to excel.
Is this not possible?

It would be, but I would prefer if the excel spreadsheet was dynamic.

 

[Ranman256]

Here's code snippet I use for end users. You can put the password in the connection string , or in the rst query path.

In excel VBE, BE SURE ADO is put in VBE References: alt-F11, tools, references, Microsoft ActiveX Data Objects x.x Library


connect to the db when the workbook opens
usage:

Private Sub Workbook_Open()
  CONNECTDB()
end sub

get data from db to spreadsheet:
GetLateRecords()

public goConn as ADODB.Connection
public gDb


'-----------------
sub GetLateRecords()
'-----------------
dim rst
dim sSql as string
 
sSql = "select * from table where [late]=true"
Set rst = getRst(sSql)

range("A1").select
ActiveCell.CopyFromRecordset rst
set rst = nothing
end subs


'-----------------
Public Function getRst(ByVal pvQry) As ADODB.recordset
'-----------------
Dim rst As ADODB.recordset

On Error GoTo errGetRst

If goConn Is Nothing Then ConnectDB

Set rst = CreateObject("ADODB.Recordset")
With rst
    Set .ActiveConnection = goConn
    .CursorLocation = adUseClient
    .PASSWORD = "MYPASSWORD"
    .Open pvQry
End With
Set getRst = rst

 'Set getRst = goConn.Execute(pvQry)
Exit Function
errGetRst:
MsgBox Err.Description, , "getRst():" & Err
End Function


'-----------------
sub ConnectDB()
'-----------------
'BE SURE ADO is put in VBE References:  alt-F11, tools, references.

gDB = "\\server\folder\myDb.accdb"

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & gDB
OLEDB:EngineType=4"
Set goConn = New ADODB.Connection
goConn.Open sConnect
end sub

 

[setis]

Thanks.

Sorry I am such a newby in VBA. Please see the attached.

Should I put the first part (Private Sub Workbook_open() ) in the "ThisWorkbook" object and the other in the Sheet1 object?

 

[Ranman256]

you also must insert a module and paste all the other code in it.
then CONNECTDB will work.