Home / Guides
Migration guide

Access Database to SQL Server: Tables, Queries, Forms

By · November 16, 2026 · 13 min read

The short version

An Access database to SQL Server migration moves the tables and the data. The forms, reports, macros and VBA stay in Access and keep working once the Access tables are linked to the new SQL Server tables. Microsoft's tool is SQL Server Migration Assistant (SSMA) for Access: it converts the schema, copies the data, and can replace each Access table with a linked one. Only select queries without parameters convert; everything else in Access SQL is a rewrite by hand, because #dates#, * wildcards, & and Nz have no direct T-SQL meaning.

What moves to SQL Server and what stays in Access

Think of the .accdb or .mdb file as two things glued together: a data file and an application. Microsoft's own advice is to split them, with a back end that "must only contain tables and relationships" and a front end holding "forms, reports, queries, macros, VBA modules" and the linked tables (Migrate an Access database to SQL Server). The migration replaces the back end with SQL Server. The front end stays a file on someone's desk.

Object Where it ends up
Tables and data SQL Server
Select queries without parameters SQL Server views, via SSMA
Action queries, parameter queries Stay in Access, or rewritten in T-SQL by hand
Forms, reports, macros, VBA Stay in Access, pointed at linked tables

The support page is plain about it: "Forms, reports, macros, and VBA modules are not converted", and "Only Select Queries are converted; other queries are not, including Select Queries that take parameters" (same page).

I did not run SSMA for this guide. It is a Windows program and I had neither Windows nor a SQL Server instance available, so this section is the documented procedure, not a transcript.

  1. Install SSMA for Access, create a project, add the database files (Access to SQL Server migration guide).
  2. Create Report, and read its conversion statistics, errors and warnings.
  3. Check Tools > Project Settings > Type Mapping before converting (table below).
  4. Connect to SQL Server, Convert Schema, then Synchronize with Database to create the objects.
  5. Migrate Data.
  6. Link: in Access Metadata Explorer, select the tables, right-click Tables, Link. "SSMA for Access backs up the original Access table and creates a linked table" (Link Access applications to SQL Server).

Step 6 is what keeps the forms alive. Linking "modifies your Access database so that your queries, forms, reports, and data access pages use the data in the SQL Server" (same page). Unlink restores the original Access table and its data, which makes it a cheap rollback.

Two warnings from that page worth reading before go-live. With SQL Server authentication, "your password is stored in clear text on the linked Access tables", so use Windows authentication. And Access code that reads an AutoNumber right after AddNew breaks: in SQL Server the identity value exists only after the record is saved.

Before any of this, the support page asks for an index and a primary key on every Access table, and says "SSMA doesn't migrate tables that contain the Attachment column" (Migrate an Access database to SQL Server).

Access to SQL Server type mapping

Three columns of evidence: SSMA's documented defaults (Project Settings (Type Mapping)), what mdb-schema printed, and what our converter wrote. The last two I ran.

Access type SSMA default mdb-schema sybase Our converter Watch for
Yes/No bit bit BIT NOT NULL, values 0 and 1 Access stores -1 for true, SQL Server 1
Currency money money DECIMAL(19,4) Access keeps 4 decimal places
Date/Time datetime smalldatetime DATETIME2 Range, see below
AutoNumber not in the table int, no identity INT IDENTITY(1,1) and the primary key Existing IDs are inserted with IDENTITY_INSERT
Long Text (Memo) nvarchar(max) nvarchar(max) NVARCHAR(MAX) Nothing in my files
Hyperlink nvarchar(max) nvarchar(max) NVARCHAR(MAX) Stops being clickable
OLE Object longbinary to varbinary(max) varbinary(max) VARBINARY(MAX), 0x literals The OLE header stays in front of the image
Decimal float not in my files not in my files Change SSMA's default to decimal
Attachment table skipped not in my files not in my files Not available in .mdb at all

Notes on the rows that bite:

mdb-schema has no SQL Server backend

The obvious first try:

$ mdb-schema DateTestDatabase.mdb sqlserver
Invalid backend type

mdbtools 1.0.1 supports "access, sybase, oracle, postgres, mysql and sqlite" (mdb-schema manual). mssql and tsql gave the same error. sybase is the closest relative. On the sample .accdb from the mdbtools test data:

$ mdb-schema ASampleDatabase.accdb sybase
...
CREATE TABLE "Asset Items"
 (
    "Asset No"          nvarchar (5),
    "Asset Category"            nvarchar (20),
...
    "Acquired"          smalldatetime,
    "Cost"          money,
    "Warranty"          int,
...
    "Comments"          nvarchar(max)
);
COMMENT ON COLUMN "Asset Items"."Asset No" IS 'Records the asset number';
...

