How to Convert Microsoft Access Queries to SQL with AI

Why Access Queries Need Conversion

Microsoft Access has been the go-to desktop database for small businesses and departments since the 1990s. But Access uses its own SQL dialect called Jet SQL (now ACE SQL) that deviates from the ANSI SQL standard in dozens of ways. When you outgrow Access and migrate to MySQL, PostgreSQL, or SQL Server, your queries will not run as-is.

The problems go beyond simple syntax. Access uses proprietary functions like IIF(), Nz(), DLookup(), and Format() that have no direct equivalent in standard SQL. It wraps dates in # symbols instead of single quotes. It uses * and ? as wildcards instead of % and _. And its boolean values are True/False instead of 1/0.

Microsoft provides SQL Server Migration Assistant (SSMA) for moving Access databases to SQL Server, but SSMA has well-documented limitations. It wraps many Access-specific functions in compatibility shims rather than converting them to native T-SQL syntax, resulting in slower queries that are harder to maintain. Complex Access queries with multiple domain functions, crosstab operations, or VBA-dependent logic often require manual intervention after SSMA migration.

AI-powered conversion tools solve this problem by understanding both the source (Access SQL) and target (MySQL, PostgreSQL, SQL Server) dialects and generating clean, native SQL for your target database.

Access SQL vs Standard SQL: Key Differences

Before converting anything, you need to understand what is different. Here is a comprehensive comparison of Access SQL syntax versus standard SQL:

Feature	Access SQL	Standard SQL (MySQL/PostgreSQL/SQL Server)
Conditional logic	IIF(condition, true, false)	CASE WHEN condition THEN true ELSE false END
String concatenation	& operator	CONCAT() or ||
Date literals	#2026-03-16#	'2026-03-16'
Wildcard (any chars)	* in LIKE	% in LIKE
Wildcard (single char)	? in LIKE	_ in LIKE
Boolean values	True / False	1 / 0 (or TRUE/FALSE in PostgreSQL)
NULL replacement	Nz(field, default)	COALESCE(field, default)
Domain lookups	DLookup("field", "table", "criteria")	SELECT field FROM table WHERE criteria (subquery)
Domain aggregates	DSum("field", "table", "criteria")	SELECT SUM(field) FROM table WHERE criteria
Date formatting	Format(date, "yyyy-mm-dd")	DATE_FORMAT() (MySQL) / TO_CHAR() (PostgreSQL)
Type casting	CStr(), CInt(), CDbl()	CAST(value AS type)
Table/field quoting	[Table Name].[Field Name]	`table_name` (MySQL) / "table_name" (PostgreSQL)
Top N rows	SELECT TOP 10	LIMIT 10 (MySQL/PostgreSQL) / TOP 10 (SQL Server)

Every one of these differences will cause a syntax error if you copy an Access query directly into another database. The more complex your queries, the more conversions are needed per query.

Method 1: AI-Powered Conversion

The fastest way to convert Access queries to standard SQL is with an AI-powered tool. AI2SQL understands Access SQL syntax and can rewrite queries for MySQL, PostgreSQL, SQL Server, or any other dialect.

Step 1: Copy Your Access Query

Open your Access database, go to the query designer, switch to SQL View, and copy the raw SQL. Here is a typical Access query:

SELECT
    [Customers].[CustomerName],
    [Orders].[OrderDate],
    IIF([Orders].[Total] > 1000, "Premium", "Standard") AS CustomerTier,
    Nz([Orders].[Discount], 0) AS Discount,
    [Customers].[FirstName] & " " & [Customers].[LastName] AS FullName
FROM Customers
INNER JOIN Orders ON [Customers].[ID] = [Orders].[CustomerID]
WHERE [Orders].[OrderDate] BETWEEN #2025-01-01# AND #2025-12-31#
    AND [Orders].[Status] = True
    AND [Customers].[Region] LIKE "North*"
ORDER BY [Orders].[Total] DESC;

Step 2: Paste into AI2SQL and Select Target Dialect

Go to AI2SQL and paste the Access query. Tell it to convert to your target database. You can type something like "Convert this Access query to MySQL" or "Rewrite this for PostgreSQL."

Step 3: Review the Converted Output

AI2SQL rewrites every Access-specific element. Here is the same query converted to MySQL:

