Microsoft Access to SQL Migration: The Complete Guide (2026)

Why Migrate Away from Access?

Microsoft Access has been a staple of small business data management since the 1990s. It is easy to set up, requires no server, and lets non-developers build forms and reports quickly. But Access has hard limits that eventually force every growing team to migrate:

• 2 GB file size limit. An Access .accdb file cannot exceed 2 GB. Once you approach this ceiling, performance degrades sharply and the risk of file corruption increases. Production databases routinely exceed this within a few years.
• 255 concurrent user cap. Access technically supports 255 simultaneous connections, but performance becomes unreliable above 10-15 users. File locking issues and network latency make shared .accdb files painful for teams.
• File corruption. Because Access stores everything in a single file on a network share, an interrupted write, a dropped connection, or a power failure can corrupt the entire database. Recovery options are limited.
• No cloud support. Access databases cannot run natively in the cloud. There is no managed Access hosting, no auto-scaling, and no built-in backup strategy. Every modern deployment model assumes a server-based database.
• VBA complexity. Business logic embedded in VBA modules, macros, and form event handlers becomes increasingly difficult to maintain, test, and version control. There is no CI/CD pipeline for Access VBA.

If any of these problems sound familiar, it is time to migrate. The good news is that the migration process is well-understood, and several tools exist to automate the hardest parts.

Choosing Your Target Database

Before you start migrating, you need to decide where your data is going. The three most common targets for Access migrations are SQL Server, MySQL, and PostgreSQL. Each has trade-offs:

Factor	SQL Server	MySQL	PostgreSQL
Migration tool	SSMA (free, official)	Manual / third-party	Manual / third-party
Syntax similarity to Access	High	Medium	Medium
Licensing cost	Free (Express) to $$$	Free (open source)	Free (open source)
Cloud hosting	Azure SQL, AWS RDS	AWS RDS, PlanetScale	AWS RDS, Supabase, Neon
Max database size	524 PB	Unlimited (InnoDB)	Unlimited
Concurrent users	32,767	Thousands	Thousands
Best for	Microsoft shops	Web apps, startups	Advanced features, analytics

SQL Server is the easiest migration path. Microsoft built SSMA specifically for Access-to-SQL-Server migrations, and the SQL dialects are the most similar. If your organization already uses Microsoft tools, this is the default choice.

MySQL is the best option if you want open-source and broad hosting support. It is the most widely deployed database in the world, and almost every hosting provider supports it.

PostgreSQL is the strongest choice if you need advanced features like JSONB columns, array types, full-text search, or window functions. It is the fastest-growing database in 2026 and has the most active open-source community.

Method 1: Microsoft SSMA (SQL Server Migration Assistant)

SSMA is the most straightforward path for migrating Access to SQL Server. It is free, officially supported by Microsoft, and handles the majority of schema and data migration automatically.

Step-by-step process:

1. Download and install SSMA from the Microsoft Download Center. Choose the "SSMA for Access" version.
2. Create a new migration project in SSMA. Select your target SQL Server version (2019, 2022, or Azure SQL).
3. Add your Access database. Point SSMA to your .accdb or .mdb file. SSMA reads the full schema including tables, relationships, indexes, and queries.
4. Review the assessment report. SSMA generates a compatibility report showing which objects can be migrated automatically and which need manual intervention.
5. Convert the schema. SSMA translates Access table definitions into SQL Server CREATE TABLE statements, mapping data types (e.g., Access Number to SQL Server int, Access Date/Time to datetime2).
6. Migrate the data. SSMA copies all rows from Access tables to the new SQL Server tables. For large datasets, this can take minutes to hours depending on network speed.
7. Convert queries. SSMA attempts to convert saved Access queries into SQL Server views or stored procedures. This step has the highest failure rate because Access SQL and T-SQL differ significantly.

SSMA limitations:

• Does not convert Access forms or reports
• Does not convert VBA modules or macros
• Complex Access queries with IIF, domain aggregate functions (DSum, DCount, DLookup), or crosstab queries often fail conversion
• Only targets SQL Server (not MySQL or PostgreSQL)

Method 2: AI-Assisted Migration

The hardest part of any Access migration is converting queries. Access SQL has its own syntax that does not translate directly to standard SQL. This is where AI-powered tools save significant time.

AI2SQL can convert Access-style queries to any target database dialect. Instead of manually rewriting each query, you paste the Access SQL and describe what you need. AI2SQL generates the equivalent query for SQL Server, MySQL, or PostgreSQL with correct syntax.

For example, this Access query uses IIF and the ampersand concatenation operator:

-- Access SQL
SELECT
    CustomerName,
    FirstName & " " & LastName AS FullName,
    IIF(TotalOrders > 100, "VIP", "Standard") AS CustomerTier,
    Format(OrderDate, "yyyy-mm") AS OrderMonth
FROM Customers
WHERE OrderDate BETWEEN #01/01/2025# AND #12/31/2025#;

AI2SQL converts this to SQL Server T-SQL:

-- SQL Server (T-SQL)
SELECT
    CustomerName,
    FirstName + ' ' + LastName AS FullName,
    CASE WHEN TotalOrders > 100 THEN 'VIP' ELSE 'Standard' END AS CustomerTier,
    FORMAT(OrderDate, 'yyyy-MM') AS OrderMonth
FROM Customers
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-12-31';

Or to PostgreSQL:

-- PostgreSQL
SELECT
    CustomerName,
    FirstName || ' ' || LastName AS FullName,
    CASE WHEN TotalOrders > 100 THEN 'VIP' ELSE 'Standard' END AS CustomerTier,
    TO_CHAR(OrderDate, 'YYYY-MM') AS OrderMonth
FROM Customers
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-12-31';

AI2SQL handles the syntax differences automatically: IIF becomes CASE WHEN, the & operator becomes + (SQL Server) or || (PostgreSQL), Access date literals (#date#) become standard date strings, and the Format() function maps to the correct dialect-specific equivalent.

This approach works for any target database. If you are migrating to MySQL, AI2SQL generates MySQL-specific syntax instead. You can also connect your new database schema so that AI2SQL validates the converted queries against your actual tables and columns.

Method 3: Manual Migration

For small databases or migrations to MySQL/PostgreSQL where SSMA is not available, a manual approach works well:

Step 1: Export tables from Access

Use Access to export each table as a CSV file (External Data > Text File). Alternatively, export directly to ODBC if you have a driver configured for your target database.

Step 2: Recreate the schema

Write CREATE TABLE statements for your target database. Map Access data types manually:

Access Type	SQL Server	MySQL	PostgreSQL
AutoNumber	INT IDENTITY	INT AUTO_INCREMENT	SERIAL
Short Text	NVARCHAR(255)	VARCHAR(255)	VARCHAR(255)
Long Text (Memo)	NVARCHAR(MAX)	TEXT	TEXT
Number (Integer)	INT	INT	INTEGER
Number (Double)	FLOAT	DOUBLE	DOUBLE PRECISION
Currency	MONEY	DECIMAL(19,4)	NUMERIC(19,4)
Date/Time	DATETIME2	DATETIME	TIMESTAMP
Yes/No	BIT	TINYINT(1)	BOOLEAN
OLE Object	VARBINARY(MAX)	LONGBLOB	BYTEA

Step 3: Import data

Load the CSV files into your new database using the appropriate bulk import tool: BULK INSERT (SQL Server), LOAD DATA INFILE (MySQL), or COPY (PostgreSQL).

Step 4: Rewrite queries

Go through each saved Access query and rewrite it in your target dialect. This is the most time-consuming step, and where AI2SQL can save hours of manual work.

Converting Access Queries to SQL

Access SQL has several unique syntax patterns that do not exist in standard SQL. Here are the most common conversions you will need to make:

IIF to CASE WHEN

Access uses IIF(condition, true_value, false_value). Standard SQL uses CASE WHEN:

-- Access
SELECT IIF(Status = "Active", "Yes", "No") AS IsActive FROM Users;

-- Standard SQL (all databases)
SELECT CASE WHEN Status = 'Active' THEN 'Yes' ELSE 'No' END AS IsActive FROM Users;

String concatenation: & to CONCAT or ||

Access uses & for string concatenation. Standard SQL varies by dialect:

-- Access
SELECT FirstName & " " & LastName AS FullName FROM Employees;

-- SQL Server
SELECT FirstName + ' ' + LastName AS FullName FROM Employees;

-- MySQL
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

-- PostgreSQL
SELECT FirstName || ' ' || LastName AS FullName FROM Employees;

Date literals: #date# to 'date'

Access wraps date values in hash marks. Standard SQL uses quoted strings:

-- Access
SELECT * FROM Orders WHERE OrderDate > #03/15/2025#;

-- Standard SQL (all databases)
SELECT * FROM Orders WHERE OrderDate > '2025-03-15';

Domain aggregate functions: DSum, DCount, DLookup

Access has built-in domain aggregate functions that have no direct SQL equivalent. They must be rewritten as subqueries:

-- Access
SELECT
    CustomerID,
    DSum("Amount", "Orders", "CustomerID = " & CustomerID) AS TotalSpent,
    DCount("OrderID", "Orders", "CustomerID = " & CustomerID) AS OrderCount
FROM Customers;

-- Standard SQL
SELECT
    c.CustomerID,
    (SELECT SUM(o.Amount) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS TotalSpent,
    (SELECT COUNT(o.OrderID) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS OrderCount
FROM Customers c;

Asterisk wildcard: * to %

Access uses * as the LIKE wildcard. Standard SQL uses %:

-- Access
SELECT * FROM Products WHERE ProductName LIKE "SQL*";

-- Standard SQL
SELECT * FROM Products WHERE ProductName LIKE 'SQL%';

What SSMA Cannot Do (And How to Handle It)

SSMA migrates tables, data, and basic queries. But most Access databases are not just tables and queries. They include a full application layer that SSMA ignores entirely:

Forms

Access forms are tightly coupled to the database. There is no automated way to convert them. Your options are Power Apps (for simple forms), a web application (React, Next.js, or similar), or a desktop application (.NET WinForms or WPF). For most teams in 2026, rebuilding as a web app is the right move.

Reports

Access reports can be replaced with SQL Server Reporting Services (SSRS), Power BI, or any reporting tool that connects to your new database. The report layouts need to be rebuilt, but the underlying queries transfer once converted.

VBA modules and macros

VBA business logic must be rewritten. If the logic is simple (data validation, calculations), it can often move into stored procedures or application-layer code. Complex VBA that interacts with forms, manipulates UI elements, or calls external APIs needs to be redesigned from scratch.

Complex queries

SSMA fails on crosstab queries (TRANSFORM/PIVOT), queries with multiple levels of IIF nesting, queries using custom VBA functions, and parameter queries that reference form controls. These all need manual conversion. AI2SQL can handle the SQL translation part, letting you focus on the logic rather than syntax differences.

Post-Migration Checklist

After migrating your schema and data, work through this checklist before going live:

1. Test every converted query. Run each query against the new database and compare results to the original Access output. Pay special attention to date calculations, string operations, and aggregations where syntax differences cause subtle bugs.
2. Validate data integrity. Compare row counts for every table. Check that primary keys, foreign keys, and unique constraints are enforced. Verify that NULL handling matches the original behavior (Access treats empty strings and NULLs differently than most SQL databases).
3. Update connection strings. Every application, script, or report that connected to the .accdb file needs a new connection string pointing to your SQL database. This includes ODBC connections, VBA code, linked tables in other Access files, and external tools.
4. Set up proper backups. Unlike Access (where backup means copying a file), SQL databases need scheduled backup jobs. Configure automated backups with point-in-time recovery. Cloud databases like Azure SQL and AWS RDS handle this automatically.
5. Configure user permissions. Access had minimal security. Your new database should have proper roles: read-only users for reporting, read-write users for the application, and admin accounts for schema changes. Never use the sa or root account for application connections.
6. Monitor performance. Run EXPLAIN or EXPLAIN ANALYZE on your most common queries. Add indexes where needed. SQL Server, MySQL, and PostgreSQL all have query profiling tools that Access lacked entirely.
7. Train your team. If your team used Access directly, they need training on the new system. Show them how to run queries, access reports, and use the new front-end application. Consider setting up AI2SQL so non-technical team members can still query the database using plain English.

Frequently Asked Questions

Can I migrate an Access database to SQL Server for free?

Yes. Microsoft provides SQL Server Migration Assistant (SSMA) as a free download. It handles table schema, data, and basic query migration from Access to SQL Server. For MySQL or PostgreSQL targets, you will need third-party tools or a manual approach. AI2SQL can help convert Access-specific queries to any SQL dialect for free with its demo mode.

Does SSMA convert Access forms and reports to SQL Server?

No. SSMA only migrates tables, data, and queries. Access forms, reports, macros, and VBA modules are not converted. You will need to rebuild the front-end using a different technology such as Power Apps, a web application, or a desktop framework like .NET WinForms.

How long does an Access to SQL migration take?

A small database (under 50 tables) with simple queries can be migrated in 1-2 days using SSMA. Larger databases with complex VBA, forms, and reports typically take 2-8 weeks depending on the amount of business logic embedded in Access. The data migration itself is fast. The time-consuming part is rewriting queries, rebuilding forms, and testing.

Should I migrate Access to SQL Server, MySQL, or PostgreSQL?

SQL Server is the easiest migration path because Microsoft provides SSMA and the syntax is most similar to Access SQL. Choose MySQL if you want an open-source database with broad hosting support. Choose PostgreSQL if you need advanced features like JSONB, arrays, window functions, and full ACID compliance. All three are massive upgrades over Access for multi-user, production workloads.