If you're like me (a long-time Access developer who doesn't do
ADPs), you've basically ignored SQL92 Mode in Access. If you're
unaware of its existence, what it does is enable a certain level of
compatibility with SQL Server's SQL dialect. Chief among those are:

1. use of % and _ as wildcards (instead of conventional Access * and
?)

2. execution of certain kinds of SQL commands (chiefly generic DDL)
that can't be run directly in Access (i.e., in the QBE as opposed to
via ADO in code).

If you don't do ADPs, you don't necessarily need to write SQL
Server-compatible SQL, so this is not much of an issue for you. And
if you use Access the way I do, you don't often have a need to
execute generic DDL statements (indeed, if you're like me, you
almost never do so at all!).

Anyway, that's the background.

I'm posting this to warn others off trying to use it without
thinking it through very carefully.

I accidentally released to users a version of their app that had
SQL92 mode turned on. I had been testing out some things that
required SQL92 mode, and I generally use my existing projects (since
I understand how they work better than if I used an old app or some
generic app). Normally, I immediately change any settings back to
the original when I'm done with testing, but for some reason, that
didn't happen in this case, and I ended up shipping an update with
SQL92 mode turned on.

Most of it worked.

Indeed, one of the things it does is convert all LIKE comparisons in
stored SQL statements (stored QueryDefs, recordsources and
rowsources) that use traditional Access wildcards to use ALIKE
(which allows you to use the opposite mode's wildcards; e.g., if
you're in SQL 89, you can use ALIKE "this%", or if in SQL 92, you
can use ALIKE "this*").

Unfortunately, it causes many problems that that substitution does
not fix:

1. greatly increased sensitivity to aliases and syntax. At first I
though I had used a reserved word with a field called "Current" but
that's not a reserved word in Access. I was only able to make the
SQL work by surrounding all occurrences of "Current" with square
brackets:

SELECT tluTaxRates.TaxRate, Format([TaxRate],"0.0000%")
& '-'+[TaxRateDescription] AS Rate,
Format([tluTaxRates].[Current],"Yes/No") AS [Current]
FROM tluTaxRates
ORDER BY tluTaxRates.[Current], tluTaxRates.TaxRate;

The original version of this lacked any brackets around "Current"
and works just fine in SQL 89 mode, but with SQL 92 turned on, the
brackets were the only way to make it work because "Current" is a
reserved word in the SQL 92 standard.

Thus, switching to SQL 92 means you have a different set of reserved
words to contend with.

2. it can cause AutoComplete in dropdown lists to stop working. I
had a terrible time with this one -- I had a dropdown list with 3
columns, the first and last not displayed, and AutoComplete wouldn't
work, so typing was completely ineffective in editing the dropdown
list. Now, I had another very similar dropdown on the same form, but
with only two columns (the second hidden). Both used ALIKE for a
comparison, so I puzzled for a long time over what the difference
was. As it turned out, it was that the first column was the bound
column and it wasn't hidden.

So, I'm not certain on this, but it seems that switching to SQL 92
broke combo boxes where the first visible column was not the bound
column.

There were also strange reports of other things that we never
figured out, and that have disappeared once SQL 92 mode was turned
off (so far as I can tell), but I didn't waste time on those trying
to figure out what was going on, so I won't discuss those in detail.

The takeaway:

Don't use SQL 92 mode unless you have a compelling reason to do so
and are prepared to convert all your SQL to be fully compatible with
a non-Access SQL standard.

Keep in mind that ADO uses SQL 92 by default (so I presume you'd
have reserved word problems in ADO, which is another reason to avoid
it, in my opinion -- a generic database interface should inherit the
foibles of each particular database it's connecting to, not
introduced a set of its own, independent of any db engine).

Also keep in mind that DAO is SQL 89 no matter what -- there is no
way to get SQL 92 mode with it, even if your database is set to use
SQL 92 mode.

Since there's very little reason to use ADO in Access MDBs/ACCDBs, I
think there's almost no justification to turn on SQL 92 mode.

You've been warned!

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 5 Nov 2010 21:35:46 GMT, "David-W-Fenton"
<NoEmail (AT) SeeSignature (DOT) invalid> wrote:

A client likes ADO when connected to a SQL Server database. There are
more "things" that you can do easier, or maybe even at all, compared
to DAO. Trouble is I don't recall what they are right now.

They do run a lot of stored procedures which return recordsets to the
bound forms. Again not sure why.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

Tony Toews <ttoews (AT) telusplanet (DOT) net> wrote in
news:fo4ed6h3blf7rbat4u22m0i084m2cvm6ej (AT) 4ax (DOT) com:

Well, that oughtn't be an issue, since you're not connecting to
Jet/ACE. But who knows!

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"David-W-Fenton" <NoEmail (AT) SeeSignature (DOT) invalid> wrote in
news:Xns9E27B2FFFA473f99a49ed1d0c49c5bbb2 (AT) 74 (DOT) 209.136.91:

This statement was in error as written. It actually changes your
LIKE "this*" to ALIKE "this%", not retaining the SQL 89 wildcards,
as is implied by the text above.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Tony Toews wrote:
reas on to prefer DAO when using Access as the front end/

On Sun, 7 Nov 2010 20:32:37 -0500, "Bob Barrows"
<reb01501 (AT) NOSPAMyahoo (DOT) com> wrote:

No, they're not using disconnected recordsets. I think it was that
you could call a stored procedure and get a recordset returned.

Tony

--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

But DAO also fully supports returning recordsets from stored
procedures.

The only DAO limitation I've come across so far is lack of any support
for getting output parameters and the return code of stored
procedures. Output by recordsets is preferable anyway I think (and
DAO can also get the resultsets from sql server table functions, as
well as multi-recordset returns from stored procedures).

Correction: one can also get the output parameters and return code
back in DAO (with the right t-sql text in the pass through query), so
everything is covered.

On Thu, 25 Nov 2010 16:41:55 -0800 (PST), TechVsLife
<techvslife (AT) gmail (DOT) com> wrote:

Ah, I stand corrected. I doubt that was why then.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/