run time error 1004 general odbc error excel 2003 vba

[Mentos]

hi,

the following is a macro i recorded. it's querying an myob database. it
works fine if i don't use variable sInvoice in the where clause. i.e. if i
replace with value '1234'. In its current state i get the run time error at

.Refresh BackgroundQuery:=False

Any help would be appreciated!!

Thanks,


Dim sInvoice As String
Dim sInvoiceType As String

sInvoice = InputBox("MYOB Invoice Number:", "Enter Invoice Number")
sInvoice = "'" & sInvoice & "'"

Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=Test;",
Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID,
Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber,
Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1,
Sales.DeliveryAddressL" _
, _
"ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines
ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE
ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= sInvoice ) AND
(Sales.InvoiceStatusID='Q'))" _
)
.Name = "Quote header"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Range("B20").Select

End Sub

 

[Kevin Beckham]

should read..

...
& "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " &
sInvoice & ") AND (Sales.InvoiceStatusID='Q'))" )

sInvoice was left as text in your query, not converted to a value

 

[Mentos]

Many thanks for your quick reply Kevin. That has definitely fixed variable
sInvoice. It is again erroring with the same error message, but this time,
with variable sInvoiceType. This is supposed to be a character, e.g. Q. I am
leaving it as a string but obviously this isn't correct. Can you kindly shed
some light into this?

Regards,


Dim sInvoice As String
Dim sInvoiceType As String

sInvoice = Worksheets("Sheet1").Range("B1").Value
sInvoiceType = Worksheets("Sheet1").Range("B2").Value

Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=Test;",
Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID,
Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber,
Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1,
Sales.DeliveryAddressL" _
, _
"ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines
ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE
ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice &
") AND (Sales.InvoiceStatusID = sInvoiceType ))" _
)
.Name = "Quote header"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Range("B20").Select

End Sub

 

[par_60056]

Many thanks for your quick reply Kevin. That has definitely fixed variable
sInvoice. It is again erroring with the same error message, but this time,
with variable sInvoiceType. This is supposed to be a character, e.g. Q. I am
leaving it as a string but obviously this isn't correct. Can you kindly shed
some light into this?

Regards,

Dim sInvoice As String
Dim sInvoiceType As String

sInvoice = Worksheets("Sheet1").Range("B1").Value
sInvoiceType = Worksheets("Sheet1").Range("B2").Value

Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=Test;",
Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID,
Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber,
Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1,
Sales.DeliveryAddressL" _
, _
"ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines
ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE
ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice &
") AND (Sales.InvoiceStatusID = sInvoiceType ))" _
)
.Name = "Quote header"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Range("B20").Select

End Sub

--
MS SBS 2003 SP2








- Show quoted text -

") AND (Sales.InvoiceStatusID = sInvoiceType ))" _

Because you have sInvoiceType inside the quotes, it is not a variable
but a litteral string.

In the original example, the field InvoiceStatusID is a string so I am
assuming that sInvoiceType is a string variable holding the letter Q
(or something)

So that line should be written as:
") AND (Sales.InvoiceStatusID = '" & sInvoiceType & "' ))" _

Note that there are single quotes inside the double quotes so that the
result has quotes around the contents of the variable sInvoiceType and
results in something like:
Sales.InvoiceStatusID = 'Q'

Peter

 

[Mentos]

Thank you so much, it now works!

Regards,

 

[Patryk Szudrowicz]

Hi,
We have also similar problem with our makro. Can you help us? The wierd thing is that sometimes it is working (makro is schaduled to run in every half an hour).

Debug is highlighting Selection.QueryTable.Refresh BackgroundQuery:=False

Sub UpdateReport()

'Cancel screenuodating, set start-criterias and show all sheets
Application.ScreenUpdating = False

Sheets("Instructions").Select
AutoWeekChangeActive = Range("B6").Value
RunWeekChange = 0

Call ShowSheets

'Update sheet Produced QTY (PLPRODMGD)
Sheets("PLPRODMGD").Select
ActiveSheet.AutoFilterMode = off
Range("A4:H2000").Select
Selection.ClearContents
Selection.Borders.LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
Range("A4").Select

