COMPGROUPS.NET | Browse | Post | Groups | Users | Stream | About | |

### Heads Up on SQL92 Mode

• Email
• Follow

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

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/

 0

See related articles to this posting

On 5 Nov 2010 21:35:46 GMT, "David-W-Fenton"
<NoEmail@SeeSignature.invalid> wrote:

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

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/

 0

Tony Toews <ttoews@telusplanet.net> wrote in
news:fo4ed6h3blf7rbat4u22m0i084m2cvm6ej@4ax.com:

> On 5 Nov 2010 21:35:46 GMT, "David-W-Fenton"
><NoEmail@SeeSignature.invalid> wrote:
>
>>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.
>
> 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.

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/

 0

"David-W-Fenton" <NoEmail@SeeSignature.invalid> wrote in
news:Xns9E27B2FFFA473f99a49ed1d0c49c5bbb2@74.209.136.91:

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

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/

 0

Tony Toews wrote:
> On 5 Nov 2010 21:35:46 GMT, "David-W-Fenton"
> <NoEmail@SeeSignature.invalid> wrote:
>
>> 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.
>
> 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.
>
Disconnected recordsets are the biggest benefit. Other than that, I see no
reas on to prefer DAO when using Access as the front end/


 0

On Sun, 7 Nov 2010 20:32:37 -0500, "Bob Barrows"
<reb01501@NOSPAMyahoo.com> wrote:

>>> 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.
>>
>> 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.
>>
>Disconnected recordsets are the biggest benefit. Other than that, I see no
>reas on to prefer DAO when using Access as the front end/

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/

 0

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

But DAO also fully supports returning recordsets from stored
procedures.

 0

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

 0

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.


 0

On Thu, 25 Nov 2010 16:41:55 -0800 (PST), TechVsLife
<techvslife@gmail.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. �
>
>But DAO also fully supports returning recordsets from stored
>procedures.

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/

 0

Tony Toews <ttoews@telusplanet.net> wrote in
news:shl0f6hq3h5ei99efmclbpqd7oi6acdjvb@4ax.com:

> On Thu, 25 Nov 2010 16:41:55 -0800 (PST), TechVsLife
><techvslife@gmail.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. �
>>
>>But DAO also fully supports returning recordsets from stored
>>procedures.
>
> Ah, I stand corrected.  I doubt that was why then.

Is it perhaps that with ADO, you can get an editable recordset? And
bind it to a form?

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

 0

On 27 Nov 2010 03:47:52 GMT, "David-W-Fenton" <dfassoc@dfenton.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. �
>>>
>>>But DAO also fully supports returning recordsets from stored
>>>procedures.
>>
>> Ah, I stand corrected.  I doubt that was why then.
>
>Is it perhaps that with ADO, you can get an editable recordset? And
>bind it to a form?

Yes, he is using ADO in that fashion.  Which really surprised me when
I was viewing the code.

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/

 0

> >Is it perhaps that with ADO, you can get an editable recordset? And
> >bind it to a form?
> Yes, he is using ADO in that fashion.

Not sure what the advantage of ADO over DAO is, which can also bind
editable recordsets.  Is is that ADO can get editable recordsets from
certain types of stored procedures and table functions, and DAO
cannot, or something else.  (since DAO can bind editable sql server
tables via linking, and local queries on them, and call stored
procedures or pass through sql for write & other operations)  Too bad
DAO won't get further enhancements to support sql server; but ADO is
also dead.  (The windows development world is in a more confused
terrain than usual after winforms (wpf? silverlight? asp? javscript/
html5?).)

 0

TechVsLife <techvslife@gmail.com> wrote in
:

>> >Is it perhaps that with ADO, you can get an editable recordset?
>> >And bind it to a form?
>> Yes, he is using ADO in that fashion.
>
> Not sure what the advantage of ADO over DAO is, which can also
> bind editable recordsets.

But not from a stored procedure, right?

> Is is that ADO can get editable recordsets from
> certain types of stored procedures and table functions, and DAO
> cannot, or something else.  (since DAO can bind editable sql
> server tables via linking, and local queries on them, and call
> stored procedures or pass through sql for write & other
> operations)  Too bad DAO won't get further enhancements to support
> sql server;

DAO never supported SQL Server directly. It always did it through
Jet/ACE via ODBC. But the new native ODBC driver for SQL Server is
quite fast in comparison to the old (in my experience) and so it's
clear that MS is investing effort in ODBC, which is actually quite
an old technology.

> also dead.  (The windows development world is in a more confused
> terrain than usual after winforms (wpf? silverlight? asp?
> javscript/ html5?).)

I wouldn't know. I'm confused enough on my own.

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

 0

wow dude.. it sounds to me like you're stuck in the past.. I mean.. 92.. I mean wow.. that's like 18 years ago!!

