Most Access data types have clean equivalents in SQL Server. Text becomes VARCHAR, Number becomes INT or DECIMAL, Date/Time becomes DATETIME2. Migration tools handle these automatically and nothing breaks.
But several Access data types do not have clean SQL Server equivalents. These are the ones that cause broken forms, bad data, and confused users after migration. Knowing about them before you start saves hours of troubleshooting afterward.
Yes/No Fields
Access: Stores as 16-bit integer. True = -1, False = 0.
SQL Server: BIT. True = 1, False =
0.
What breaks: Any VBA code that explicitly checks for -1. For example:
If Me.IsActive = -1 Then ' This works in Access
If Me.IsActive = True Then ' This works in bothThe first line fails after migration because SQL Server returns 1 for True, not -1. The second line works everywhere because VBA evaluates any non-zero value as True.
Also breaks: Queries that use
WHERE IsActive = -1. After migration, no records match
because the column now contains 1 instead of -1.
Fix: Search your VBA code and queries for any
comparison to -1. Replace with True,
<> 0, or = 1. Do this before migration
so you can test both environments.
Memo Fields (Long Text)
Access: Memo fields store up to about 1GB of text with no practical limit in normal use.
SQL Server: Maps to NVARCHAR(MAX),
which stores up to 2GB.
What breaks: The mapping is fine for storage, but several Access features do not work the same way with NVARCHAR(MAX) through ODBC:
- Sorting. You cannot sort on a Memo field in Access, and you still cannot sort on NVARCHAR(MAX) in most contexts. But some queries that accidentally worked in Access may fail more visibly after migration.
- Indexing. You cannot create a standard index on NVARCHAR(MAX). If you need to search these fields frequently, consider full-text indexing.
- Truncation in linked tables. By default, Access displays only the first 255 characters of a Memo field in Datasheet view. This behavior continues with linked tables, but users sometimes interpret it as data loss.
Fix: Usually no action needed. If you need to index or search these fields, consider whether they should actually be NVARCHAR(MAX) or if a shorter NVARCHAR(4000) would suffice.
Hyperlink Fields
Access: A special multi-part field that stores
display text, URL, screen tip, and sub-address in a single field,
separated by # characters.
SQL Server: No equivalent. Maps to NVARCHAR, which
stores the raw text including the # separators.
What breaks: After migration, the field contains
text like Contoso Website#https://contoso.com## instead of
a clickable link. Forms that displayed clickable hyperlinks now show raw
text. Queries that searched hyperlink fields may not find matches
because the search text does not include the #
separators.
Fix: Before migration, decide how to handle these fields. Options: - Extract just the URL portion and store it in a plain text field - Keep the multi-part format and parse it in your application code - Split into separate columns (DisplayText, URL)
The cleanest approach is splitting into separate columns. This also makes the data usable by any application, not just Access.
Attachment Fields
Access: A special field type that stores multiple files (documents, images, PDFs) inside a single record. Internally, Access stores these in hidden system tables with a complex relational structure.
SQL Server: No equivalent. There is no way to store multiple files in a single column in SQL Server (or any standard database).
What breaks: Migration tools either skip attachment fields entirely or produce errors. The data is not lost — it is still in the Access file — but it does not transfer to SQL Server.
Fix: Before migration, extract the files from the Attachment fields. You have two options: - Store files on the file system and save the file path in a VARCHAR column - Store files as binary data in a VARBINARY(MAX) column (one file per record, which may require a related table for records with multiple attachments)
The file system approach is generally better. Databases are not efficient file storage systems, and putting large binary files in the database bloats it quickly.
Multi-Value Fields
Access: Allows a single field to store multiple values, displayed as a checkbox list. For example, a “Skills” field might contain “SQL, VBA, Python” as separate selectable values.
SQL Server: No equivalent. This violates first normal form (a field should contain a single atomic value), and server databases enforce this.
What breaks: Migration tools cannot convert multi-value fields. The data is either skipped or concatenated into a single string.
Fix: Restructure into a proper relational design before migration. Create a junction table:
Employeestable (EmployeeID, Name, …)Skillstable (SkillID, SkillName)EmployeeSkillsjunction table (EmployeeID, SkillID)
This is more work up front but produces a correct relational design that works in any database.
OLE Object Fields
Access: Stores embedded OLE objects — Word documents, Excel spreadsheets, bitmaps, or any other OLE-compatible content. The data includes OLE headers and metadata, not just the raw file content.
SQL Server: Maps to VARBINARY(MAX), which stores raw binary data.
What breaks: The OLE headers are preserved in the binary data, making the content difficult to use outside of Access. A bitmap stored as an OLE Object is not a standard BMP file — it has OLE wrapper data around it. Applications expecting standard file formats cannot read the content.
OLE Object fields are also large. A simple 50KB image might occupy 200KB as an OLE Object because of the overhead.
Fix: Before migration, extract the actual file content from the OLE wrapper. VBA code can do this by reading the binary data and stripping the OLE headers. For images, consider converting to standard formats (JPEG, PNG) and storing on the file system instead.
AutoNumber
Access: Generates sequential integers starting from 1. Never reuses a number, even if a record is deleted.
SQL Server: INT IDENTITY(1,1).
Generates sequential integers but can have gaps (after rollbacks,
deletes, or server restarts).
What breaks: Functionally, this mapping works fine.
The only issue is that VBA code using
DMax("ID", "TableName") + 1 to predict the next ID will
sometimes get the wrong answer, because identity values can have
gaps.
Also, if you use rs.AddNew followed by reading the ID
field, the behavior differs. In Access, the AutoNumber is assigned
immediately when you call AddNew. In SQL Server through
ODBC, the identity value is not available until after you call
rs.Update.
Fix: Use SELECT SCOPE_IDENTITY() after
an INSERT to get the newly assigned ID. Replace any code that predicts
or manually assigns AutoNumber values.
Date/Time
Access: Stores dates as floating-point numbers. Can represent dates from January 1, 100 to December 31, 9999. Internally flexible about invalid dates.
SQL Server: DATETIME supports January
1, 1753 to December 31, 9999. DATETIME2 supports January 1,
0001 to December 31, 9999.
What breaks: If your Access data contains dates
before 1753 (and you are using DATETIME), migration fails
for those records. More commonly, Access databases contain invalid date
values — empty strings stored in date fields, “00/00/0000”, or other
garbage — that Access tolerates but SQL Server rejects.
Fix: Clean your date data before migration. Run
queries to find records with invalid dates and either correct them or
set them to NULL. If you need dates before 1753, use
DATETIME2 instead of DATETIME on the SQL
Server side.
The Pre-Migration Checklist
Before running any migration tool:
- Identify all Attachment, Multi-Value, OLE Object, and Hyperlink fields
- Restructure these into standard field types
- Clean date fields of invalid values
- Search VBA code for
-1Boolean comparisons - Test the migration on a copy of the database first
Fixing these issues before migration is always easier than debugging them after.