What are the differences between Access SQL and T-SQL (SQL Server)?

Access SQL and SQL Server's T-SQL have more in common than differences. They share much the same structure, syntax, and many functions. And yet for all that, the differences can be frustrating. There are many times when an experienced Access user will know exactly how to do what they want in Access, but cannot figure out how to do the same thing in T-SQL.

This is especially true when trying to convert an Access query to a Pass-Through query. A Pass-Through query passes the SQL Statement on directly to the Server database (ie: SQL Server, Oracle, etc.). The problem is it must be in the syntax used by the Server database.

 

Basic Differences

I'll start by just listing the basics, but I'll spend some time over the next few months expanding on them. The following is certainly not exhaustive, but they are differences that cause most of the confusion when first trying to use T-SQL.

 

Delimiters

Type	Access	T-SQL
Numeric	no delimiter	no delimiter
Text	" or ' (quote or apostrophe)	' (apostrophe)
Date	# (pound sign)	' (apostrophe)
Wildcard	*	%
Delimited Identifiers	[..]	[..] or “..”

 

Distinct and DistinctRow

Both Access and SQL Server have the DISTINCT predicate which follows the SELECT, but T-SQL does not have DISTINCTROW.

 

Joins

Both Access and T-SQL use INNER JOIN for inner joins. However, for outer joins, Access uses LEFT JOIN and RIGHT JOIN, while T-SQL uses LEFT OUTER JOIN and RIGHT OUTER JOIN. T-SQL also has a FULL JOIN, which Access lacks. (Note: In the comments, Michal points out that T-SQL can also use LEFT JOIN and RIGHT JOIN.  He’s quite correct.)

 

Built-in Functions

Type	Access	T-SQL
Conditional	IIF()	CASE, IF-THEN-ELSE
Conversion	Cdate(), Clng(), Cint(), etc	CAST()
Formatting	Format()	CONVERT()
Nulls	Nz()	ISNULL()
Aggregates	Min Max First Last Sum Avg Count No equivalent	MIN MAX No equivalent No equivalent SUM AVG COUNT COUNT DISTINCT
Current Date	Date(), Now()	GETDATE()
Domain Aggregate Functions	DMax(), DMin, DCount, DLookup, etc	No counterpart. Must use subqueries.
String	InStr() Trim() Mid()	CHARINDEX RTRIM and LTRIM SUBSTRING
String Concatenation	&, +	+

 

Parameter Queries

Parameter queries as known in Access SQL cannot be converted directly to a Pass-through query. The most common way to simulate parameters is to build the Pass-Through query in VBA code and then execute it.

 

Great Features of T-SQL you should know about

 

Comments

Access does not allow comments in queries, but T-SQL DOES! This is a great feature of SQL Server. There are two methods of commenting:

• two dashes ( -- ) comments out a single line
• blocks of text bracketed by /*…*/ comments out multiple lines

 

Variables

Variables are another thing which Access does not have that T-SQL does. Variables provide another way to parameterize a T-SQL query.

 

Count Distinct

As mentioned in the Functions list, T-SQL has a COUNT DISTINCT aggregate function, which Access lacks. You can read more about it here: COUNT DISTINCT in Access

 

Multiple SQL Statement and Temporary Tables

These two things go hand in hand. In Access, a "query" can only have a single SQL Statement. But in T-SQL, you can execute multiple SQL Statements. You can even use a Make-Table query (SELECT..INTO) to create a temporary table, which can be used in subsequent SQL Statements. You can use this feature in an Access Pass-Through query.

 

Conclusion

There are, of course, many more differences between Access SQL and T-SQL. What I've concentrated on here, are those "gotchas" that crop up when trying to convert an Access query to a Pass-through query in T-SQL syntax. I'll discuss the details of these differences in future posts.