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