Your Access database has served the business for years — maybe decades. It started as a simple tracking tool and grew into something that runs a department. Now it is hitting walls: too many concurrent users, approaching the 2 GB limit, no real security model, and your IT team is asking hard questions about backup and disaster recovery. Migrating to SQL Server is the standard answer, and this guide walks through the entire process — from planning to cutover — so you know what to expect and where the gotchas are.
Access is a remarkable tool for what it was designed to do — small-team database applications with built-in forms and reports. But it was never designed to be a production database server. Here are the specific limitations that drive migration:
There are two fundamental approaches, and choosing the right one upfront saves you from rework later.
Move the tables to SQL Server. Keep the Access forms, reports, queries, and VBA code. Access connects to SQL Server tables via ODBC linked tables. This is the most common approach because it preserves the existing user interface while giving you a real database backend.
Advantages:
Disadvantages:
Move the data to SQL Server and rebuild the front-end using a different technology — Power Apps, a web application, or a desktop application built in .NET or another framework. This eliminates Access from the stack entirely.
Advantages:
Disadvantages:
For most organizations, Approach 1 is the right first step. Get the data into SQL Server now, stabilize operations, and plan the front-end replacement as a separate project.
LegacyLeaps scans your Access database and generates a detailed migration assessment — table structures, VBA dependencies, linked tables, query complexity, and estimated effort. No data leaves your machine.
Run a Free ScanBefore you touch SSMA or any migration tool, you need to understand what you are working with. Skip this step and you will hit surprises during migration that could have been planned for.
Microsoft's SQL Server Migration Assistant (SSMA) for Access is a free tool that automates the heaviest parts of migration: schema conversion and data transfer. Here is how to use it.
SSMA handles most data type conversions automatically, but several Access data types do not have clean SQL Server equivalents. Understanding these upfront prevents data integrity issues.
| Access Type | SSMA Default Mapping | Potential Issue |
|---|---|---|
| AutoNumber (Long Integer) | INT IDENTITY(1,1) | Works well. But if your Access database has gaps in AutoNumber sequences (from deleted records), verify the IDENTITY seed is set correctly. |
| Yes/No | BIT | Access stores Yes as -1 and No as 0. SQL Server BIT uses 1 and 0. VBA code that checks If field = True works, but code that checks If field = -1 breaks. |
| Date/Time | DATETIME | Access allows dates without times and times without dates. In SQL Server DATETIME, a date without a time defaults to midnight, and a time without a date defaults to 1900-01-01. Consider using DATETIME2 for better precision. |
| Memo (Long Text) | NVARCHAR(MAX) | Works, but Access Memo fields sometimes contain embedded formatting or OLE data that looks like garbage text in SQL Server. |
| Hyperlink | NVARCHAR(MAX) | Access stores hyperlinks in a special format: Display Text#URL#Subaddress#. After migration, the data is a plain string with hash delimiters. Forms that displayed clickable links need code updates. |
| OLE Object | VARBINARY(MAX) | Access OLE Objects contain embedded documents (Word docs, images) in a proprietary wrapper. The binary data migrates, but extracting the actual document from the OLE wrapper requires extra processing. |
| Attachment (accdb only) | Separate table | Access Attachment fields store multiple files per record. There is no SQL Server equivalent. SSMA creates a separate child table to hold attachment data. |
| Number (Decimal) | DECIMAL(18,x) | Verify the precision and scale match your actual data. Access's Decimal type supports up to 28 digits; make sure SSMA maps the scale correctly. |
This is where the real work happens. Access queries use Jet SQL, which has significant differences from T-SQL (SQL Server's dialect). SSMA converts many queries automatically, but complex queries — especially crosstab queries, queries with domain aggregate functions, and action queries — often need manual work.
| Jet SQL Function | T-SQL Equivalent |
|---|---|
IIf(condition, true, false) | CASE WHEN condition THEN true ELSE false END |
Nz(value, default) | ISNULL(value, default) or COALESCE(value, default) |
DLookup("field", "table", "criteria") | Subquery: (SELECT field FROM table WHERE criteria) |
DSum("field", "table", "criteria") | Subquery: (SELECT SUM(field) FROM table WHERE criteria) |
DCount("field", "table", "criteria") | Subquery: (SELECT COUNT(field) FROM table WHERE criteria) |
Format(date, "yyyy-mm") | FORMAT(date, 'yyyy-MM') (SQL Server 2012+) or CONVERT |
Mid(string, start, length) | SUBSTRING(string, start, length) |
Left(string, length) | LEFT(string, length) (same name, works) |
Date() | GETDATE() or CAST(GETDATE() AS DATE) |
Now() | GETDATE() |
True / False | 1 / 0 |
Access TRANSFORM/PIVOT queries are one of the hardest things to convert because SQL Server's PIVOT syntax is fundamentally different. Access crosstab queries dynamically generate column headers from data values. SQL Server's PIVOT requires you to specify column values explicitly, which means dynamic crosstab queries require dynamic SQL.
-- Access crosstab:
TRANSFORM Sum(Sales.Amount)
SELECT Sales.Product
FROM Sales
GROUP BY Sales.Product
PIVOT Sales.Region;
-- SQL Server equivalent (static columns):
SELECT Product, [East], [West], [North], [South]
FROM (SELECT Product, Region, Amount FROM Sales) AS src
PIVOT (SUM(Amount) FOR Region IN ([East], [West], [North], [South])) AS pvt;If the pivot values are dynamic (e.g., the regions change over time), you need dynamic SQL that builds the PIVOT statement at runtime — a significantly more complex solution than the Access version.
Access action queries generally translate well to T-SQL INSERT INTO, UPDATE, and DELETE statements. The main issues are:
SELECT INTO in T-SQL, but the table must not already existUPDATE table1 INNER JOIN table2... while T-SQL uses UPDATE t1 SET... FROM table1 t1 INNER JOIN table2 t2...If you are keeping Access as the front-end (Approach 1), your VBA code needs changes wherever it interacts with data. The most common changes:
Many Access applications use DAO (Data Access Objects) to run queries and manipulate records. DAO talks directly to the Jet/ACE engine. When your tables are on SQL Server, DAO still works through the ODBC linked tables, but performance suffers because every operation is translated through the ODBC layer.
For better performance, convert data-heavy operations to use either:
DLookup, DSum, DCount, DMax, DMin, and DAvg are heavily used in Access VBA code. They work through ODBC linked tables but are extremely slow because each call generates a separate query to SQL Server. If you have a form that calls DLookup in 15 different controls' AfterUpdate events, that is 15 separate round trips to SQL Server every time a user updates a record.
Replace domain aggregate calls with a single query that fetches all needed values at once, or use pass-through queries.
LegacyLeaps maps every VBA dependency, linked table, and query compatibility issue in your database. Get a detailed migration plan before you write a single line of SQL.
Download LegacyLeaps FreeIf you are doing a full rewrite (Approach 2) and want to eliminate Access entirely, you need to replace the forms and reports. Here are the realistic options:
Microsoft's low-code platform. It can connect directly to SQL Server (on-premises via a data gateway, or Azure SQL directly). Good for simple data entry forms. Struggles with complex forms that have many subforms, calculated fields, or heavy VBA logic.
Best for: Simple CRUD applications with fewer than 20 fields per form.
Limitations: No equivalent to Access subreports, limited formatting control, requires Microsoft 365 licensing per user.
Build a web front-end using ASP.NET, Node.js, Django, or any web framework. Connects to SQL Server natively. Offers full control over the user interface and can be accessed from any device with a browser.
Best for: Complex applications where Access forms have significant business logic, or where you need web/mobile access.
Limitations: Requires developers. Most expensive option. Takes weeks to months.
If you primarily need to replace Access reports, Power BI connects to SQL Server and can reproduce most Access report layouts. It adds interactive filtering, drill-down, and scheduled refresh that Access reports cannot match.
Best for: Organizations that already use Power BI and want to consolidate reporting.
Limitations: Does not handle data entry. Cannot replace Access forms.
This is the pragmatic choice. It is not glamorous, but it works. Your users keep the forms they know, you get SQL Server's reliability and security for the data, and you buy time to plan a proper front-end replacement on a timeline that makes sense for your organization.
Do not underestimate testing. A migration that passes a quick sanity check can still have subtle issues that only appear under real-world usage.
The cutover is when you switch from the old Access database to the new SQL Server-backed version. Plan this carefully to avoid data loss or downtime.
Yes. Microsoft's SQL Server Migration Assistant (SSMA) for Access is a free tool that handles schema conversion, data migration, and basic query translation. SQL Server Express is also free for databases up to 10 GB. The main cost is the labor to fix queries, VBA code, and forms that do not convert automatically.
Yes, with modifications. The standard approach is to keep Access as the front-end (forms, reports, VBA) while moving the tables to SQL Server. Access links to the SQL Server tables via ODBC. Most forms work immediately, but any VBA code that uses Jet-specific SQL syntax (IIf in queries, DLookup, domain aggregate functions) or relies on Jet behaviors like implicit type conversion will need updating.
For a simple database with 10-20 tables and no VBA, the data migration itself takes minutes. The total project time depends on complexity: a database with no custom code might take 1-2 days including testing. A database with extensive VBA, dozens of queries, and custom forms typically takes 2-6 weeks. The biggest time sink is usually query conversion and VBA code updates, not the data migration.
If your Access front-end users are all on the same local network, SQL Server on-premises or SQL Server Express is simpler and avoids ongoing cloud costs. If users are distributed or you want to eliminate server maintenance, Azure SQL Database is the better choice. The migration process is nearly identical for both — SSMA supports Azure SQL as a target. Azure SQL has some T-SQL limitations compared to on-premises SQL Server, so check compatibility if you have complex stored procedures.
Practical fixes for legacy Excel and Access problems. No spam.