SQL more like TSQL

[Andre]

Access 2003

I'm wondering if there is a way to have Access type queries more TSQL like.
For example, the query below is from Access:
SELECT Product.productID, Product.productName
FROM Product
WHERE (((Product.productName) ALike "b%"));

If I were writing this query in SQL's Query Analyzer, it would be like this:
SELECT productID, productName
FROM Product
WHERE productName Like 'b%'

Is it possible to have Access interpret queries so they are more TSQL-like?
I've already turned on SQL Server Compatible Syntax (Ansi 92), but I'd
personally never write these queries using all these parenthesis. Nor have
I ever heard of "ALike".

Thanks, Andre

 

[Douglas J. Steele]

I don't think you can stop Access from inserting the parentheses when you
use the graphical query builder, but you can certainly remove them and the
query will work fine. The only place where they're critical to Access is in
Joins.

You should also be able to change the ALike to Like.

In other words, what you've put as being what you'd use in SQL Server will
work in Access, at least in this case. (The only exception is if you're
using DAO rather than ADO. Then, the wild card character would be *, not %)

 

[Chaim]

Access 2003

I'm wondering if there is a way to have Access type queries more TSQL like.
For example, the query below is from Access:
SELECT Product.productID, Product.productName
FROM Product
WHERE (((Product.productName) ALike "b%"));

If I were writing this query in SQL's Query Analyzer, it would be like this:
SELECT productID, productName
FROM Product
WHERE productName Like 'b%'

Is it possible to have Access interpret queries so they are more TSQL-like?
I've already turned on SQL Server Compatible Syntax (Ansi 92), but I'd
personally never write these queries using all these parenthesis. Nor have

None of them are needed and most people would not write SQL that way. The
Query Builder is, after all, like any other code generator- follow the syntax
to a T and avoid any possibility of ambiguity, even when, to a person, there
is no such possibility. After all, the code generator don't have to read it,
just parse it.

I ever heard of "ALike".

Nor have I. Was this generated by the Query Builder? I use A2000 and it
generates 'LIKE". I can't imagine that Microsoft would deviate from either
SQL92 or break so completely with their own past.

 

[Andre]

I ever heard of "ALike".

Nor have I. Was this generated by the Query Builder? I use A2000 and it
generates 'LIKE". I can't imagine that Microsoft would deviate from either
SQL92 or break so completely with their own past.

Yes, Access 2003 generated ALike when I switched:
Tools/Options,Tables/Queries tab, SQL Server Compatible Syntax (ANSI 92) on
for "This database". The next new query I wrote using Like was then
switched to ALike.

Andre