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
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).