ODBC with variable

Posted by Rob on August 09, 2001 5:51 AM

I am trying to query using an ODBC driver and a variable. How can I replace 'ROB' in (EMP.EMP_CD='ROB') with the variable myEmp?

Dim myEmp
myEmp = ROB

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Excel for live;UID=abc;PWD=123;ConnectString=live;", _
Destination:=Range("A1"))
.Sql = Array( _
"SELECT EMP.EMP_CD, EMP.FNAME" & Chr(13) & "" & Chr(10) & "FROM MISC.EMP EMP" & Chr(13) & "" & Chr(10) & "WHERE (EMP.EMP_CD='ROB')")
.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

Posted by Larry Liberto on November 19, 2001 5:23 AM

Well, usually I do something like this . . .

Dim mySQL as string
Dim myEmp
myEmp = ROB

mySQL = "SELECT EMP.EMP_CD, EMP.FNAME" & Chr(13) & "" & Chr(10) & "FROM MISC.EMP EMP" & Chr(13) & "" & Chr(10) & "WHERE (EMP.EMP_CD='" & myEMP & "'')"

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Excel for live;UID=abc;PWD=123;ConnectString=live;", _
Destination:=Range("A1"))
.Sql = mysql
.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