How to Convert Access to SQL Server

Converting Access to SQL Server means moving your tables and data to SQL Server and pointing the Access front-end at the server with linked tables. You keep your forms, reports, and VBA; only the back-end changes. This guide gives you exact steps, the problems you will hit (and how to fix them), correct connection strings, and what to change in queries and code so the migration actually works. If you have not split your database yet, multi-user Access database design is the first step.

Before You Start: What You Need

You need: (1) a split Access app—one .accdb with only tables (the back-end) and one .accdb with forms, reports, queries, and VBA (the front-end); (2) SQL Server installed and reachable from the PC where you run Access; (3) a login that can create tables and insert data in a database (your DBA can create the empty database and grant db_owner or equivalent). Install the ODBC driver: on the PC running Access, check for “ODBC Driver 17 for SQL Server” or “SQL Server” in Windows ODBC Data Source Administrator. If missing, download “Microsoft ODBC Driver for SQL Server” from Microsoft. Use the same bitness as Access (32-bit Access needs 32-bit ODBC).

Step-by-Step: Run the Upsizing Wizard and Move Data

Work on a copy of the back-end .accdb so the original stays intact.

1. Open the back-end copy in Access: File → Open, select the back-end .accdb. All objects in the Navigation Pane should be tables only.
2. Start the wizard: Database Tools tab → Move Data group → SQL Server. (In some versions the button is “Upsize” or “SQL Server” only.) If you do not see it, you may be in 64-bit Access—the wizard is 32-bit only; use a 32-bit Access install or migrate manually with SSMS and linked tables.
3. Choose server and database: Select “Use existing database” and pick your SQL Server instance (e.g. SERVERNAME or SERVERNAME\SQLEXPRESS) and the target database. Test the connection. If it fails, check: SQL Server Browser running (for named instances), firewall allows port 1433, and the login has rights to the database.
4. Select tables: Select all tables to export (or the ones you want in this pass). Click Next.
5. Wizard options: Leave “Create table with the following attributes” checked (primary keys, indexes, default values). Add “Table attributes” if you want. Do not choose “Create an ADP” unless you plan to use ADP; choose to upsize the tables only. Finish the wizard and let it run.
6. Verify in SQL Server: In SQL Server Management Studio, open the database. Check that every table is there, row counts match the Access back-end (right-click table → Properties → Storage, or SELECT COUNT(*) FROM table), and primary keys exist. Fix any missing primary key: in SSMS, open the table in Design, set the key column, save.

Data Types the Wizard Gets Wrong (And How to Fix Them)

The wizard usually maps: AutoNumber → int IDENTITY(1,1); Text(50) → nvarchar(50); Number Long Integer → int; DateTime → datetime; Yes/No → bit; Currency → money; Long Text → nvarchar(max). Problems that break your app:

• Yes/No (bit) and checkboxes: Access uses -1 for True and 0 for False; SQL Server bit uses 1 and 0. Bound checkboxes can show wrong state. Fix: In SSMS, add a view that selects the table and converts: CASE WHEN bitcol = 1 THEN 1 WHEN bitcol = 0 THEN 0 ELSE NULL END, or ensure default 0 and update any -1 to 1 in the data. Then link Access to the view for read-only, or keep linking to the table and test every form—often Access still displays correctly.
• AutoNumber not reseeding: After importing, new rows should get the next ID. If the wizard did not set IDENTITY, in SSMS run: ALTER TABLE YourTable ADD ID int IDENTITY(1,1) (only if the column is missing). If the column exists but is not IDENTITY, you must recreate the column or table—so catch this before loading data.
• Table or column names that are SQL reserved words: Names like Order, Date, User, Group cause “invalid column name” or syntax errors in pass-through queries. In SQL Server, rename the object (e.g. Order → OrderTable) or always use square brackets in T-SQL: [Order]. In Access linked tables, the link name can stay “Order” while the server table is [dbo].[OrderTable]; update any pass-through queries and VBA that reference the table.
• Text truncated: Access Short Text defaults to 255; the wizard may create nvarchar(255). If you had Allow Zero Length and some empty strings, they should still be valid. If you need longer, alter the column in SSMS: ALTER TABLE t ALTER COLUMN c nvarchar(500); then re-run any failed import for that table if needed.

