Re: Help with MS Access query with Informix tables

Heather Chipka wrote: > > 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. > > Thanks for any help, > > Heather First of all I want to thank Jeff Davis of High Peak Systems and Tim Porter of Informix for trying to help me with this. You may be wondering why I am answering my own question. Well, I eventually had to call MS on this and I thought you all might be interested in their response. Essentially what I learned was this: How many Microsoft employees does it take to change a light bulb? None. They change the standard to darkness. Which my way of saying they told me they know about it, and it is working as designed and too bad too sad. What I learned is this: Lets say you have attached to an application's Informix tables. Then you open a table and try to do an order by on a column other than the primary key column. This will fail with an ODBC error, then it will get an Informix error that says 'cannot order by a column that is not in the Select' . What the Access Jet engine is doing is first sending the query using the primary key column only, and still leaving the order by request in the SQL This will fail. According to Microsoft this is not a bug, it is working as designed. (Just because it doesn't work with any relational databases that don't allow you to order by a column not in the select list doesn't seem to concern them). Anyway, there are 2 workaround for this. If using Microsoft Access version 2.0, you can add the following line to the [ODBC] section of the MSACC20.INI file (in the Windows directory): SnapshotOnly=1 Then unattach and reattach the tables. If using MS Access for Windows 95 V 7.0 there are other weird things you have to do concerning the registry. The info for that is at www.microsoft.com/kb/deskapps/access/q10097.htm. The second work around, bypasses the jet engine. You create amd run a pass through query in Access. The info on how to do that is in the Help option. Anyway, just wanted to post this in case anyone else uses Access with Informix and gets the same error. Heather