SELECT
    c.CustomerName,
    o.OrderDate,
    CASE WHEN o.Total > 1000 THEN 'Premium' ELSE 'Standard' END AS CustomerTier,
    COALESCE(o.Discount, 0) AS Discount,
    CONCAT(c.FirstName, ' ', c.LastName) AS FullName
FROM Customers c
INNER JOIN Orders o ON c.ID = o.CustomerID
WHERE o.OrderDate BETWEEN '2025-01-01' AND '2025-12-31'
    AND o.Status = 1
    AND c.Region LIKE 'North%'
ORDER BY o.Total DESC;

Notice what changed: IIF() became CASE WHEN, Nz() became COALESCE(), & became CONCAT(), #date# became 'date', True became 1, * became %, double quotes became single quotes, and bracket notation was replaced with standard aliases.

Step 4: Test and Iterate

Run the converted query against your target database. If you get errors (usually from edge cases like Access-specific date functions), paste the error back into AI2SQL and it will fix the query.

Method 2: Manual Conversion

If you prefer a find-and-replace approach, here is a systematic process for manually converting Access SQL:

1. Replace date delimiters: Find all # around dates and replace with ' single quotes.
2. Replace wildcards: Change * to % and ? to _ inside LIKE patterns.
3. Replace IIF(): Rewrite every IIF(cond, true, false) to CASE WHEN cond THEN true ELSE false END. Watch for nested IIF calls.
4. Replace Nz(): Change Nz(field, default) to COALESCE(field, default).
5. Replace string concatenation: Change & to CONCAT() or || depending on your target database.
6. Replace booleans: Change True to 1 and False to 0.
7. Replace bracket notation: Remove [ and ] or replace with backticks (MySQL) or double quotes (PostgreSQL).
8. Replace double quotes in strings: Change "string" to 'string' for string literals.
9. Fix TOP N: Move TOP N to LIMIT N at the end (for MySQL/PostgreSQL).
10. Convert functions: Replace Format(), CStr(), CInt(), DateSerial() with their target equivalents.

This approach works for simple queries but becomes tedious and error-prone for complex queries with nested functions, multiple domain lookups, or VBA logic.

Converting VBA to SQL

Many Access databases rely heavily on VBA code that contains embedded SQL or uses domain functions. Converting VBA to standard SQL requires a fundamentally different approach: replacing procedural, row-by-row logic with set-based SQL operations.

DLookup to Subquery

Access VBA:

Dim managerName As String
managerName = DLookup("FullName", "Employees", "EmployeeID = " & Me.ManagerID)

Standard SQL:

SELECT FullName
FROM Employees
WHERE EmployeeID = @ManagerID;

Or use it inline as a correlated subquery:

SELECT
    e.FullName AS EmployeeName,
    (SELECT m.FullName FROM Employees m WHERE m.EmployeeID = e.ManagerID) AS ManagerName
FROM Employees e;

DSum/DCount to Aggregate Queries

Access VBA:

Dim totalSales As Double
totalSales = DSum("Amount", "Sales", "SalesDate >= #2025-01-01# AND Region = 'West'")

Standard SQL:

SELECT SUM(Amount) AS totalSales
FROM Sales
WHERE SalesDate >= '2025-01-01'
    AND Region = 'West';

Recordset Loops to Set-Based Operations

One of the biggest performance improvements when leaving Access is replacing VBA recordset loops with set-based SQL. Access developers often write loops like this:

Access VBA (row-by-row update):

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Products WHERE CategoryID = 5")
Do While Not rs.EOF
    rs.Edit
    rs!Price = rs!Price * 1.10
    rs.Update
    rs.MoveNext
Loop
rs.Close

Standard SQL (single statement):

UPDATE Products
SET Price = Price * 1.10
WHERE CategoryID = 5;

The SQL version is not only simpler but executes orders of magnitude faster on large datasets because the database engine optimizes the entire operation at once.

5 Real Conversion Examples

Here are five real-world Access queries converted to MySQL, PostgreSQL, and SQL Server equivalents.

Example 1: Conditional Formatting with Nested IIF

Access:

SELECT
    [Product Name],
    [Units In Stock],
    IIF([Units In Stock] = 0, "Out of Stock",
        IIF([Units In Stock] < 10, "Low Stock", "In Stock")) AS StockStatus
FROM Products;

MySQL / PostgreSQL / SQL Server:

