Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010.
If you migrate your Access data to SQL Server or you design an Access solution with SQL Server as the back-end database, it’s crucial that you understand the distinctions between Access SQL and SQL Server Transact SQL (TSQL). In this article, the significant differences you must know to ensure your solution works in your desired way will be examined.
For further information, read Access SQL: basic concepts, vocabulary, and syntax and Transact-SQL Reference.
Syntax and expression differences
There are a few syntax and expression variations that mandate conversion. The following table summarises the most familiar 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]OrWHERE Bitvalue = [-1 | 0]||WHERE Bitvalue = [1 | 0]|
|Parameters||[<A name that is not a defined column>]OrIn SQL view, use the SQL Parameters Declaration.||@ParamName|
- Access employs quote characters (“) around table names and objects. T-SQL can apply them for table names with spaces, but this contrasts with typical naming practice. In many cases, object names should be renamed excluding spaces, but queries have to also be updated to represent new table names. Utilise brackets [ ] for tables that are unable to be renamed but which are redefine from the status quo of naming standards. Access even appends extra parentheses around parameters in queries, but they can be erased in T-SQL.
- Take into account using the canonical date format, yyyy-mm-dd hh:nn:ss, which is an ODBC standard for dates held as characters that supplies a standardised way to denote them throughout databases and maintains the date sort order.
- To evade confusion when analysing Boolean values, you can apply the following comparison for Access and SQL Server:
- Test for false value – WHERE Bitvalue = 0
- Test for true value – WHERE Bitvalue <> 0
A null value is the opposite of an empty field that means “no value at all”. Instead, a null value is a placeholder that symbolises that data is missing or unknown. Database systems that recognise null values enforce “three-valued logic”, reflecting that something can be true, false, or unknown. If you don’t effectively handle null values, you can obtain inaccurate results during the process of 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 standard experience is that null values are allowed. To disable null values in a table column, do the following:
- In Access, format a field’s Required property to Yes.
- In SQL Server, insert 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 concludes:
- 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
Deploy the null functions to preserve your expressions and capture alternative values:
- In Access, use the NZ (value, [valueifnull]) function which returns 0 or another value. For example:
SELECT AVG (NZ (Weight, 50) ) FROM Product
- In 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 pioneering mechanisms:
- In Access, there are no database options that relate to Null.
- In SQL Server, you can apply the SET ANSI_NULLS OFF option for direct equality comparisons with NULL employing the = and <> operators. We advise 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
Anytime you are working with data or programming, there is a constant need to convert from one data type to another. The process of conversion can be easy or difficult. Common issues that you must consider are: implicit or explicit conversion, the actual date and time regional settings, rounding or truncation of numbers, and data type sizes. There’s no replacement for intricate testing and reaching of your results.
In Access, you utilise the Type Conversion Functions, of which there are eleven, each beginning with the letter C, one for every 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 mainly use the CAST and CONVERT TSQL functions, even though there are other Conversion Functions for dedicated 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 frequently used date functions are related (DateAdd, DateDiff, and DatePart) in Access and TSQL, but the application of the first argument varies.
- In Access, the first argument is named the interval, and it’s a string expression that needs quotes.
- In SQL Server, the first argument is known as the datepart, and it employs keyword values that don’t require quotes. ComponentAccessSQL Server Year”yyyy”year, yy, yyyyQuarter”q”quarter, qq, qMonth”m”month, mm, mDay of Year”y”dayofyear, dy, yDay”d”day, dd, dWeek”ww”wk, wwDay of Week”w”weekday, dwHour”h”hour, hhMinute”n”minute, mi, nSecond”s”second, ss, sMillisecondmillisecond, ms
Access queries can include calculated columns that often utilise Access Functions to obtain results. Once you migrate queries to SQL Server, you must replace the Access function with an equivalent TSQL function if one is available. If there is no matching TSQL function, then you can usually create a computed column (The TSQL term used for a calculated column) to do as you intend. TSQL has a broad array of functions and it’s to your advantage to peruse what’s available. For more information, see What are the SQL database functions?.
The following table presents which Access function has a relevant TSQL function.