Access SQL dialect documentation?"

  • Thread starter Thread starter Kevin Davidson
  • Start date Start date
K

Kevin Davidson

I bought a copy of Access 2002, but when I tried to write some queries
against the database I found no documentation on the dialect of SQL that
Access uses.

The Help file has tidbits scattered among various topics but nothing
comprehensive. So where is it?

Kevin
 
You can learn a lot about Access SQL by using the Query Builder and
switching to the SQL view. I found "Access Database Design and Programming"
by Steve Roman (O'Reilly) has a reasonably good section on Access SQL
including both DDL and DML.
 
Kevin said:
I bought a copy of Access 2002, but when I tried to write some queries
against the database I found no documentation on the dialect of SQL that
Access uses.

The Help file has tidbits scattered among various topics but nothing
comprehensive. So where is it?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Open the Access Help (with the database window showing). Under the
Contents tab there is a listing "Microsoft JET SQL Reference." All
the subheadings have info on how to use SQL in Access. The heading
"Data Manipulation Language" has all the commands usually used in
queries.

You can have a more SQL-92 compliant queries (.mdb files) by selecting
Tools > Options from the main menu. In the Options dialog box select
the "Tables/Queries" tab and check the "SQL Server Compatible Syntax
(ANSI-92)" "This database" check box.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFIqhYechKqOuFEgEQLz8gCdFvrQwPmdxc+wFw5uqCwn3D97ePcAn3p1
TP/X21TyGErzyUWjueOf6TTF
=FDN4
-----END PGP SIGNATURE-----
 
Kevin D.,

Do a search on your installation harddrive for filename "JETSQL40.CHM".


Sincerely,

Chris O.
 
Hi Kevin,

Thanks for posting in the community!

I understand that when you migrate from SQL to Access, you might meet
with the problem of difference between T-SQL and Jet-SQL. I believe Chris
has pointed that the JET40.CHM is comprehensive and light you on the
right way.

Also thank to MGFoster's suggestion to enable "SQL Server Compatible
Syntax (ANSI-92)" and check the SQL-92 compliant queries in your project.

As to the books Roger recommended. I'd like to introduce another
classical one "Programming Microsoft Access 2000" written by Rick
Dobson. Although it's documented to Access 2000, I believe it applis to
AccXP perfrctly on the field of Jet SQL and dialect of SQL that Access uses.

Does that answer your question Kevin? If there is anything more I can do to
assist you, please feel free to let us know.

Thanks,

Billy Yao
Microsoft Online Support
 
MGFoster wrote:
....
You can have a more SQL-92 compliant queries (.mdb files) by selecting
Tools > Options from the main menu. In the Options dialog box select
the "Tables/Queries" tab and check the "SQL Server Compatible Syntax
(ANSI-92)" "This database" check box.
Click to expand...

Now that is interesting. What I'm trying to do is to develop queries to
go against Access through ODBC, not use the Access program itself. Is
there a way to invoke this SQL Server Compatible Syntax through ODBC?

I noticed, for example, that when I tried to use a date constant in the
form 'mm/dd/yy', it didn't work although one help file that I read
mentioned that dates in single quotes did work (mentioned ANSI-92).
Since I'm much more familiar with SQL Server (T-SQL), it would be very
helpful to be able to write in that dialect for my work against an
Access database.

Thanks,

Kevin
 
Kevin-

You can download this document:
http://www.viescas.com/aftp/Download/UnderstandingSQL.doc

It's a comprehensive reference for the SQL dialect(s) understood by Access.
The syntax examples reference sample databases that come with the book, but
I think you'll be able to understand them without the databases.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Kevin said:
MGFoster wrote:
...



Now that is interesting. What I'm trying to do is to develop queries to
go against Access through ODBC, not use the Access program itself. Is
there a way to invoke this SQL Server Compatible Syntax through ODBC?

I noticed, for example, that when I tried to use a date constant in the
form 'mm/dd/yy', it didn't work although one help file that I read
mentioned that dates in single quotes did work (mentioned ANSI-92).
Since I'm much more familiar with SQL Server (T-SQL), it would be very
helpful to be able to write in that dialect for my work against an
Access database.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know how to, or if you can, inform ODBC that the db is in ANSI
92 compliance. I believe the compliance has to be invoked by Access
when it builds the database (creates tables/queries).

Not all T-SQL statements can be used in JET-SQL (or SQL92 for that
matter - T-SQL having many enhancements over SQL92 & JET-SQL).

In JET dbs dates are usually delimited by the hash-mark:
#mm/dd/yyyy#. Only if the db is created in ANSI-92 compliance will
you be able to use single-quotes to delimit dates.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFPAV4echKqOuFEgEQI+CgCdGB8r8hpB//NCXrNuGFn8AYvVmTQAnjzl
5B6AVtMYQkrphKu1wRwKtn3q
=eMu1
-----END PGP SIGNATURE-----
 
Billy said:
Hi Kevin, ....
Does that answer your question Kevin? If there is anything more I can do to
assist you, please feel free to let us know.
Click to expand...

Thanks to you and and the others who took their time to point me towards
the documentation I needed and to MGFoster for the information on SQL-92
compliance.

Obviously I don't work with Access normally. Sometimes when you have to
deal with a fairly technical problem in the middle of a body of
knowledge you're not familiar with, a newsgroup such this is an
invaluable time saver. I appreciate the assistance.

Kevin Davidson
QS Technologies
 
Back
Top