Three problems for SQL Server. COMMENT ON is not T-SQL; descriptions go through sp_addextendedproperty (sp_addextendedproperty), so delete those lines or rewrite them. smalldatetime will refuse any date before 1900. And the keys are gone: mdbtools knows them, because the postgres backend on Northwind prints them, but the sybase backend drops them.

$ mdb-schema nwind.mdb postgres | grep -E "SERIAL|PRIMARY" | head -3
ALTER TABLE "order details" ADD CONSTRAINT "order details_pkey" PRIMARY KEY ("orderid", "productid");
    "orderid"           SERIAL,
ALTER TABLE "orders" ADD CONSTRAINT "orders_pkey" PRIMARY KEY ("orderid");

Usable as a starting script, after editing by hand.

Our converter on the same files

From backend/, the Access reader and the mssql writer, run on all three files. I had no SQL Server to load the output into, so this is the generated script, not a load test. Northwind first, because it has the keys, AutoNumbers, required columns and pictures:

CREATE TABLE [Order_Details] (
  [OrderID] INT NOT NULL,
  [ProductID] INT NOT NULL,
  [UnitPrice] DECIMAL(19,4) NOT NULL,
  [Quantity] SMALLINT NOT NULL,
  [Discount] FLOAT NOT NULL,
  PRIMARY KEY ([OrderID], [ProductID])
);
...
CREATE TABLE [Shippers] (
  [ShipperID] INT IDENTITY(1,1) NOT NULL,
  [CompanyName] NVARCHAR(40) NOT NULL,
  [Phone] NVARCHAR(24),
  PRIMARY KEY ([ShipperID])
);
GO

SET IDENTITY_INSERT [Shippers] ON;
INSERT INTO [Shippers] ([ShipperID], [CompanyName], [Phone]) VALUES
(1, N'Speedy Express', N'(503) 555-9831'),
(2, N'United Package', N'(503) 555-3199'),
(3, N'Federal Shipping', N'(503) 555-9931');
SET IDENTITY_INSERT [Shippers] OFF;
GO

CREATE TABLE [Categories] (
  [CategoryID] INT IDENTITY(1,1) NOT NULL,
  [CategoryName] NVARCHAR(15) NOT NULL,
  [Description] NVARCHAR(MAX),
  [Picture] VARBINARY(MAX),
  PRIMARY KEY ([CategoryID])
);
GO

