Access SQL and SQL Server Differences?

[RoCoPe]

Hey everyone. have been out of the loop for about a year and a half, so I need to play catch up on a few topics. The following might sound like a dumb question but here goes:

Is the SQL (transact sql?) used in Access a subset or smaller (mini)version of SQL server? Trying to get a comparison list on the two, to see where t-sql ends and sql server continues.

Try and not beat me up too bad please.....

thanks
ROC

RoCoPe
"Learning on the Fly"

 

[bboffin]

Just to confuse matters, from Access 2000 onwards there are two dialects of SQL used by Access.

The first is the one used to handle the native Access tables which is distinctly different to the SQL used by SQL Server.

The second dialect IS the SQL Server dialect and it is used to handle MSDE and SQL Server tables (i.e. NO differences)

The main difference between the two dialects is that you can use all the VBA functions in Access SQL along with direct references to form and public variables. There are a few other differences that relate to how joins are handled for example. Access SQL does not support proper stored procedures nor proper temporary tables.

The SQL Server dialect requires that you must use only the SQL functions and that parameters can only be passed in via stored procedure arguments.



Bob Boffin

 

[RoCoPe]

Thanks Bob.
It appears that there is a bit of crossover between the two. A book or two a few late nights, and I should be up to speed.

Any other input out there?

RoCoPe
"Learning on the Fly"