Error on refresh backgroundquery - help please

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

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" wrote:

> 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

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

"sebastienm" wrote:

> 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" wrote:
>
> > 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

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.

"RichardLOZ" wrote:

> 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
>
> "sebastienm" wrote:
>
> > 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" wrote:
> >
> > > 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

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

Just saw your latest post.
One thing comes to my mind. The sql statement used to be either a single
string of less than 255 characters or an array of string of less than 255
characters.
Eg:
mysql = "select ... 255character_right_here FROM..."
so
mysqlArray = Array("select ... 255chara", "cter_right_here FROM ..."{)

Could it be the issue?

Sebastien

"sebastienm" wrote:

> 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

No I dont believe that it is, as I am able to run the query ok with a
selected number of fields say 2 but as soon as I add in the sum function I
get the ODBC error. Its starting to drive me crazy now.

"sebastienm" wrote:

> Just saw your latest post.
> One thing comes to my mind. The sql statement used to be either a single
> string of less than 255 characters or an array of string of less than 255
> characters.
> Eg:
> mysql = "select ... 255character_right_here FROM..."
> so
> mysqlArray = Array("select ... 255chara", "cter_right_here FROM ..."{)
>
> Could it be the issue?
>
> Sebastien
>
> "sebastienm" wrote:
>
> > 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

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

"RichardLOZ" wrote:

> No I dont believe that it is, as I am able to run the query ok with a
> selected number of fields say 2 but as soon as I add in the sum function I
> get the ODBC error. Its starting to drive me crazy now.
>
> "sebastienm" wrote:
>
> > Just saw your latest post.
> > One thing comes to my mind. The sql statement used to be either a single
> > string of less than 255 characters or an array of string of less than 255
> > characters.
> > Eg:
> > mysql = "select ... 255character_right_here FROM..."
> > so
> > mysqlArray = Array("select ... 255chara", "cter_right_here FROM ..."{)
> >
> > Could it be the issue?
> >
> > Sebastien
> >
> > "sebastienm" wrote:
> >
> > > 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