CODEWITHSUNDEEP
sqlms-accessjoin
knittl
knittl

Reputation: 265261

disable parentheses in access sql queries

is there a way to tell ms access (2003) to not put joins into parentheses. or at least to understand them without (every other database does)

i want something like:

    SELECT *
      FROM a
INNER JOIN b
        ON a.a = b.a
INNER JOIN c
        ON b.c = c.c

but access tells me that the query is wrong. IT’S NOT, and it’s driving me crazy …

it also puts all joins in a single line—impossible to read

thanks in advance.

ps. i already activated ANSI 92 compatibility/support in settings

Upvotes: 2

Views: 2399

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

See the Access Help About ANSI SQL query mode (MDB).

This mode conforms closely to the ANSI-92 Level 1 specification, but is not ANSI-92 Level 1 compliant.

For "ANSI-92" read "ANSI/ISO SQL-92" (and for "conforms closely to" read "vaguely resembles" ).

Bottom line: you must include the parentheses. Note the Access database engine's optimizer may change the order of the tables as it sees fit.

Upvotes: 2

JeffO
JeffO

Reputation: 8043

I just tried SQL Server Compantible Syntax (ANSI 92) and Checked This Database.

I tend to setup all my tables and joins in the graphic query builder and then customize in the SQL editor while working around all the parentheses. The usage of square brackets drives me crazy as well. I'm lucky, most of the apps in my firm are being migrated from Access to SQL Server.

Upvotes: 0

VoteyDisciple
VoteyDisciple

Reputation: 37803

Sadly, no. Access 2003 is just that "dumb" about SQL. It is "wrong" in that Access can only parse one JOIN, which leaves you with the infuriating (but also still correct):

SELECT *
FROM a
INNER JOIN (b INNER JOIN c ON b.c = c.c)
    ON a.a = b.a

Upvotes: 2

Related Questions