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.
