Re: Help with MS Access query with Informix tables

Heather Chipka <heather.chipka@bridge.bellsouth.com> wrote in article <326D07BF.3495@bridge.bellsouth.com>... > I have attached from MS Access to Informix tables. I am able, in > datasheet view, to see all data from all the columns. When I check what > sql has been sent for this it has all the columns selected. However, when > I then choose a specific column to order by (other than primary key), > Access changes the sql and only asks for the primary key in the select > statement while trying to do an order by with the requested column. This > is an invalid sql statement and fails. > > Anyone ever seen this before or have any suggestions? BTW if my table > doesnt have a primary key I can order by any column because Access doesnt > change the select portion of the sql. > The explanation behind this one can get quite complex, but briefly (or as briefly as I can put it): Normally, Access will treat a query as a "dynaset". When builing the SQL for a dynaset, Access selects only the unique keys (the first unique index it finds, NOT necessarily the "primary key") of each table in the query, and then fetches the remaining columns as needed using the primary keys. This can save tremendously on network traffic and can make browsing a large result set very fast. The downfall is that you can not order by a column which is not part of the first unique index of one of the tables in the query. If Access does not find a unique index it will treat the query as a "snapshot". In this case, the full SQL you might expect is sent to the server and all columns are selected at once. You can also force a "snapshot" for a particular query with a property setting somewhere (I forget excatly where). The upside to the snapshot is that you can now order by any selected column. The downside is slower browsing of the result set and higher network traffic, even if you end up not looking at all of the rows. If there is a way around the sort problem other than using a "snapshot", I don't know it. Then again, I'm not an Access "expert". The foregoing is based upon my experiences with Access 2.0 a couple of years ago and the MS White Paper on the "Jet Engine" from around that time. HTH, Irwin Goldstein Objective Software Systems, Inc.