SELECT
    ProductName,
    UnitsInStock,
    CASE
        WHEN UnitsInStock = 0 THEN 'Out of Stock'
        WHEN UnitsInStock < 10 THEN 'Low Stock'
        ELSE 'In Stock'
    END AS StockStatus
FROM Products;

Example 2: Date Filtering with Access Functions

Access:

SELECT [Order ID], [Order Date], [Ship Date]
FROM Orders
WHERE [Order Date] >= DateSerial(Year(Date()), 1, 1)
    AND [Order Date] < DateSerial(Year(Date()) + 1, 1, 1);

MySQL:

SELECT OrderID, OrderDate, ShipDate
FROM Orders
WHERE OrderDate >= MAKEDATE(YEAR(CURDATE()), 1)
    AND OrderDate < MAKEDATE(YEAR(CURDATE()) + 1, 1);

PostgreSQL:

SELECT OrderID, OrderDate, ShipDate
FROM Orders
WHERE OrderDate >= DATE_TRUNC('year', CURRENT_DATE)
    AND OrderDate < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year';

SQL Server:

SELECT OrderID, OrderDate, ShipDate
FROM Orders
WHERE OrderDate >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1)
    AND OrderDate < DATEFROMPARTS(YEAR(GETDATE()) + 1, 1, 1);

Example 3: String Building with Null Handling

Access:

SELECT
    [First Name] & " " & Nz([Middle Name] & " ", "") & [Last Name] AS FullName,
    Nz([Phone], "No phone on file") AS ContactPhone
FROM Contacts
WHERE [Last Name] LIKE "Sm*";

MySQL:

SELECT
    CONCAT(FirstName, ' ', COALESCE(CONCAT(MiddleName, ' '), ''), LastName) AS FullName,
    COALESCE(Phone, 'No phone on file') AS ContactPhone
FROM Contacts
WHERE LastName LIKE 'Sm%';

PostgreSQL:

SELECT
    FirstName || ' ' || COALESCE(MiddleName || ' ', '') || LastName AS FullName,
    COALESCE(Phone, 'No phone on file') AS ContactPhone
FROM Contacts
WHERE LastName LIKE 'Sm%';

Example 4: Domain Function in a SELECT

Access:

SELECT
    [Category Name],
    DCount("ProductID", "Products", "CategoryID = [Categories].[CategoryID]") AS ProductCount,
    DSum("UnitPrice * UnitsInStock", "Products", "CategoryID = [Categories].[CategoryID]") AS InventoryValue
FROM Categories;

Standard SQL (all dialects):

SELECT
    c.CategoryName,
    COUNT(p.ProductID) AS ProductCount,
    SUM(p.UnitPrice * p.UnitsInStock) AS InventoryValue
FROM Categories c
LEFT JOIN Products p ON c.CategoryID = p.CategoryID
GROUP BY c.CategoryName;

Example 5: Type Casting and Formatting

Access:

SELECT
    [Order ID],
    "ORD-" & CStr([Order ID]) AS OrderCode,
    Format([Order Date], "mmmm yyyy") AS OrderMonth,
    CCur([Subtotal]) AS SubtotalCurrency
FROM Orders
WHERE CInt(Format([Order Date], "yyyy")) = 2025;

MySQL:

SELECT
    OrderID,
    CONCAT('ORD-', CAST(OrderID AS CHAR)) AS OrderCode,
    DATE_FORMAT(OrderDate, '%M %Y') AS OrderMonth,
    CAST(Subtotal AS DECIMAL(10,2)) AS SubtotalCurrency
FROM Orders
WHERE YEAR(OrderDate) = 2025;

PostgreSQL:

SELECT
    OrderID,
    'ORD-' || CAST(OrderID AS TEXT) AS OrderCode,
    TO_CHAR(OrderDate, 'Month YYYY') AS OrderMonth,
    CAST(Subtotal AS NUMERIC(10,2)) AS SubtotalCurrency
FROM Orders
WHERE EXTRACT(YEAR FROM OrderDate) = 2025;

Handling Complex Access Queries

Access has several query types that do not have a one-to-one equivalent in standard SQL. Here is how to handle the most common ones.

Crosstab Queries to PIVOT

Access Crosstab:

TRANSFORM Sum([Order Details].[Quantity]) AS SumOfQuantity
SELECT [Products].[ProductName]
FROM Products INNER JOIN [Order Details]
    ON [Products].[ProductID] = [Order Details].[ProductID]
