Re: Informix/Access Sorting Bug?

Curt Van Den Heuvel <heuvelc@primenet.com> wrote in article <6aidih$quc@nntp02.primenet.com>... > I have an Access-97 database which contains several linked informix 7.23 > tables (using Informx CLI-32 to connect). If I try to order by an column > other than the primary key, I get the following error from the ODBC driver > : "COLUMN whatever MUST BE IN ORDER BY LIST". I put a trace on the ODBC > driver, and indeed it was trying to order by the specified column without > having it in the SELECT list. > > Question: Does the SQL command originate with Access, Informix-CLI or the > ODBC driver? > <snip> I belive the problem lies with Access. The OpenLink ODBC drivers try to intelligently handle this by adding the needed column(s) to the select list. Sometimes it works, sometime it does not. (The latest version from OpenLink may do this even more reliably.) Sometimes, even if you include the ordered by column as displayed in your query, Access may still return this error. This is because Access will usually try to fetch just the primary key of each table in the query, and then ftech the other columns only as they are needed for display. A way around this is to change the query's recordset type to "snapshot" rather than "dynaset". This will cause Access to send the whole query as a single SQL statement much like you would expect. This is fine if your query is primarily to be used for a report or other "read-only" purposes. Data can not be updated through a "snapshot".