| title | Incompatible Access Features (AccessToSQL) | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| description | Learn about possible migration issues with Access database features that aren't compatible with SQL Server, and how to address them. | |||||||||||||||||
| author | nilabjaball | |||||||||||||||||
| ms.author | niball | |||||||||||||||||
| ms.reviewer | randolphwest | |||||||||||||||||
| ms.date | 12/30/2025 | |||||||||||||||||
| ms.service | sql | |||||||||||||||||
| ms.subservice | ssma | |||||||||||||||||
| ms.topic | concept-article | |||||||||||||||||
| ms.collection |
|
|||||||||||||||||
| helpviewer_keywords |
|
Not all Access database features work with [!INCLUDE ssNoVersion]. For example, [!INCLUDE ssNoVersion] and Access use different sets of reserved keywords. These differences can cause problems when you migrate using SQL Server Migration Assistant (SSMA). The following section describes possible migration problems and how you can fix them.
Review the following Access settings or features that can affect migration to [!INCLUDE ssnoversion-md] or Azure SQL.
- Tables don't have unique indexes
- Tables have replication columns
- Tables with unique indexes contain multiple NULL values
- Tables contain date values that are out of SQL Server range
- Index lengths exceed 900 bytes
- Object names are SQL Server keywords, or contain special characters
- Field sizes differ in primary key or foreign key relationships
- Referenced tables don't have a primary key or a unique index
- Tables have hyperlink columns
- Functions can't be converted to SQL Server or Azure SQL
If you migrate a table without a unique index to [!INCLUDE ssNoVersion], you can't modify the table after migration. This limitation can cause application compatibility problems.
When you convert Access database objects, the Output window lists any Access tables that don't have unique indexes.
You can configure Access to add a primary key on the [!INCLUDE ssNoVersion] table during conversion. For more information, see Project Settings (Conversion).
If you migrate an Access table that includes replication system columns to [!INCLUDE ssNoVersion], Jet replication functionality stops working after migration.
After migration, consider using [!INCLUDE ssNoVersion] replication to maintain synchronized copies of your databases.
Before version 8.13, you can't transfer Access tables that have unique indexes with multiple null values to [!INCLUDE ssNoVersion]. In [!INCLUDE ssNoVersion], unique indexes disallow multiple nulls. Migration fails for these tables.
SSMA flags this issue in assessment reports. To create an assessment report, see Assess Access database objects for conversion.
If this problem exists, make sure that the primary key doesn't have duplicate null values. Or, remove the primary key or unique indexes that contain multiple null values.
The [!INCLUDE ssNoVersion] datetime type accepts dates in the range of January 1, 1753, to December 31, 9999, only. Access accepts dates in the range of January 1, 100, to December 31, 9999.
SSMA flags this issue in assessment reports. To create an assessment report, see Assess Access database objects for conversion.
You can configure how SSMA resolves dates that are out of the [!INCLUDE ssNoVersion] range. For more information, see Project Settings (Migration).
[!INCLUDE ssNoVersion] indexes have a 900-byte limit for the total size of index key columns. If your Access tables use larger indexes, SSMA displays a warning.
If you continue with data migration, the migration might fail.
Access and [!INCLUDE ssNoVersion] have different sets of reserved keywords and special characters. [!INCLUDE ssNoVersion] accepts objects that are named by using [!INCLUDE ssNoVersion] keywords or that contain special characters if you use bracketed or quoted identifiers, such as select or [select].p. For more information, see Database identifiers.
Note
To use quotation marks to delimit identifiers, SET QUOTED_IDENTIFIER must be ON.
For example, CREATE TABLE [schema](c1 [FOR]) is a valid statement, even though schema and FOR are reserved keywords. Also, CREATE TABLE [xxx*yyy](c1 x&y) is a valid statement, even though the table and column name contain the special characters * and &.
All queries that reference those objects must also use the names with brackets or quotation marks. For example, the query SELECT * FROM schema fails. The correct query is: SELECT * FROM [schema].
When you convert Access database objects, the Output pane lists any Access tables that use keywords or special characters. You can modify the tables in Access, and then remove and add the database again. Or, you can modify queries that reference those objects so that the queries use brackets or quotation marks to delimit identifiers. If you don't modify your queries, your Access applications might return errors or have other problems.
[!INCLUDE ssNoVersion] doesn't support the Jet functionality of linking columns that have different data types or sizes with foreign key constraints.
When you convert Access database objects, the Output window lists any primary key or foreign key constraints that aren't converted to [!INCLUDE ssNoVersion]. You can alter data types and sizes on Access columns so that they match, then remove and add the Access database back again. Or, you can migrate data although these constraints aren't created in [!INCLUDE ssNoVersion].
Access accepts relationships between tables where the referenced table doesn't have a primary key or a unique index. However, [!INCLUDE ssNoVersion] doesn't support this type of relationship.
When you convert Access database objects, the Output window lists any tables that have relationships but no primary key or unique index. You can alter the tables to add primary keys or unique indexes, then remove and add the Access database back again. Or, you can migrate data although the relationship between the tables is broken.
[!INCLUDE ssNoVersion] doesn't support hyperlink columns. Instead, the columns are treated like Access memo columns. By default, these columns are converted to nvarchar(max) columns in [!INCLUDE ssNoVersion]. You can customize the mapping. For more information, see Map source and target data types.
Access default expressions or validation rules might include Access system functions or user-defined functions that don't map to [!INCLUDE ssNoVersion] or Azure SQL. If you use functions that don't map to [!INCLUDE ssNoVersion] or Azure SQL, you can't load the default expressions or validation rules into [!INCLUDE ssNoVersion] or Azure SQL.