KB 824189 - The AutoExpand Property May Not Work When ANSI-92 Syntax....

[John Tempest]

After nearly two years of trying to find the solution to why I was getting
an error message "The text you entered isn't an item in the list." when the
lookup list in a combo box definitely did have the item in the list, I
finally found this Knowledge Base article 824189 which looks as if it first
appeared on the 28th August 2003.

The problem appears to be that if SQL Server Compatible Syntax (ANSI-92) is
checked in the Options//Tables/Queries dialog for the database, then Access
2002/2003 does not generate the appropriate SQL syntax for the RowSource
property. You manually have to enter the word DISTINCT after the SELECT
word. The problem did not arise in Access 2000 or earlier versions.

I have two questions:
1. Does anyone know if Microsoft are working on a fix for the problem that
does not require this manual intervention?
2. What are the disadvantages of not using the SQL Server Compatible Syntax?
There are some advantages of using SQL that I am aware of: the statements
are easily modifiable; and additional query "tables" do not have to be set
up on top of the original tables for additional selection or sorting
criteria. Also, possibly in more complex situations SQL can be used in
macros or modules as event procedures.

NB. The heading of the article uses the word "may", indicating that this
problem possibly does not always arise in the same circumstances. Are there
any clues as to why this may be so?

Perhaps one of the Professionals can enlighten us?

John Tempest

 

[Larry Linson]

John,

If you regularly use Office Update, or other means, to make certain your
copy of Office is up-to-date, then apparently the issue has not been
resolved. Microsoft does not share their patch/Service Pack schedule with
MVPs (who aren't, as you probably know, Microsoft employees), nor their work
priority lists.

From your question, I assume that you think that not checking "SQL Server
Compatible Syntax" means you cannot use SQL -- that is certainly not the
case. Access, however, has some slight differences from ANSI SQL in its
"flavor" of SQL, so you can't use the identical SQL that you would use in
Microsoft SQL Server. For example, the "wildcard" for Access SQL is "*", but
for SQL Server is "%".

As far as I know, I have never checked "SQL Server Compatible SQL" in any
Access database since 1993, and most of my paying work has been on
client-server applications, some of which have used MS SQL Server. And,
every one of those applications of any significance built SQL statements in
code. I'm not sure how you'd build it in macros. That use, also, need not be
in "event procedures" but could be in standard modules or class modules, as
well.

Once you know that it happens, and know how to fix it at design time, it's
not really an urgent problem, IMNSHO, as would be a security breach with no
workaround. Security issueses are the cases in which everybody gets excited
about finding, completing, testing, and distributing the fix!

Larry Linson
Microsoft Access MVP