Migrating From Access to SQL Server: Data Types and Gotchas

The file still runs the business, but more users now depend on it. When migrating from Access, types and query expressions need explicit review rather than a direct copy. Preserve the original values, validate the mapped data, and test the forms against the new server contract.

Inventory Types Before Migrating From Access

Map Short Text to nvarchar with a deliberate length. Map Long Text to nvarchar(max) when its size requires it. Unicode storage preserves the supported text repertoire, while varchar with a UTF-8 collation is another deliberate choice on recent versions. Do not choose a short default merely because the current sample values happen to fit.

Access Number has several field sizes. Byte fits tinyint. Integer fits smallint. Long Integer fits int. Large Number maps to bigint when that source type is used. Single and Double correspond to approximate real and float values, while an exact decimal field needs matching decimal precision and scale. I inspect the actual field definition instead of treating every Number as an integer.

Preserve Currency, Flags and Dates

Currency needs an exact representation with its four fractional places. A suitably sized decimal column makes the precision contract explicit. Yes/No values conventionally use minus one for true in the source, while SQL Server bit exposes one for true. Normalize flags deliberately, and reject unexpected values rather than calling every nonzero value valid without review.

CREATE TABLE #AccessStage
(
    SourceID int NOT NULL,SourceFlag smallint NULL,
    SourceAmount decimal(19,4) NULL,SourceDateText nvarchar(40) NULL
);
INSERT #AccessStage VALUES
    (1,-1,12.3456,N'1700-01-01T08:00:00'),
    (2,0,25.0000,N'2026-09-01T09:00:00'),
    (3,7,NULL,N'not a date');
SELECT SourceID,
       CASE WHEN SourceFlag=-1 THEN CONVERT(bit,1)
            WHEN SourceFlag=0 THEN CONVERT(bit,0) END AS TargetFlag,
       SourceAmount,TRY_CONVERT(datetime2(7),SourceDateText,126) AS TargetDate
FROM #AccessStage;

The sample staging rows are fictional. Date/Time values require a timezone decision because the source value itself does not identify a zone. SQL Server datetime cannot represent dates before 1753. Prefer datetime2 for its broader date range and explicit precision. Check unusual legacy values before conversion, including dates accepted by the source that fall outside the target type's range.

Report Conversion Exceptions Separately

A NULL from TRY_CONVERT prevents an exception, but it also needs interpretation. Separate a source NULL from a failed conversion. Keep invalid rows in a rejection report with their source identity. The accepted population and rejected population should reconcile to the extract. Silently dropping rows makes the load look successful while changing the business data.

SELECT SourceID,SourceFlag,SourceDateText
FROM #AccessStage
WHERE (SourceFlag IS NOT NULL AND SourceFlag NOT IN(-1,0))
   OR (SourceDateText IS NOT NULL
       AND TRY_CONVERT(datetime2(7),SourceDateText,126) IS NULL);

I review those exceptions with the application owner before choosing replacements. A default date or false flag is a business change, not a conversion convenience. Preserve source text and the approved mapping in the migration evidence. That gives you a way to explain a disputed value after the original file stops being the operational source.

Carry AutoNumber Identity Forward

An incrementing Long Integer AutoNumber commonly maps to int IDENTITY. Existing keys should remain stable when related records depend on them. Unique identifier based AutoNumber fields need a uniqueidentifier design instead. Inspect the actual source type. The example creates a target and loads chosen existing identifiers using IDENTITY_INSERT in a disposable database.

CREATE TABLE dbo.Customer
(
    CustomerID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Customer PRIMARY KEY,
    CustomerName nvarchar(100) NOT NULL,
    Notes nvarchar(max) NULL,
    IsActive bit NOT NULL,
    RecordedAt datetime2(7) NULL
);
SET IDENTITY_INSERT dbo.Customer ON;
INSERT dbo.Customer(CustomerID,CustomerName,Notes,IsActive,RecordedAt)
VALUES(100,N'Sample Customer',N'Sample notes',1,'2026-09-01T09:00:00');
SET IDENTITY_INSERT dbo.Customer OFF;
DBCC CHECKIDENT(N'dbo.Customer',NORESEED);

