Error on refresh backgroundquery - help please

[RichardLOZ]

I am trying to run an ODBC query to an informix database. I have done this
is previous organisations without any trouble, but for some reason I am now
getting errors.

Run-time error '1004'
Application-defined or object-defined error

And for the life of me I cant work it out.

CODE:

Sub CreateQT()

Dim ConnectStr As String
Dim SqlStr As String
Dim Statsqry As QueryTable

ConnectStr =
"ODBC;DSN=CMS;UID=xxx;PWD=xxx;Database=store7;FetchBufferSize=30;Host=xx.x.xx.xx;NoLoginBox=No;Options=;Protocol=TCP/IP;ReadOnly=Yes;ServerOptions=;ServerType=Informix 2000"

TodayStr = Format(Date, "yyyy-mm-dd")
YestStr = Format(Date - 1, "yyyy-mm-dd")

SqlStr = "SELECT hsplit.row_date, synonyms.item_name,
Sum(hsplit.acdcalls), Sum(hsplit.acceptable), Sum(hsplit.abncalls),
Sum(hsplit.abntime), Sum(hsplit.holdcalls), Sum(hsplit.holdtime),
Max(hsplit.maxocwtime), Sum(hsplit.acdtime), Sum(hsplit.acwtime),
Sum(hsplit.transferred), Sum(hsplit.anstime), Sum(hsplit.callsoffered)" &
Chr(13) & "" & Chr(10) & "FROM root.hsplit hsplit, root.synonyms synonyms" &
Chr(13) & "" & Chr(10) & "WHERE synonyms.value = hsplit.split AND
((synonyms.item_type='split') AND (hsplit.row_date={d '" & YestStr & "'}) AND
(hsplit.split>=22 And hsplit.split<=31))" & Chr(13) & "" & Chr(10) & "GROUP
BY hsplit.row_date, synonyms.item_name, hsplit.split" & Chr(13) & "" &
Chr(10) & "ORDER BY hsplit.split"

Set Statsqry = Sheets(1).QueryTables.Add(Connection:=ConnectStr,
Destination:=Range("A1"))
With Statsqry
.CommandText = SqlStr
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

End Sub

Any assistance with this would be much appreciated. Thanks

Richard

 

[sebastienm]

Hi,
On which line is the error.
Assuming on the Refresh line:
I had the same problem long ago. If i remeber correctly, it would run fine
at first but wouldn't a second time. And if i remember correctly, i had to
comment out the two lines:
' .SavePassword = True
' .SaveData = True
.... for some reasons.
Regards,
sebastien

 

[RichardLOZ]

Tried that but no luck. Still getting the same error, but unlike your issue
I cant even get it to run once for me.

Richard

 

[RichardLOZ]

Ok it seems as though it is in the sum(field) part ot the query. If I take
the sum part out I can run it and it works ok, with the sum function in I now
get a general ODBC error '1004' so we aremakeing some progress it seems.

 

[sebastienm]

I ran your code up to the refresh. Of course, since i don't have this dsn i
get a 1004 error with "General ODBC Error" . I don't have access to an
Informix db anymore so i can't help with Informixz specifically.

I am surprised the error doesn't say anything relating to ODBC. Have you
looked into the Application.ODBCErrors to get more details?
Dim err_code As ODBCError
dim appname as string
If Err.Number <> 7 Then '7=Out of memory; when query cancelled
s = "Error(s):" & Strings.Chr$(13)
For Each err_code In Application.ODBCErrors
s = s & Strings.Chr$(149) & err_code.ErrorString & " (" &
err_code.SqlState & ")." & Strings.Chr$(13)
Next
If Err.Number <> 0 Then
s = s & Err.Number & ":" & Err.Description
End If
Err.Clear
MsgBox s, vbCritical, APPNAME
ElseIf Err.Number = 7 Then
MsgBox "Query Cancelled by User (or Out of Memory).", vbCritical,
APPNAME
Err.Clear
Else
Msgbox "unknown error"
End If

What else has changed compared to your prior environment?
informix Driver?
Excel platform? While my QueryTable code used to work in xl97 , i had to
make changes on xl2k 'cause it was not working properly anymore. I had to
delete not only the QueryTables each time, and i had to delete the Named
Range given to the resulting range of the query (automatically given or not).
A few things like that, but cannot remember.

Sebastienm

 

[sebastienm]

hmmm... i am running out of ideas.
Have you tried to get more detail about the error using the ODBCErrors
Dim err_code As ODBCError
as i posted earlier, instead of the regular vba errors?

Regards,
Sebastien