Received: from u1.abs.net (root@u1.abs.net [207.114.0.131]) by hub.org (8.8.8/8.7.5) with ESMTP id IAA02841 for ; Mon, 10 Aug 1998 08:06:24 -0400 (EDT) Received: from insightdist.com (nobody@localhost) by u1.abs.net (8.9.0/8.9.0) with UUCP id IAA00877 for pgsql-sql@hub.org; Mon, 10 Aug 1998 08:00:03 -0400 (EDT) X-Authentication-Warning: u1.abs.net: nobody set sender to insightdist.com!daveh using -f Received: from ceodev by insightdist.com (AIX 3.2/UCB 5.64/4.03) id AA31149; Mon, 10 Aug 1998 07:55:36 -0400 Received: from daveh by ceodev (AIX 4.1/UCB 5.64/4.03) id AA62010; Mon, 10 Aug 1998 07:55:36 -0400 Message-Id: <35CEE0D5.BB8F0F8B@insightdist.com> Date: Mon, 10 Aug 1998 08:00:22 -0400 From: David Hartwig Organization: Insight Distribution Systems X-Mailer: Mozilla 4.05 [en] (Win95; I) Mime-Version: 1.0 To: Taral Cc: pgsql-sql@hub.org Subject: Re: [SQL] MS Access via ODBC References: <000001bdc3c6$299f7960$3b291f0a@taral> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit This is caused by the MS Jet Engine. It is using a record set strategy to optimize navigation through potentially large numbers of rows. It is even more interesting if you use a join condition. The Jet Engine will split the single join into two (or more) queries. In any case PostgreSQL 6.3.2 cannot handle an ORDER BY that is not in the projection/target_list. It will be available in 6.4 In the mean time. I have posted the patch at our web sit (see: http://insightdist.com/psqlodbc_faq.htm Why does MS Access sometimes complain about a GROUP BY or ORDER BY not being in the target list?.) There a two other pit falls. Multi-part keys may exhaust memory in the backend. And large tables will be slow. This is being addressed for 6.4 Taral wrote: > I noticed that when trying to get MS Access to do a query of "SELECT * FROM > table ORDER BY somekey" as its data source for a form, it actually issues > "SELECT primarykey FROM table ORDER BY somekey", which PostgreSQL rejects > because "somekey" isn't being selected. > > Now, either PostgreSQL has put an (odd) restriction on SQL, or MS Access is > issuing an invalid SQL query. Which one is it? > > Taral