SET IDENTITY_INSERT [Categories] ON;
INSERT INTO [Categories] ([CategoryID], [CategoryName], [Description], [Picture]) VALUES
(1, N'Beverages', N'Soft drinks, coffees, teas, beers, and ales', 0x151C2F00020000000D000E0014002100FFFFFFFF4269746D617020496D616765...
...

Each AutoNumber is an IDENTITY column, and the rows go in under IDENTITY_INSERT so the existing IDs keep their values, which matters because Orders and Order Details refer to them. The composite key on Order Details survives. Required columns stay NOT NULL, including Products.Discontinued, a Yes/No that comes out as BIT NOT NULL with 0 and 1. Memo columns are NVARCHAR(MAX): 4 of the 9 Employees.Notes values are longer than 255 characters, the longest 448. Pictures are 0x literals in VARBINARY(MAX), with the OLE header that names "Bitmap Image" still in front of the bitmap.

The sample .accdb has a text primary key, and it carries over:

CREATE TABLE [Asset_Items] (
  [Asset_No] NVARCHAR(5) NOT NULL,
  [Asset_Category] NVARCHAR(20),
...
  [Acquired] DATETIME2,
  [Cost] DECIMAL(19,4),
  [Warranty] INT,
...
  [Comments] NVARCHAR(MAX),
  PRIMARY KEY ([Asset_No])
);

The date test file has no key in Access, so none in the output. It has the dates that mdbtools prints as 1900-01-00; the reader decodes those from the stored value and says so:

WARN ACCESS_DATE_DECODED DateTest: 8 date value(s) in DateCol, DateTimeCol, TimeCol are valid Access dates that mdbtools could not print (it printed '1900-01-00'). They were decoded from the stored value instead.

CREATE TABLE [DateTest] (
  [IDCol] INT,
  [DateTimeCol] DATETIME2,
  [DateCol] DATETIME2,
  [TimeCol] DATETIME2,
  [LongTextCol] NVARCHAR(MAX),
  [ShortTextCol] NVARCHAR(255)
);
GO

INSERT INTO [DateTest] ([IDCol], [DateTimeCol], [DateCol], [TimeCol], [LongTextCol], [ShortTextCol]) VALUES
(1, '2021-12-02 00:00:00', '2021-12-02 00:00:00', '2021-12-02 00:00:00', N'Hello', N'Hello'),
(2, '9999-01-01 00:00:00', '9999-01-01 00:00:00', '9999-01-01 00:00:00', N'Year 9999', N'Year 1999'),
(3, '1898-01-01 00:00:00', '1899-01-01 00:00:00', '1900-01-01 00:00:00', N'Pre-1900 years', N'Pre-1900 years'),
(4, '0632-01-01 00:00:00', '0633-01-01 00:00:00', '0634-01-01 00:00:00', N'Pre-1000 years', N'Pre-1000 years'),
...

All of those fit DATETIME2. Rows 2 to 4 would not fit the smalldatetime that mdb-schema sybase chose. The Access to SQL Server converter produces this script from an upload. The reader is the same one behind opening an ACCDB or MDB without Access and Access to PostgreSQL.

The converter also renamed Asset Items to [Asset_Items] and Asset No to [Asset_No]. If you link Access to that table, Access names it dbo_Asset_Items (Microsoft's example: dbo.Product becomes dbo_Product, per Import or link to data in an SQL Server database), and every form bound to [Asset No] needs its control source changed. SSMA's link step instead puts a linked table in place of each Access table so the existing forms keep working, which is the reason to use it when the forms are the point.

Translating Access queries to T-SQL

A typical Access query, written against Northwind:

SELECT Orders.OrderID,
       [FirstName] & " " & [LastName] AS Salesperson,
       IIf(IsNull(Orders.ShippedDate), "Open", "Shipped") AS Status,
       Nz(Orders.ShipRegion, "-") AS Region,
       Orders.Freight
FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE Orders.OrderDate >= #1/1/1997#
  AND Orders.ShipName Like "Q*"
ORDER BY Orders.OrderID;

sqlglot 30.17.0 has no Access dialect. Read as T-SQL, it stops at the date literal:

ParseError: Required keyword: 'expression' missing for <class 'sqlglot.expressions.core.GTE'>. Line 7, Col: 28.

With a regex that turns #1/1/1997# into '1997-01-01' first, it parses, and the result is worse than an error because it looks finished:

SELECT
  Orders.OrderID,
  [FirstName] & [ ] & [LastName] AS Salesperson,
  IIF(ISNULL(Orders.ShippedDate), [Open], [Shipped]) AS Status,
  NZ(Orders.ShipRegion, [-]) AS Region,
  Orders.Freight
FROM Orders
INNER JOIN Employees
  ON Orders.EmployeeID = Employees.EmployeeID
WHERE
  Orders.OrderDate >= '1997-01-01' AND Orders.ShipName LIKE [Q*]
ORDER BY
  Orders.OrderID

Every double-quoted string became a column name in brackets. & is still there, which in T-SQL is bitwise AND. ISNULL got one argument where T-SQL needs two (ISNULL), and NZ does not exist. The hand translation:

SELECT o.OrderID,
       CONCAT(e.FirstName, ' ', e.LastName) AS Salesperson,
       IIF(o.ShippedDate IS NULL, 'Open', 'Shipped') AS Status,
       ISNULL(o.ShipRegion, '-') AS Region,
       o.Freight
FROM dbo.Orders AS o
INNER JOIN dbo.Employees AS e ON o.EmployeeID = e.EmployeeID
WHERE o.OrderDate >= '1997-01-01'
  AND o.ShipName LIKE 'Q%'
ORDER BY o.OrderID;

sqlglot parses it as T-SQL; I had no SQL Server to execute it on. The reasoning, line by line:

Queries that stay in Access and run against linked tables keep their Access syntax. The cost is speed: when a query uses functions SQL Server lacks, "Jet pulls down tables locally", and the fix is a pass-through query, a view or a stored procedure (Link Access applications to SQL Server).

Can I migrate to SQL Server Express?

Yes. Microsoft suggests SQL Server Express on the desktop for this, and notes it "supports up to 10 GB" (Migrate an Access database to SQL Server). An Access file tops out at 2 GB (Import or link to data in an SQL Server database), so the data fits with room to grow.

Without SSMA, after loading the tables some other way: External Data > New Data Source > From Database > From SQL Server, choose to link, make or pick a DSN, and select the tables (Import or link to data in an SQL Server database). From the same page: Access links "only the first 255 columns", saving the password "stores an unencrypted password in the Access database", and "You can't add, delete, or modify columns or change data types in a linked table". Schema changes happen in SQL Server, then External Data > Linked Table Manager refreshes the links.

Sources