Open Access table with password

[stepper997]

I’m putting together a macro to open Access tables and transfer them to Excel. This is the code for one of them.

Sub AccessHeadCount()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=C:\Desktop\Database\HD_5062.mdb;DefaultDir=C:\Desktop\Database;" ), Array( _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransact" ), Array("ions=0;Threads=3;UserCommitSync=Yes;")), Destination:=Range("A1"))
.Sql = Array( _
"SELECT `DAT_AGENT_DATA`.row_date, `DAT_AGENT_DATA`.acd, `DAT_AGENT_DATA`.split, `DAT_AGENT_DATA`.extension, `DAT_AGENT_DATA`.logid, division_id" & Chr(13) & "" & Chr(10) & "FROM `C:\Desktop" _
, "\Database\HD_5062`.`DAT_AGENT_DATA` `DAT_AGENT_DATA`")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With
End Sub

This one works like a champ, but when I try to use it open a table that’s linked ODBC, it requires a password. I have the password, that’s not a problem. The problem is I’d like to write code in to automatically put the username/password in. I guess somewhere to put –Password=””; User ID=””.

Thanks for any help.
_________________
Give a man a fire, and he will be warm for a night.
Light a man on fire, and he will be warm for the rest of his life.

 

[TheToeOfPooh]

Just a thought

There a several related threads in the Excel section.
How about adapting one of them?
I recently did this and as a result was able to add a password to an ON-Click Event which if a known password was entered, then ran another macro which contains code similar to yours.

Bernard