I'm just surprised that you're still using an obsolete database engine that hasn't received any improvements in the past 15 years!

 0
Reply aaron_kempf (24) 1/3/2011 12:18:00 PM

On Monday, January 3, 2011 5:18:00 AM UTC-7, a a r o n _ k e m p f wrote:
> wow dude.. it sounds to me like you're stuck in the past.. I mean.. 92.. =
I mean wow.. that's like 18 years ago!!
>=20
> I'm just surprised that you're still using an obsolete database engine th=
at hasn't received any improvements in the past 15 years!

A a r o n,
That's what I would have thought too: "SQL 92... WAT???" However, it is now=
15 Sep 2013, 2.5 years later. Guess why I'm reading this post at all? I wa=
s browsing through the speaker list at this year's High Performance Computi=
ng financial Show and Conference. You'd think that they would be the most u=
p-to-date, unlimited budget developers, right? Supercomputing AND financial=
markets, including uber-lucrative high frequency trading.

So I was looking at the biographies of the speakers to see what they were w=
orking on. I noticed this one,=20
http://www.flaggmgmt.com/hpc/speakers.html#Birnbaum=20
developing a new robust and scalable messaging system for HPC users in fina=
ncial markets: =20
"...60East Technologies, LLC, the company that is developing =91AMPS' (Adva=
nced Message Processing System). AMPS is a modern, high performance messagi=
ng platform designed for the most demanding environments where latency, thr=
oughput and scale are critical. The platform offers topic and content based=
pub/sub with integrated SQL-92 database and real-time aggregation capabili=
ties."

Yes, that's right. The modern AMPS platform's integrated SQL-92 database is=
a feature!=20

Dave fenton dude has company, if he is stuck in the past. I have no idea wh=
y AMPS uses SQL 92. Maybe it's an ODBC thing? Microsot says =20
"The ODBC Desktop Database Drivers and the underlying Microsoft Jet engine =
are not SQL-92 compliant"=20
and =20
"SQL-92 features not supported by Microsoft Jet SQL: Security statements, s=
uch as GRANT and LOCK" =20
although the SQL used by Desktop Database Drivers seems better overall than=
SQL-92 See SQL-92 Compliance=20
http://msdn.microsoft.com/en-us/library/ms711756(v=3Dvs.85).aspx

P.S. Sorry phor typos, my eph key is broken.

 0
Reply myindigolife (3) 9/15/2013 9:01:55 AM

On Sunday, November 7, 2010 6:32:37 PM UTC-7, Bob Barrows wrote:
> Tony Toews wrote:
> > On 5 Nov 2010 21:35:46 GMT, "David-W-Fenton"

I am so sorry. I didn't realize that David W. Fenton had passed away, earlier this year, per https://groups.google.com/d/topic/comp.databases.ms-access/OPJKKwxdsUs/discussion

I was merely replying to someone who posted in 2011, saying that Mr. Fenton's use o SQL 92 was antiquated. I was trying to explain why that wasn't true, not at all. I meant no disrespect.

May he rest in peace. His work and wisdom lives on here, guiding and helping us.

 0
Reply myindigolife (3) 9/15/2013 9:40:20 AM

FYI, aaron kempf has never been qualified to scrape the mud off David
Fenton's boots, much less to question which SQL standard David's clients
used.

--
Larry Linson

"Ellie K" <myindigolife@gmail.com> wrote in message
> On Sunday, November 7, 2010 6:32:37 PM UTC-7, Bob Barrows wrote:
>> Tony Toews wrote:
>> > On 5 Nov 2010 21:35:46 GMT, "David-W-Fenton"
>
> I am so sorry. I didn't realize that David W. Fenton had passed away,
> earlier this year, per
>
> I was merely replying to someone who posted in 2011, saying that Mr.
> Fenton's use o SQL 92 was antiquated. I was trying to explain why that
> wasn't true, not at all. I meant no disrespect.
>
> May he rest in peace. His work and wisdom lives on here, guiding and
> helping us.


 0
Reply accdevel (436) 9/15/2013 5:16:15 PM

On Sun, 15 Sep 2013 12:16:15 -0500, "Access Developer"
<accdevel@gmail.com> wrote:

>FYI, aaron kempf has never been qualified to scrape the mud off David
>Fenton's boots, much less to question which SQL standard David's clients
>used.

Hehehe.  Well said.

Tony

 0
Reply ttoews (2789) 9/15/2013 8:47:33 PM

18 Replies
492 Views

Similar Articles

[PageSpeed] 51

• Email
• Follow

Similar Artilces:

I have two 1600x1200 monitors hooked up to one card (one VGA, the other DVI). In X it works fine using Xinerama. Outside of X, the two monitors are duplicates, which is useful at times (e.g. when the cat stands in front of one), but a separate screen would be more useful. Is there any way to make it act as a second screen, maybe switching between them with a hotkey? -- -eben QebWenE01R@vTerYizUonI.nOetP http://royalty.mine.nu:81 AQUARIUS: There's travel in your future when your tongue freezes to the back of a speeding bus. Fill the void in your pathetic life by playing Wha...

Appearence of headings in emacs LaTeX mode
When I have: \section{The problem and solution strategy} in my tex document the heading "The prob..." is formatted in the emacs buffer. I don't want that. How do I get rid of it? Rather I like to use preview mode. In my mode/status line the mode is LaTeX/M. What does M mean? -- Associate Prof. Ph.D Torben Knudsen Phone : (+45) 9635 8670 Section of Automation and Control, Email : tk@es.aau.dk Department of Electronic Systems, Aalborg University Fredrik Bajersvej 7 DK-9220 Aalborg � Denmark Torben Knudsen <tk@es.aau.dk> writes: > When I have: > \section{The...

Math mode boldness in section/chapter headings
I have a problem that I'm writing a document where a lot of section and chapter titles have to include math mode symbols, notably \to arrows and Greek letters. However, they naturally display unboldened in the title, which looks odd. Using \bm{} to wrap the math mode commands then makes it look okay, but with the undesirable side-effect of making the math mode bits stick out as bold bits in the otherwise unboldened repeats of the section name in the table of contents and the page header (using report and book document styles). Any suggestions on how to solve this problem? I'm surpris...

Fedora Core 1
Hi all, I've ugraded my Redhat 9 to Fedora and I noticed a strange problem. When I am starting my X-server in dual-head mode with Gnome, I don't see any panel (task-bar) on my screen. As you guess it's not easy to work without task-bar, so I have to use kde which is more "heavy" on my laptop. I tried to add a new user and start it on fresh configuration but without sucess (still without task-bar). Any suggestions? -- Maciej Zobniow Open Source - Free software for free people. http://markcb.zobniow.net http://mysql.zobniow.net Maciek Zobniow...

XVR-600 vs. XVR-1200 in single-head -OpenGL- mode on an SB2K?
Hi everyone, I'm pondering if I should get an XVR-600 or XVR-1200 to supplement the XVR-1000 in my SB2k. The XVR-600 and XVR-1200 are both WildCat-IV-based. Only differences appear to be the ammount of RAM (Texture, FrameBuffer, Display Lists, etc..) and the fact that the XVR-1200 supports the ARB MultiTexture OpenGL extension while the XVR-600 does not. Also, the XVR-1200 is "supported" in the SB2K while the XVR-600 is not (Most Likely because it's not supported as the primary console on the sb2k). The XVR-1000 is quite a different beast and is MAJC-5200-based (a SUN chi...

Samba mode (SMB mode), SRS icarus mode, whatif mode
These 3 modes are back online: Samba mode (SMB mode)</A>, SRS icarus mode, whatif mode http://jrweare.googlepages.com/ReleasedOpenSourceSoftware.html enjoy Johnathon ...

mmm-mode, python-mode and doctest-mode?
Is it possible to get doctest-mode to work with mmm-mode and python-mode nicely so that docstrings containing doctests are editable in doctest-mode? In my utter e-lisp ignorance, I tried this: (require 'mmm-auto) (setq mmm-global-mode 'maybe) (mmm-add-classes '( (doctest :submode doctest-mode :front "\"\"\"" :back "\"\"\""))) (mmm-add-mode-ext-class nil "\\.py\$" 'doctest) That has the following problems: - Fails to set the background colour of the doctest-mode regions to the default mmm-mo...

mmm-mode, ruby-mode and html-helper-mode
Hi, I would like to use Emacs to edit my Rails [http://www.rubyonrails.org] project, but I can't get mmm-mode, ruby-mode and html-helper-mode to work nicely together. Here's the relevant part of my .emacs: ;; eRuby (setq mmm-global-mode 'maybe) (setq mmm-submode-decoration-level 2) (mmm-add-classes '((eruby :submode ruby-mode :front "<%" :back "%>"))) (add-hook 'html-helper-mode-hook '(lambda () (turn-on-font-lock) (setq mmm-classes '(eruby)) (set-face-background 'mmm-default-submode-face "gray28...

re: mmm-mode, python-mode and doctest-mode?
[John J Lee] > Is it possible to get doctest-mode to work with mmm-mode and python-mode > nicely so that docstrings containing doctests are editable in > doctest-mode? I recently released a new version of doctest-mode [1], and I came across your email [2] (it was on the first page of google hits for "doctest-mode"). So I decided to have a go at getting doctest-mode to play nicely with mmm-mode. The result is available here: https://python-mode.svn.sf.net/svnroot/python-mode/trunk/python-mode/doctest-mode.el I still consider it alpha code (the version that's ava...