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
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