If you migrate your Access data to SQL Server or you create an Access solution with SQL Server as the back-end database, it's vital that you know the differences between Access SQL and SQL Server Transact SQL (TSQL). What follows are the important variations you need to know so your solution works as intended.
For more information, see Access SQL: basic concepts, vocabulary, and syntax and Transact-SQL Reference.
Syntax and expression differences
There are a few syntax and expression differences that require conversion. The following table summarizes the most common ones.
| Difference | Access SQL | SQL Server TSQL |
|---|---|---|
| Relational database attribute | Usually called a field | Usually called a column |
| String literals | Quote ("), such as "Mary Q. Contrary" | Apostrophe ('), such as 'Mary Q. Contrary' |
| Date literals | Pound sign (#), such as #1/1/2019# | Apostrophe ('), such as '1/1/2019' |
| Multiple wildcard character | Asterisk (*), such as "Cath*" | Percent (%), such as 'Cath%' |
| Single wildcard character | Question Mark (?), such as "Cath?" | Underscore (_), such as "Cath_" |
| Modulo operator | MOD operator, such as Value1 MOD Value2 | Percent (%), such as Value1 % Value2 |
| Boolean values | WHERE Bitvalue = [True | False] Or WHERE Bitvalue = [-1 | 0] |
WHERE Bitvalue = [1 | 0] |
| Parameters | [<A name that is not a defined column>] Or In SQL view, use the SQL Parameters Declaration |
@ParamName |
Notes
Access uses quote characters (") around table names and objects. T-SQL can use them for table names with spaces, but this is not standard naming practice. In most cases, object names should be renamed without spaces, but queries must also be rewritten to reflect new table names. Use brackets [ ] for tables that cannot be renamed but which do not conform to naming standards. Access also adds extra parentheses around parameters in queries, but they can be removed in T-SQL.
Consider using the canonical date format, yyyy-mm-dd hh:nn:ss, which is an ODBC standard for dates stored as characters that provides a consistent way to represent them across databases and preserves the date sort order.
To avoid confusion when comparing Boolean values, you can use the following comparison for Access and SQL Server:
- Test for false value WHERE Bitvalue = 0
- Test for true value WHERE Bitvalue <> 0
Null values
A null value is not an empty field that means "no value at all". A null value is a placeholder that means that data is missing or unknown. Database systems that recognize null values implement "three-valued logic", which means something can be true, false, or unknown. If you don't properly handle null values, you can get incorrect results when making equality comparisons or evaluating WHERE clauses. Here is a comparison of how Access and SQL Server handle null values.
Disable null values in a table
in Access and SQL Server, the default experience is that null values are enabled. To disable null values in a table column, do the following:
- In Access, set a field's Required property to Yes.
- In SQL Server, add the NOT NULL attribute to a column in a CREATE TABLE statement.
Test for null values in a WHERE clause
Use the IS NULL and IS NOT NULL comparison predicates:
In Access, use IS NULL or IS NOT NULL. For example:
SELECT … WHERE column IS NULL.In SQL Server, use IS NULL or IS NOT NULL. For example:
SELECT … WHERE field IS NULL
Convert null values with functions
Use the null functions to protect your expressions and return alternative values:
In Access, use the NZ (value, [valueifnull]) function which returns 0 or another value. For example:
SELECT AVG (NZ (Weight, 50) ) FROM ProductIn SQL Server, use the ISNULL(Value, replacement_value) function which returns 0 or another value. For example:
SELECT AVG (ISNULL (Weight, 50)) FROM Product
Understand Database options
Some database systems have proprietary mechanisms:
- In Access, there are no database options that pertain to Null.
- In SQL Server you can use the SET ANSI_NULLS OFF option for direct equality comparisons with NULL using the = and <> operators. We recommend that you avoid using this option because it is deprecated, and it can confuse others who rely on ISO-compliant null-handling.
Conversion and casting
Whenever you are working with data or programming, there is a persistent need to convert from one data type to another. The process of conversion can be simple or complex. Common issues that you need to think about are: implicit or explicit conversion, the current date and time regional settings, rounding or truncation of numbers, and data type sizes. There's no substitute for thorough testing and confirming of your results.
In Access, you use the Type Conversion Functions, of which there are eleven, each starting with the letter C, one for each data type. For example, to convert a floating point number to a string:
CStr(437.324) returns the string "437.324".
In SQL Server, you primarily use the CAST and CONVERT TSQL functions, although there are other Conversion Functions for specialized needs. For example, to convert a floating point number to a string:
CONVERT(TEXT, 437.324) returns the string "437.324"
DateAdd, DateDiff, and DatePart functions
These commonly used date functions are similar (DateAdd, DateDiff, and DatePart) in Access and TSQL, but the use of the first argument differs.
In Access, the first argument is called the interval, and it's a string expression that requires quotes.
In SQL Server, the first argument is called the datepart, and it uses keyword values that don't require quotes.
Component Access SQL Server Year "yyyy" year, yy, yyyy Quarter "q" quarter, qq, q Month "m" month, mm, m Day of Year "y" dayofyear, dy, y Day "d" day, dd, d Week "ww" wk, ww Day of Week "w" weekday, dw Hour "h" hour, hh Minute "n" minute, mi, n Second "s" second, ss, s Millisecond millisecond, ms
Functions comparison
Access queries can contain calculated columns that sometimes use Access Functions to get results. When you migrate queries to SQL Server, you need to replace the Access function with an equivalent TSQL function if one is available. If there is no corresponding TSQL function, then you can usually create a computed column (The TSQL term used for a calculated column) to do what you want. TSQL has a wide array of functions and it's to your benefit to see what's available. For more information, see What are the SQL database functions?.
The following table shows which Access function has a corresponding TSQL function.