Has any one else had problems Access '97 changing the left join syntax,eg:
SELECT jobs_pg.id, edition_pg.edid
FROM jobs_pg LEFT JOIN edition_pg ON jobs_pg.id = edition_pg.id;
to:
SELECT "jobs","id","edition","edid" FROM {oj "jobs LEFT OUTER JOIN "edition"
ON ("jobs"."id"="edition"."id") }
Where id and edid are the primary keys.
Is there an easy way around this that I've missed?
Having a quick look at the ODBC 3.0 SDK it seems this is an ODBC thing. It
appears that ODBC use an escape sequence for outer joins. Here's the BNF
(according to the ODBC SDK 3.0):
ODBC-outer-join-escape ::=
ODBC-esc-initiator oj outer-join ODBC-esc-terminator
outer-join ::= table-name [correlation-name] {LEFT | RIGHT | FULL}
OUTER JOIN{table-name [correlation-name] | outer-join} ON search-
condition
correlation-name ::= user-defined-name
ODBC-esc-initiator ::= {
ODBC-esc-terminator ::= }
It also mentions that it looks to SQLGetInfo with SQL_OJ_CAPABILITIES. But I
couldn't find any thing usefull (bar totally turning them off :( )
Theres a couple of alternatives that I can see, views or ODBC Pass Through.
If any one has a better solution (I am being blind and missing an option or
whatever) please share.
(Incase you're wondering why I'm not using Pass through queries and views
from the start, its because I've just converted an Access '97 file based db,
and I'm guessing if I've come up against this problem, others might).
- Stuart