Relink the Front-End to SQL Server (Exact Steps)

In the front-end .accdb, you replace the link to each Access table with a link to the SQL Server table. Do not delete the table data—you are only removing the link.

1. Delete existing links: In the Navigation Pane, linked tables show a small arrow icon. Right-click each linked table → Delete. Choose “Yes” when asked to remove the link (you are not deleting the data on the server). Repeat for every table that you moved to SQL Server.
2. Create new links: External Data tab → Import & Link group → ODBC Database. Select “Link to the data source” → OK. Choose “Machine Data Source” or “File Data Source” if you use a DSN, or “Connection string” for DSN-less. For DSN-less: build the string (see below) or use the “Build” button, pick “SQL Server”, enter server name, authentication (Windows or SQL Server login), and database. Click “Next” through the driver dialog, then select the database. You should see a list of tables (e.g. dbo.Customers, dbo.Orders). Select the ones you need (Ctrl+Click for multiple) → OK. Access will list them as linked tables with the same name (without dbo.) unless you already had dbo in the name.
3. If you have many tables—relink in VBA: Use the RelinkAllTablesToSQL code below so you can point to a different server or database without relinking by hand. Change the Left(strTable, 3) = "tbl" condition to match your table naming, or remove the If block to relink every linked table.

Connection Strings That Work (Trusted and SQL Auth)

Use a DSN-less connection so every user PC only needs the ODBC driver; no DSN setup. For pass-through queries and VBA, use one of these (replace YourServer, YourDatabase, and if using SQL auth: YourUser, YourPassword).

' Windows authentication (current Windows user must have access to SQL Server)
"ODBC;Driver={ODBC Driver 17 for SQL Server};Server=YourServer;Database=YourDatabase;Trusted_Connection=Yes;"

' SQL Server authentication
"ODBC;Driver={ODBC Driver 17 for SQL Server};Server=YourServer;Database=YourDatabase;Uid=YourUser;Pwd=YourPassword;"

' Named instance (e.g. SQLEXPRESS)
"ODBC;Driver={ODBC Driver 17 for SQL Server};Server=YourServerSQLEXPRESS;Database=YourDatabase;Trusted_Connection=Yes;"

VBA: Relink All Tables to SQL Server (One Place to Change Server)

Put the connection string in one place (constant or table). This procedure relinks every table that starts with “tbl” to the same SQL Server database. Run it when you switch from dev to prod or change server name.

Public Sub RelinkAllTablesToSQL()
    Dim tdf As DAO.TableDef
    Dim strConnect As String
    Dim strTable As String
    strConnect = "ODBC;Driver={ODBC Driver 17 for SQL Server};Server=YourServer;Database=YourDatabase;Trusted_Connection=Yes;"
    For Each tdf In CurrentDb.TableDefs
        If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
            strTable = tdf.SourceTableName
            If Left(strTable, 3) = "tbl" Then
                tdf.Connect = strConnect
                tdf.RefreshLink
            End If
        End If
    Next tdf
    Set tdf = Nothing
    MsgBox "Relink complete.", vbInformation
End Sub

Queries and VBA That Break After You Convert Access to SQL

• Domain functions (DLookup, DCount, DSum): These run in Access and pull from the linked table—so they still work but can be slow (they often pull more data than needed). For better performance, replace with a saved query that joins to the lookup table, or a pass-through that returns one value. No syntax change required for them to work.
• Format() and dates in pass-through: In pass-through queries you write T-SQL, not Access SQL. Use T-SQL GETDATE(), CONVERT(varchar, datecol, 120), and single quotes for strings. Access #1/15/2024# is not valid in T-SQL; use '2024-01-15'.
• IIF in pass-through: T-SQL uses CASE WHEN … THEN … ELSE … END, not IIF (IIF exists in SQL Server 2012+ but CASE is universal). Replace IIF(cond, a, b) with CASE WHEN cond THEN a ELSE b END in any pass-through.
• CurrentDb.Execute and recordsets: CurrentDb.Execute "DELETE FROM MyTable WHERE ..." works when MyTable is a linked SQL Server table—Access sends the query to the server. Recordset.OpenRecordset "MyTable" also works. You only need to change code that builds connection strings for pass-through queries: set QueryDef.Connect to your ODBC string and QueryDef.ReturnsRecords = False for action queries, True for selects.

