Ensure SQL-92 on Access 2000 mdb to SQL Server und

Question

Due to circumstances not fully under my control, I have to develop ACCESS queries which will be stored in an Access 2000 MDB but which may be developed under any one of: Access 2000, 2002, 2003, 2007, 2010. These queries will access (via linked tables) either an MS SQL Server DB or an Access 2000 .MDB file.

In the past, we had issues which we tracked down to queries not being ANSI-92 compatible. This we solved by marking the Check Box in the Tables/Queries tab of the Tools|Options dialog (under older versions of Access than 2007).

We move these Access application .MDBs between various systems and we now generally don't have any problems.

However, I've just developed a query which works fine with all inner joins, but when I change one of the joins to an outer join, the query fails with an "Invalid Operation" error under Access 2010. So I thought I'd check if the ANSI-92 checkbox was still set. I couldn't find it.

Now, I KNOW that Access 2003 and later uses ANSI 92 syntax automatically but I wondered if there's any way to check that this is the case for these queries in this (rather specific circumstances). I observed that when we changed the checkbox, all my "Like" queries became "ALike" queries...

So the question boils down to is ANSI 92 syntax totally transparent in this circumstances and the reason my query is failing is something else...

TIA, Paolo

Answer 1

In Access 2010, the setting for SQL Server Syntax compatibility is under:

File > Options > Object Designers > Query Design.

You'll first need to check if that option is set or not to confirm whether the behaviour of your query is normal.

As for LIKE vs ALIKE, the latter will work in the ANSI-way, using % and _ pattern matching.
In SQL-89 compatibility mode (the MS Access SQL subset), LIKE only works with * and ? patterns but if you change the compatibility to SQL-92, you need to rewrite your queries with % and _ instead.

That's why ALIKE was introduced, it allows you to write ANSI compliant patterns regardless of the compatibility level you choose (so in SQL-92 compatibility mode, both are behaving the same).

Answer 2

There is really no reason to set ANSI 92 SQL mode ON in an Access front end, except if you're a lazy SQL Server programmer who wants to forget that he's writing the SQL in Access instead of SQL Server.

The reason you don't want to do this is because it changes things in the Access application, while not actually doing anything to enhance interaction with your SQL Server back end. That is, if you use SQL 89 wildcards in your SQL in your Access front end, your ODBC driver takes care of converting to the SQL 92 wildcards when it sends the SQL to the server. The only exception would be passthrough queries, but those are written in the native SQL of your server, anyway.