GROUP BY [Products].[ProductName]
PIVOT Format([Orders].[OrderDate], "yyyy");

SQL Server (PIVOT):

SELECT ProductName, [2024], [2025], [2026]
FROM (
    SELECT p.ProductName, YEAR(o.OrderDate) AS OrderYear, od.Quantity
    FROM Products p
    INNER JOIN OrderDetails od ON p.ProductID = od.ProductID
    INNER JOIN Orders o ON od.OrderID = o.OrderID
) src
PIVOT (
    SUM(Quantity) FOR OrderYear IN ([2024], [2025], [2026])
) pvt;

MySQL/PostgreSQL (CASE WHEN):

SELECT
    p.ProductName,
    SUM(CASE WHEN YEAR(o.OrderDate) = 2024 THEN od.Quantity ELSE 0 END) AS "2024",
    SUM(CASE WHEN YEAR(o.OrderDate) = 2025 THEN od.Quantity ELSE 0 END) AS "2025",
    SUM(CASE WHEN YEAR(o.OrderDate) = 2026 THEN od.Quantity ELSE 0 END) AS "2026"
FROM Products p
INNER JOIN OrderDetails od ON p.ProductID = od.ProductID
INNER JOIN Orders o ON od.OrderID = o.OrderID
GROUP BY p.ProductName;

Make-Table Queries to CREATE TABLE AS

Access:

SELECT * INTO ArchivedOrders
FROM Orders
WHERE OrderDate < #2024-01-01#;

MySQL:

CREATE TABLE ArchivedOrders AS
SELECT * FROM Orders
WHERE OrderDate < '2024-01-01';

PostgreSQL:

CREATE TABLE ArchivedOrders AS
SELECT * FROM Orders
WHERE OrderDate < '2024-01-01';

SQL Server:

SELECT * INTO ArchivedOrders
FROM Orders
WHERE OrderDate < '2024-01-01';

Append Queries to INSERT INTO SELECT

Access:

INSERT INTO ArchivedOrders
SELECT * FROM Orders
WHERE [Order Date] < #2024-01-01# AND [Status] = True;

Standard SQL:

INSERT INTO ArchivedOrders
SELECT * FROM Orders
WHERE OrderDate < '2024-01-01' AND Status = 1;

Append queries are the simplest to convert. You mainly need to fix the date delimiters, boolean values, and bracket notation.

Frequently Asked Questions

Can I convert Access queries to MySQL automatically?

Yes. AI-powered tools like AI2SQL can convert Access SQL syntax to MySQL automatically. Paste your Access query, select MySQL as the target dialect, and the tool rewrites IIF() to CASE WHEN, replaces # date delimiters with single quotes, converts wildcards (* to % and ? to _), and handles all other syntax differences. For bulk conversions, you can process queries one at a time and verify each output.

How do I convert Access VBA code that uses DLookup and DSum to SQL?

Access domain functions like DLookup, DSum, and DCount translate to standard SQL subqueries and aggregate functions. DLookup becomes a scalar subquery with SELECT ... WHERE. DSum becomes SELECT SUM() ... WHERE. DCount becomes SELECT COUNT() ... WHERE. VBA recordset loops (Do While Not .EOF) should be replaced with set-based SQL operations like UPDATE ... JOIN or INSERT INTO ... SELECT, which are both faster and more portable.

What are the biggest differences between Access SQL and standard SQL?

The main differences are: IIF() instead of CASE WHEN, & for string concatenation instead of CONCAT(), # date delimiters instead of single quotes, * and ? wildcards instead of % and _, True/False instead of 1/0 booleans, Nz() instead of COALESCE(), bracket notation [table].[field] instead of standard quoting, and proprietary functions like DLookup, DSum, Format(), CStr(), and CInt() that have no direct equivalents in standard SQL.

Does Microsoft SSMA handle all Access-to-SQL-Server conversions?

Microsoft SQL Server Migration Assistant (SSMA) handles table and data migration well, but it struggles with complex queries, VBA modules, and Access-specific functions. SSMA wraps many Access functions in compatibility shims rather than converting them to native T-SQL, resulting in slower queries. For best results, use SSMA for the initial table migration, then manually convert or use an AI tool to rewrite queries and VBA logic into clean, native SQL.