MS-Access: can't sort linked INFORMIX table?

Hi, Maybe somebody have news about this problem? When I done below (WorkArounds=1073741824) then pass-through SQL (using select with ORDER BY) in Access did not work! WorkArounds=1073741824. Microsoft Access assumes that ORDER BY columns do not have to be in the SELECT list. This workaround addresses that mistaken assumption for data sources such as INFORMIX and OpenIngres. Thanks, Rein > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org]On Behalf Of > mfalcian9913@my-dejanews.com > Sent: 24. m'rts 1999. A. 13:57 > To: informix-list@iiug.org > Subject: Re: MS-Access: can't sort linked INFORMIX table? > > > Any one from Microsoft want to chime in here?........ > > > In article <7d7r4q$pr$1@newsource.ihug.co.nz>, > "Stephen Denne" <spdenne@toyota.co.nz> wrote: > > Two other work-arounds that I've discovered are: > > > > 4) Ask access to only retrieve the top 100% of records > > > > 5) Upgrade your ODBC driver to v3.11 to enable you to use > the following > > workaround ... from > > > http://www.intersolv.com/datadirect/download/docs/odbc/Readme/ > 95ntread.htm > > > > WorkArounds=1073741824. Microsoft Access assumes that > ORDER BY columns > > do not have to be in the SELECT list. This workaround > addresses that > > mistaken assumption for data sources such as INFORMIX and > OpenIngres. > > > > Stephen. > > > > Pradeep Kutty <pkutty@informix.com> wrote in message > > news:36F65664.25DD83BE@informix.com... > > > Hi, > > > This is a Microsoft issue and they have to clear it > out. The Informix > > bug is > > > 86030. The reason you get this error is because on a > query in which the > > user would > > > select lname from customer order by lname, Access changes> to select > > customer_num > > > from customer order by lname. The ODBC specification has > a function named > > > SQLGetInfo() that allows the program to gather > information about the > > database that > > > it is connected to. One of the pieces of information that > can be gathered > > is > > > whether or not the database server requires the column in > the order by to > > be in > > > the select list. > > > The arguement to SQLGetInfo for this information is > SQLL_ORDER_BY_COLUMNS_ > > > IN_SELECT. Informix has confirmed that the correct value > is returned in > > this call. > > > Therefore, the select statement that is being issued > should be expected to > > come > > > back with a 309 error and this is expected behaviour as > far as Informix > > goes. > > > > > > Possible workarounds: > > > 1. Write pass-through SQL in Access, i.e. hand code the statement. > > > This works, but the end user loses almost all the > benefits of using Access > > > as a front end. > > > > > > 2. One of the messages cited below suggested using a > win.ini parameter to > > set > > > SnapshotOnly to true. The modern equivalent is to edit > the registry: > > > HKEY: HKEY_LOCAL_MACHINE\\Software\\Microsoft\\Jet\\3.5\\Engines\\ODBC > > > KEY: SnapshotOnly > > > KEY_TYPE REG_DWORD > > > VALUE: 1 > > > > > > However this DID NOT WORK. > > > > > > 3. Add the offending column to the select list. > > > At first this seems to work, in that the query runs > without getting the > > error and > > > produces the correctly rows. However this workaround > exposes another bug > > in > > > Access. If some other column is added to the query, and > included in the > > Access > > > sort by row, the result is NOT sorted by that column. An > example of the > > workaround > > > . again expressed in SQL, is: select b.b2, b.f from a, b > where a.f = b.f > > > > > > Hope this points you in the Bill Gates direction ...... !! > > > > > > PK > > > > > > > have an MS-Access front end using an ODBC link to an > Informix backend > > and > > > > here is my problem: > > > > > > > > Attempting to sort an updateable linked table using MS access to > > Informix back > > > > end results in an ODBC error "[Informix][Odbc Informix > Driver][Informix] > > ORDER > > > > BY column (dept) must be SELECT list (#-309) or "[Informix][Odbc > > Informix > > > > Driver][Informix]A syntax error has occurred.". > > > > > > > > If the table is linked in non-updateable mode i.e. > without a unique > > field, > > > > then you are able to sort. Filters work in either case. > > > > > > > > Has anyone seen this before? A can't believe this doesn't work! > > > > > > > > Here is are the details of my config: > > > > Backend: > > > > HP-UX helpful B.10.20 A 9000/877 > > > > INFORMIX-OnLine Version 7.20.UC2 > > > > > > > > ODBC: > > > > INFORMIX 2.80 32 BIT 2.80.0004 > > > > Configuration: > > > > Data Source Name : Year 2000 > > > > Database: y2k > > > > Server: Helpful > > > > Host: helpful > > > > service: sqlturbo > > > > Protocol: onsoctcp > > > > > > > > MS Access: > > > > Microsoft Access 97 SR-1 > > > > > > > > -----------== Posted via Deja News, The Discussion > Network ==---------- > > > > http://www.dejanews.com/ Search, Read, Discuss, > or Start Your Own > > > > > > > > > -----------== Posted via Deja News, The Discussion Network > ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or > Start Your Own >