QueryTables

[cmcgrath]

I have a query in a macro that works fine if you use a value in the
'V_SCHOOL_DETAIL.LOC=' clause. If I substitute a variable, I get an
ODBC 1004 error on the statement '.Refresh BackgroundQuery:=False'.

I am pretty sure the sql statement is correct. Below is the code that
works (hardcoded value) and the code that does not work (parameter).
Has anyone run into this before and can offer a solution?

Code that works:****************************
Sub FTE_detail()
'
' FTE_detail Macro
' Macro recorded 10/7/2003 by GCPS
'
'
Dim Counter As Integer

Dim SchArray(75, 2) As String

' column 1 school codes

SchArray(1, 1) = "090"
SchArray(2, 1) = "095"

' column 2 school names

SchArray(1, 2) = "ANNISTOWN ELEMENTARY"
SchArray(2, 2) = "ARCADO ELEMENTARY"


For Counter = 1 To 2

Workbooks.Open Filename:= _
"R:\Progs\MARYHELEN\2004FTE\FTENew\SCHOOLS\Errors XXXX D
HEADINGS.XLS"
Range("A4").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SAMPLE;UID=****;PWD=****;MODE=SHARE;DBALIAS=SAMPLE;",
_
Destination:=Range("A4"))
.CommandText = Array( _
"SELECT V_SCHOOL_DETAIL.LOC, V_SCHOOL_DETAIL.ERROR_CODE,
V_SCHOOL_DETAIL.PERMNUM, V_SCHOOL_DETAIL.STUDENT_NAME,
V_SCHOOL_DETAIL.FIELD_NAME, V_SCHOOL_DETAIL.FIELD_CONTENT" & Chr(13) &
"" & Chr(10) & "FROM FTE.V_SCHOOL_DETAIL V_SCH" _
, "OOL_DETAIL" & Chr(13) & "" & Chr(10) & "WHERE
(V_SCHOOL_DETAIL.LOC=" & "'090'" & ")")
.Name = "FTE_" & SchArray(Counter, 1)
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Columns("A:A").ColumnWidth = 8.33
Columns("B:B").ColumnWidth = 8.33
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").ColumnWidth = 30
ActiveWorkbook.SaveAs Filename:= _
"R:\Progs\MARYHELEN\2004FTE\FTENew\SCHOOLS\Errors 1002 D " &
SchArray(Counter, 2) & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

Next Counter
End Sub

Code that doesn't work:****************************
Sub FTE_detail()
'
' FTE_detail Macro
' Macro recorded 10/7/2003 by GCPS
'
'
Dim Counter As Integer

Dim SchArray(75, 2) As String

' column 1 school codes

SchArray(1, 1) = "090"
SchArray(2, 1) = "095"

' column 2 school names

SchArray(1, 2) = "ANNISTOWN ELEMENTARY"
SchArray(2, 2) = "ARCADO ELEMENTARY"


For Counter = 1 To 2

Workbooks.Open Filename:= _
"R:\Progs\MARYHELEN\2004FTE\FTENew\SCHOOLS\Errors XXXX D
HEADINGS.XLS"
Range("A4").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=SAMPLE;UID=****;PWD=****;MODE=SHARE;DBALIAS=SAMPLE;",
_
Destination:=Range("A4"))
.CommandText = Array( _
"SELECT V_SCHOOL_DETAIL.LOC, V_SCHOOL_DETAIL.ERROR_CODE,
V_SCHOOL_DETAIL.PERMNUM, V_SCHOOL_DETAIL.STUDENT_NAME,
V_SCHOOL_DETAIL.FIELD_NAME, V_SCHOOL_DETAIL.FIELD_CONTENT" & Chr(13) &
"" & Chr(10) & "FROM FTE.V_SCHOOL_DETAIL V_SCH" _
, "OOL_DETAIL" & Chr(13) & "" & Chr(10) & "WHERE
(V_SCHOOL_DETAIL.LOC=" & "SchArray(Counter, 1)" & ")")
.Name = "FTE_" & SchArray(Counter, 1)
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Columns("A:A").ColumnWidth = 8.33
Columns("B:B").ColumnWidth = 8.33
Columns("C:C").EntireColumn.AutoFit
Columns("D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").ColumnWidth = 30
ActiveWorkbook.SaveAs Filename:= _
"R:\Progs\MARYHELEN\2004FTE\FTENew\SCHOOLS\Errors 1002 D " &
SchArray(Counter, 2) & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

Next Counter
End Sub


Thanks.............

 

[Dick Kusleika]

cmcgrath

(V_SCHOOL_DETAIL.LOC=" & "SchArray(Counter, 1)" & ")")

Remove the quotes around the variable, e.g.

(V_SCHOOL_DETAIL.LOC=" & SchArray(Counter,1) & ")")

 

[cmcgrath]

I have tried that: > "WHERE (V_SCHOOL_DETAIL.LOC=" & SchArray(Counter,
1) & ")")

It has the same result: ODBC 1004 error.

I also tried this with query to an access table. When I changed the
hardcoded item to a variable, I got the ODBC 1004 error on the
'.Refresh BackgroundQuery:=False' statement.

Any other suggestions?

 

[Dick Kusleika]

C

I just noticed that in your "code that works," there are single quotes
around the string. Try this

WHERE(V_SCHOOL_DETAIL.LOC=" & Chr(39) & SchArray(Counter,1) & Chr(39) & ")")

Chr(39) is a single quote.

 

[cmcgrath]

I figured it out......... You have to add " ' " around the variable.

& "'" & SchArray(Counter, 1) & "'" &

That is a double quote, single quote, double quote

 

[Dick Kusleika]

Great minds think alike

Good work.