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).
The Microsoft route: SSMA, then link the tables
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.
- Install SSMA for Access, create a project, add the database files (Access to SQL Server migration guide).
- Create Report, and read its conversion statistics, errors and warnings.
- Check Tools > Project Settings > Type Mapping before converting (table below).
- Connect to SQL Server, Convert Schema, then Synchronize with Database to create the objects.
- Migrate Data.
- 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:
- Yes/No. Access stores "zero (0) for false, and -1 for true" (Data types for Access desktop databases). In T-SQL true is 1, and Microsoft suggests
WHERE Bitvalue <> 0so the query reads the same both ways (Comparing Access SQL with SQL Server TSQL). Search VBA for= -1and= True. - Date/Time.
datetimestarts at 1753-01-01 (datetime),smalldatetimeruns from 1900-01-01 to 2079-06-06 and rounds to the minute (smalldatetime),datetime2covers 0001 to 9999 (datetime2). Microsoft maps Access Date/Time Extended todatetime2(Migrate an Access database to SQL Server). - Hyperlink. After migration hyperlinks "lose their functionality and become simple nvarchar(max) columns", resolution "None" (Link Access applications to SQL Server). mdbtools reports Memo and Hyperlink as one type,
Memo/Hyperlink, so no tool built on it can tell them apart. - Decimal to
floatis SSMA's documented default. For anything that is money or a quantity, change it in Type Mapping before you convert.
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:
- Strings use single quotes, dates use quotes instead of
#, and*becomes%(?becomes_) (Comparing Access SQL with SQL Server TSQL). &treats a Null side as an empty string (& operator).CONCATdoes the same, as it "implicitly converts null values to empty strings" (CONCAT). Plain+would return NULL for an employee with no first name.IIfmaps toIIFandNztoISNULL(comparison page above; IIF).
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.
How do I link an Access database to SQL Server by hand?
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
- Migrate an Access database to SQL Server
- Access to SQL Server: Migration guide
- Link Access applications to SQL Server and Azure SQL Database
- Project Settings (Type Mapping) (AccessToSQL)
- Import or link to data in an SQL Server database
- Comparing Access SQL with SQL Server TSQL
- Data types for Access desktop databases
- datetime, smalldatetime, datetime2
- CONCAT, ISNULL, IIF
- sp_addextendedproperty
- Ampersand (&) operator, VBA reference
- mdb-schema manual (mdbtools)