Range("B3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False


Range("B3").Select
If ActiveCell.Offset(1, 0) = "" Then
ActiveCell.Offset(2, 0).Select
Else
Selection.End(xlDown).Select
End If
ActiveCell.Offset(0, -1).FormulaR1C1 = "1"


Range("A4").Select
ActiveCell.Formula = "=TEXT(D4&E4&B4,""#"")"
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("A3").Select

'Update sheet New production orders (PLPODATO)
Sheets("PLPODATO").Select
ActiveSheet.AutoFilterMode = off
Range("A4:K2000").Select
Selection.ClearContents
Selection.Borders.LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
Range("A4").Select

Range("D3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Range("D3").Select
If ActiveCell.Offset(1, 0) = "" Then
ActiveCell.Offset(2, 0).Select
Else
Selection.End(xlDown).Select
End If
ActiveCell.Offset(0, -1).FormulaR1C1 = "1"
ActiveCell.Offset(0, -2).FormulaR1C1 = "1"
ActiveCell.Offset(0, -3).FormulaR1C1 = "1"

Range("A4").Select
ActiveCell.Formula = "=TEXT(F4&G4&D4,""#"")"
Range("B4").Select
ActiveCell.Formula = "=TEXT(F4&G4&D4&""-""&C4,""#"")"
Range("C4").Select
ActiveCell.Formula = "=IF(H4="""","""",IF(H4=""DK"",""DK"",IF(H4=""PL"",""PL"",IF(H4=""GB"",""UK"",IF(H4=""FR"",""FR"",""Export"")))))"
Range("A4:C4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("A3").Select

'Update sheet New order proposals (PLPROPOSAL)
Sheets("PLPROPOSAL").Select
ActiveSheet.AutoFilterMode = off
Range("A4:N2000").Select
Selection.ClearContents
Selection.Borders.LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
Range("A4").Select

Range("E3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Range("E3").Select
If ActiveCell.Offset(1, 0) = "" Then
ActiveCell.Offset(2, 0).Select
Else
Selection.End(xlDown).Select
End If
ActiveCell.Offset(0, -1).FormulaR1C1 = "1"
ActiveCell.Offset(0, -2).FormulaR1C1 = "1"
ActiveCell.Offset(0, -3).FormulaR1C1 = "1"
ActiveCell.Offset(0, -4).FormulaR1C1 = "1"

Range("A4").Select
ActiveCell.Formula = "=CONCATENATE(D4,""-"",TEXT(B4,""RRRRMMDD""))"
ActiveCell.Offset(0, 1).Formula = "=IF(ISERROR(VLOOKUP(C4,'Non-production'!$A$11:$A$70,1,FALSE)),WORKDAY(C4,0,'Non-production'!$A$10:$A$70),WORKDAY(C4,-1,'Non-production'!$A$10:$A$70))"
ActiveCell.Offset(0, 2).Formula = "=IF(OR(M4=""520"",M4=""540""),WORKDAY(DATE(LEFT(I4,4),MID(I4,5,2),RIGHT(I4,2)),-3,'Non-production'!$A$10:$A$70),DATE(LEFT(I4,4),MID(I4,5,2),RIGHT(I4,2)))"
ActiveCell.Offset(0, 3).Formula = "=G4&H4&E4"
Range("A44").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("A3").Select

'Update sheet Released production orders (PLPOLOAD)
Sheets("PLPOLOAD").Select
ActiveSheet.AutoFilterMode = off
Range("A42000").Select
Selection.ClearContents
Selection.Borders.LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
Range("A4").Select

Range("C3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Range("C3").Select
If ActiveCell.Offset(1, 0) = "" Then
ActiveCell.Offset(2, 0).Select
Else
Selection.End(xlDown).Select
End If
ActiveCell.Offset(0, -1).FormulaR1C1 = "1"
ActiveCell.Offset(0, -2).FormulaR1C1 = "1"

Range("A4").Select
ActiveCell.Formula = "=IF(L4<""40"",TEXT(E4&F4&C4&""-""&20&""-""&M4&""-""&O4&""-""&G4,""#""),TEXT(E4&F4&C4&""-""&40&""-""&M4&""-""&O4&""-""&G4,""#""))"
ActiveCell.Offset(0, 1).Formula = "=TEXT(E4&F4&C4&""-""&IF(L4>=""40"",40,IF(L4<""40"",""20"",L4))&""-""&M4&""-""&G4,""#"")"
Range("A4:B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("A3").Select

'Insert todays date and place cursor in correct cell
Sheets("Control").Select
IDAG = Range("A27").Value

Sheets("Information").Select
Range("Q24:Q33").Copy
Range("J23").Select
If ActiveCell = IDAG Then
ActiveCell.Offset(1, 0).Select
ElseIf ActiveCell.Offset(0, 1) = IDAG Then
ActiveCell.Offset(1, 1).Select
ElseIf ActiveCell.Offset(0, 2) = IDAG Then
ActiveCell.Offset(1, 2).Select
ElseIf ActiveCell.Offset(0, 3) = IDAG Then
ActiveCell.Offset(1, 3).Select
ElseIf ActiveCell.Offset(0, 4) = IDAG Then
ActiveCell.Offset(1, 4).Select
Else
ActiveCell.Offset(1, 10).Select

'If weekday is not available, set criteria to run weekchange to 1 (=Active)
If AutoWeekChangeActive = "Yes" Then
RunWeekChange = 1
End If
End If

'Insert totals for produced orders
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("Q39:Q44").Copy
ActiveCell.Offset(15, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Insert totals for order intake
Range("Q52:Q61").Copy
ActiveCell.Offset(13, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Insert totals for order split of order intake
Range("Q67:Q77").Copy
ActiveCell.Offset(15, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

'Change week if activated
If RunWeekChange = 1 Then
Call ShowSheets
Call MakeWeekChange
Call HideSheets
End If

Range("A4").Select

'Insert time of updating
Sheets("FB1").Select
Range("C1") = Now

'Hide unneeded shets
Call HideSheets

'Restart screen-updatinga
Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub