Change ODBC connection in excel VBA

[boazstuff]

Hi,

I have this connection which is working fine now. But i like it to be dynamic and picking up the new range from excel. I tried changing the cells in red to fPath but it wouldn't work.

Hope someone can enlighten. Thanks.

Currently, cell E3 read as: C:\DATA\DEMO17_DB.mdb

Sub SQLQuery()

Dim varConnection
Dim varSQL
Dim fPath As String

fPath = Range("E3").Value


Range("A1").CurrentRegion.ClearContents

varConnection = "ODBC; DSN=MS Access Database; DBQ=C:\DATA\DEMO17_DB.mdb; Driver{Driver do Microsoft Access(*.mdb)}"

varSQL = "SELECT DISTINCT AGENT from AGENTNAME"

With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=ActiveSheet.Range("A1"))
.CommandText = varSQL
.Refresh BackgroundQuery:=False
End With


End Sub

 

[ukmikeb]

Hi

Something like -

varConnection = "ODBC; DSN=MS Access Database; DBQ=" & fPath & "; Driver{Driver do Microsoft Access(*.mdb)}"

hth

Mike

 

[boazstuff]

Thanks Mike! I read somewhere on having " but missed out the & thingy.
Thanks!