ANSI-92 SQL Vs the Access default of ANSI-89

[Ben]

Hi all,

I would like to know if I selected the ANSI-92 SQL in:
Tools->Optinos->Tables/Queries->This database

Will there be any impact, in terms of the ability to use JET SQL, or VBA
functions in the SQL. I need the ability to use the
CREATE TABLE
(MyField Decimal (10,4))

But it appears that I can only do so if I check the option. Do you
foresee any problems with this option when used used in conjunction any
VBA functions?

Thanks in advance,

Ben

 

[Brendan Reynolds]

Hi all,

I would like to know if I selected the ANSI-92 SQL in:
Tools->Optinos->Tables/Queries->This database

Will there be any impact, in terms of the ability to use JET SQL, or VBA
functions in the SQL. I need the ability to use the
CREATE TABLE
(MyField Decimal (10,4))

But it appears that I can only do so if I check the option. Do you
foresee any problems with this option when used used in conjunction any
VBA functions?

Thanks in advance,

Ben

There's a bug associated with that option ...

http://support.microsoft.com/kb/824189

 

[RoyVidar]

Hi all,

I would like to know if I selected the ANSI-92 SQL in:
Tools->Optinos->Tables/Queries->This database

Will there be any impact, in terms of the ability to use JET SQL, or
VBA functions in the SQL. I need the ability to use the
CREATE TABLE
(MyField Decimal (10,4))

But it appears that I can only do so if I check the option. Do you
foresee any problems with this option when used used in conjunction
any VBA functions?

Thanks in advance,

Ben

Do you have to be able to use the interface? If not, just fire it
off through ADO, say

CurrentProject.Connection.Execute _
"CREATE TABLE myTable (MyField DECIMAL (10,4))"

Check out the following article (maybe also the articles it refers
too)
http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx

 

[Ben]

MG, Brendan, Roy -

Thanks so much for your helpful input. Much appreciated.

Ben

 

[David W. Fenton]

[re: SQL92 compatibility mode:]

It only affects the queries - mostly the wild-card characters:
JET uses * and ?; ANSI-92 uses % and _, respectively.

It also allows you to use () As TableName for derived tables instead
of the problematic []. As TableName format.

 

[David W. Fenton]

There's a bug associated with that option ...

http://support.microsoft.com/kb/824189

Ack. That's a pretty bad bug.

 

[David W. Fenton]

There's a bug associated with that option ...

http://support.microsoft.com/kb/824189

Actually, I changed my mind -- that's not a bad bug. There is no
dropdown list that *shouldn't* have a list of distinct values in it.
If the ID field is hidden, the ID + display fields will be distinct.
If there's no ID field, then the other values being distinct is
fine, as you won't be storing anything to connect back to the source
record, but even if you do, you'll still get a list of unique
values. There would be no utility in a combo box with identical rows
that also returned the same value in the bound field.

So, never mind my previous comment!

 

[Brendan Reynolds]

Actually, I changed my mind -- that's not a bad bug. There is no
dropdown list that *shouldn't* have a list of distinct values in it.
If the ID field is hidden, the ID + display fields will be distinct.
If there's no ID field, then the other values being distinct is
fine, as you won't be storing anything to connect back to the source
record, but even if you do, you'll still get a list of unique
values. There would be no utility in a combo box with identical rows
that also returned the same value in the bound field.

True. However, the bug happens with queries that do return distinct rows, if
they don't use the DISTINCT keyword. Take the example from the KB article
....

SELECT EmployeeID, LastName, FirstName FROM Employees;

If EmployeeID is the primary key, this will return only distinct records,
but will not avoid the bug. It has to be modified like so ...

SELECT DISTINCT EmployeeID, LastName, FirstName FROM Employees;

When EmployeeID is the primary key, the two queries will return exactly the
same records, but one of them will be hit by the bug and one won't.