Troubleshooting: “Can’t Connect”, “#Deleted”, and Slow Performance

• Wizard or link says “Cannot connect to server”: Confirm SQL Server is running (Services: SQL Server (MSSQLSERVER) or your instance). For a named instance, SQL Server Browser must be running. From the Access PC, test: open ODBC Data Source Administrator → add a System DSN → SQL Server → enter server (e.g. MachineName\SQLEXPRESS) and complete the wizard. If Test fails, fix firewall (port 1433, or 1434 for browser) and that the Windows user or SQL login has access to the database.
• Linked table shows #Deleted in every row: Usually the table has no primary key or unique index on SQL Server. Access uses keys to detect which row was updated. Add a primary key in SSMS (or a unique index on a column that has no duplicates). Refresh the link (right-click linked table → Linked Table Manager → OK) or relink.
• Forms or queries are very slow after migration: Linked tables send the query to the server; if the query uses Access-specific functions or complex expressions, Access may pull too much data. Add indexes in SQL Server on columns used in WHERE and JOIN. For heavy aggregates or large result sets, use a pass-through query so the work runs on the server and only the result set comes back.

Checklist: Before and After You Convert Access to SQL

• Before: Back up the Access back-end and front-end. List all tables and row counts (Create → Query Design → close table list → SQL View: SELECT 'TableName', COUNT(*) FROM TableName; run for each). Ensure the database is split. Confirm ODBC driver and SQL Server connectivity.
• After wizard: In SSMS, row counts match; every table has a primary key; Yes/No and AutoNumber behave (test one insert). Fix reserved-word names and any truncated or wrong types.
• After relink: Open each form (add, edit, delete one record). Run each report. Run any VBA that writes or deletes. Test pass-through queries and set .Connect where needed. Put connection string in one place and document it.

When Not to Convert Access to SQL

Stay on Access when: the app is single-user or a handful of users with no corruption or lock issues; you have no SQL Server license or server support; or the cost of migration and testing does not pay off. Converting is most useful when you already have lock/corruption problems, need more concurrent users, or need server backup and compliance. For web-only or cloud-only access, a web app talking to SQL Server is usually a better fit than Access as the only client.

Common Mistakes When Converting Access to SQL

• Upsizing without splitting first: Always split: one .accdb with only tables (back-end), one with forms/reports/queries/VBA (front-end). Upsize only the back-end; then relink the front-end. Upsizing a single-file app creates a mess.
• Not checking primary keys in SQL Server: Tables without a primary key cause #Deleted in linked views and update failures. In SSMS, open each table and ensure the key column has the key icon; add it if the wizard missed it.
• Different connection strings on each PC: Use one DSN-less connection string in a shared config table or in VBA, and relink using that string (e.g. RelinkAllTablesToSQL). Avoid per-machine DSNs unless you manage them centrally.
• Pass-through queries still using Access SQL: Pass-through runs on the server. Use T-SQL only: CASE not IIF (unless SQL 2012+), single quotes for strings, standard date literals. Set QueryDef.Connect to your ODBC string and run the query.

Key Takeaways

• Split first (front-end + back-end), then run the Upsizing Wizard on the back-end copy. Verify row counts and primary keys in SSMS before relinking.
• Relink by deleting existing links and creating new ODBC links to the SQL Server tables. Use a DSN-less connection string and, if you have many tables, VBA to relink so you can change server in one place.
• Fix in SQL Server: missing primary keys (#Deleted), Yes/No bit vs -1/0, reserved-word table names, and truncated text. Then test every form, report, and VBA path.
• In pass-through queries use T-SQL (CASE, CONVERT, single quotes). Domain functions still work on linked tables but could be slow; replace with queries or pass-through where it matters.
• Convert when you have concurrency or corruption issues or need server backup/compliance; optional for small, stable single-user apps.

Frequently Asked Questions

Conclusion and Next Steps

Converting Access to SQL Server is doable with the Upsizing Wizard, a careful check of keys and data types in SQL Server, and relinking the front-end with a single connection string. Use the checklist, fix #Deleted and connection issues as above, and test every form and report. Multi-user Access database design is the foundation; Microsoft Access ODBC connection covers ODBC setup. If you need help planning or executing an Access-to-SQL migration, contact us for a free quote.