Only one table in a session can have IDENTITY_INSERT enabled. Ensure the load turns it off on error too. Verify the next generated identity after loading and reconcile every related key. Do not reseed downward over existing values. An identity column generates values, while the primary key enforces their uniqueness. They are separate parts of the target design.

Rewrite Wildcards and IIf When Migrating From Access

Traditional Access wildcard syntax uses star and question mark. SQL Server LIKE uses percent and underscore. Literal wildcard characters need escaping in either contract. Check the source query mode because Access can also use ANSI compatible wildcard behavior. Port the meaning of the search rather than replacing every character in stored query text blindly.

SELECT CustomerID,CustomerName FROM dbo.Customer
WHERE CustomerName LIKE N'Sample%';
SELECT CustomerID,CustomerName FROM dbo.Customer
WHERE CustomerName LIKE N'S_mple%';
SELECT CustomerID,
       CASE WHEN IsActive=1 THEN N'Active' ELSE N'Inactive' END AS StatusText
FROM dbo.Customer;

IIf expressions can become CASE expressions. Review NULL behavior and result types for each branch. A branch containing division or conversion deserves a boundary test instead of assuming identical evaluation behavior. Include the expected result for missing values. Readability improves when the condition describes the actual business state rather than preserving a long nested expression from the source query.

Replace Now and Parameter Assumptions

Now() can map to SYSDATETIME() when the intended value is server local time. That server can be in a different timezone from the workstation that previously supplied the value. For stored instants, choose an explicit UTC contract with SYSUTCDATETIME when appropriate. Converting syntax alone does not preserve the original location's clock semantics.

SELECT SYSDATETIME() AS ServerLocalTime,SYSUTCDATETIME() AS UtcTime;
DECLARE @customer_id int=100;
SELECT CustomerID,CustomerName,Notes
FROM dbo.Customer WHERE CustomerID=@customer_id;

Use typed parameters from forms and application code. Avoid concatenated date strings that depend on local date order. Check transactions, affected row handling, and key retrieval after inserts. Which screen assumes a new key or a timestamp comes from the local file immediately? Test that screen through the actual application connection before considering the data move complete.

Find Missing Primary Keys After Migrating From Access

Run this in the target database under an identity with full intended metadata visibility. It returns user tables without a primary key constraint. A unique index is useful, but the report deliberately asks about declared primary keys. Migrated forms and synchronization logic need stable row identity, so review each exception rather than accepting a heap as proof of a successful import.

SELECT s.name AS SchemaName,t.name AS TableName
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id=t.schema_id
WHERE t.is_ms_shipped=0
  AND NOT EXISTS
      (SELECT 1 FROM sys.key_constraints AS k
       WHERE k.parent_object_id=t.object_id AND k.type=N'PK')
ORDER BY s.name,t.name;

When migrating from Access, also validate foreign keys, required fields, unique rules, and collation differences. A case insensitive target can reject source values that were treated as distinct elsewhere. A migration spreadsheet can look beautifully complete until the first duplicate key arrives. Run constraint checks on the validated extract before the final cutover window.

Rehearse the Business Workflow

Compare row populations, exact amounts, text lengths, flags, dates, and relationships. Test inserts and updates from the forms, including conflicting edits and canceled work. Preserve a recoverable source copy and define when writes switch to the server. Two writable authorities create reconciliation work immediately, so keep any overlap controlled and time limited.

I treat migrating from Access as a contract change with a data move inside it. The destination types and queries should explain the business values clearly. A verified extract is only one checkpoint. The application is ready when its key handling, concurrency, date rules, and normal daily workflows agree with the new server design.

Related reading on this blog: Assessing a Database Before a Migration and A Migration Cutover Checklist.

A migration is not a row transfer, it is a checked translation of the data contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.