Thread: MS Access via ODBC

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

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