SQL more like TSQL

A

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
 
D

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 %)
 
C

Chaim

Andre said:
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.
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top