Postgres Pro
Downloads
Home   >   mailing lists

Thread: MS Access / Postgres ODBC / Outer joins

MS Access / Postgres ODBC / Outer joins

From
Glen Parker
Date:
We're having a problem with Access, Postgres, and outer joins.  I'm
hoping this will ring a bell with someone and there'll be an easy answer.

Everything seems to work OK with inner joins and everything else we've
tried, but when switching to an outer join, Access screws the SQL all
up.  I'm almost positive at this point that Access is the culprit, as
apposed to the ODBC driver.

Given this SQL statement typed into the Access query builder...

SELECT t1.field1, t2.field2 FROM t1 LEFT OUTER JOIN t2 on t1.field1 =
t2.field2 WHERE t1.field3 = 'some value';

Access will rewrite it to this...

SELECT t1.field1, t2.field2 FROM {oj t1 LEFT OUTER JOIN t2 on t1.field1
= t2.field2 WHERE t1.field3 = 'some value' };

Note the "{oj" after "FROM", and the closing "}" at the end of the
query.  What the heck is that all about?  Has anybody seen this before?
  Is there an SQL server that might actually recognize that?

(I didn't think version information would be too important for this, but
I can gather all that info if it's really needed...)

TIA

Glen Parker
glenebob@nwlink.com

Re: MS Access / Postgres ODBC / Outer joins

From
"Dann Corbit"
Date:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Glen Parker
> Sent: Thursday, October 06, 2005 2:41 PM
> To: Postgres General
> Subject: [GENERAL] MS Access / Postgres ODBC / Outer joins
>
> We're having a problem with Access, Postgres, and outer joins.  I'm
> hoping this will ring a bell with someone and there'll be an easy
answer.
>
> Everything seems to work OK with inner joins and everything else we've
> tried, but when switching to an outer join, Access screws the SQL all
> up.  I'm almost positive at this point that Access is the culprit, as
> apposed to the ODBC driver.
>
> Given this SQL statement typed into the Access query builder...
>
> SELECT t1.field1, t2.field2 FROM t1 LEFT OUTER JOIN t2 on t1.field1 =
> t2.field2 WHERE t1.field3 = 'some value';
>
> Access will rewrite it to this...
>
> SELECT t1.field1, t2.field2 FROM {oj t1 LEFT OUTER JOIN t2 on
t1.field1
> = t2.field2 WHERE t1.field3 = 'some value' };
>
> Note the "{oj" after "FROM", and the closing "}" at the end of the
> query.  What the heck is that all about?  Has anybody seen this
before?
>   Is there an SQL server that might actually recognize that?

That's just ODBC syntax.  The semicolon is not part of the statement,
but is some terminator for the tool that reads SQL commands, I guess.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ht
m/odbcouter_join_escape_sequence.asp

> (I didn't think version information would be too important for this,
but
> I can gather all that info if it's really needed...)

Some more information about what is actually going wrong with the
queries and what tool set you are using would probably be good to
explain so that you can get the best possible help.

Re: MS Access / Postgres ODBC / Outer joins

From
Tom Lane
Date:
Glen Parker <glenebob@nwlink.com> writes:
> Access will rewrite it to this...

> SELECT t1.field1, t2.field2 FROM {oj t1 LEFT OUTER JOIN t2 on t1.field1
> = t2.field2 WHERE t1.field3 = 'some value' };

> Note the "{oj" after "FROM", and the closing "}" at the end of the
> query.  What the heck is that all about?  Has anybody seen this before?

Yeah, I think this is actually in the ODBC standard: the ODBC driver is
supposed to recognize that and convert it to whatever outer join syntax
the database likes.  Not sure if our ODBC driver does so --- you'd be
better off asking about this on pgsql-odbc.

            regards, tom lane

Re: MS Access / Postgres ODBC / Outer joins

From
"Relyea, Mike"
Date:
As an alternate solution, what about using a pass through query?  Access
won't mess with the SQL that way.  I don't know if that will suit your
needs, but I tend to use them almost exclusively in my Access/PostgreSQL
project.



-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Glen Parker
Sent: Thursday, October 06, 2005 5:41 PM
To: Postgres General
Subject: [GENERAL] MS Access / Postgres ODBC / Outer joins

We're having a problem with Access, Postgres, and outer joins.  I'm
hoping this will ring a bell with someone and there'll be an easy
answer.

Everything seems to work OK with inner joins and everything else we've
tried, but when switching to an outer join, Access screws the SQL all
up.  I'm almost positive at this point that Access is the culprit, as
apposed to the ODBC driver.

Given this SQL statement typed into the Access query builder...

SELECT t1.field1, t2.field2 FROM t1 LEFT OUTER JOIN t2 on t1.field1 =
t2.field2 WHERE t1.field3 = 'some value';

Access will rewrite it to this...

SELECT t1.field1, t2.field2 FROM {oj t1 LEFT OUTER JOIN t2 on t1.field1
= t2.field2 WHERE t1.field3 = 'some value' };

Note the "{oj" after "FROM", and the closing "}" at the end of the
query.  What the heck is that all about?  Has anybody seen this before?
  Is there an SQL server that might actually recognize that?

(I didn't think version information would be too important for this, but

I can gather all that info if it's really needed...)

TIA

Glen Parker
glenebob@nwlink.com

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings