Re: informix ms-access sort problem

Klaus Gotthardt <k.gotthardt@em.uni-frankfurt.de> wrote in article <35F8CBB5.3969@em.uni-frankfurt.de>... > I tried using ms-access(97) as a frontend to informix(7.23,cli 2.5). It > worked, but i can sort only fields which are primary keys. > > (error: ....order by column (...) must be in select list (# -309)) > > <snip> This happens even when you include the column to be sorted by in the query because Access is running the query as a "dyanset". With a dynaset, Access retreives only a unique key for each table (the 1st unique index it finds) in the main select, then uses the primary key for each table to fetch the other columns as each row is called for. There are a couple of ways around this problem. One way is to force the query to execute as a "snapshot" rather than a dynaset. I don't remember excatcly how to do this, but if you dig through the help files you may be able to find out. The other way to deal with this is to use an ODBC driver which intelligently handles the types of queries that Access uses for dynasets and adds the ordered by columns to the select list of the query. The OpenLink multi-tier drivers do this. Others may as well. HTH Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com