Most Access systems we are called about are one file. Forms, queries, VBA and tables all live in the same .accdb or .mdb, sitting on a file share, opened by eleven people, and the person who wrote it retired in 2019. The instinct is to quote a rewrite of the whole thing on .NET 8. That is usually the wrong first move, because the rewrite cannot start until you know what the data actually contains, and you cannot learn that safely while every form writes directly to the file.
So split it in two steps. First move the data to SQL Server and leave the Access front end running against it. Then rebuild the front end, one form at a time, against a database that is already the system of record. This tutorial is the first step.
What you need: the Access file, a SQL Server instance (Express is fine to start), and SSMA for Access, Microsoft's free migration assistant. Nobody's day is interrupted while you do this.
Step 1: take an inventory before you touch anything
Copy the file. Work on the copy. Then write down four things:
- Table row counts and last-modified dates. Tables nobody has written to since 2014 are not part of the migration; they are an archive decision.
- Every query object, and whether it is a
SELECTa form binds to or an action query that changes data. Action queries are business rules hiding in the query designer. - Every VBA module, especially anything using
DoCmd.RunSQL,Recordsetloops, orApplication.Run. - Every external link: linked Excel workbooks, ODBC links to some other system, file exports, and any Word merge template.
That inventory is the scope. In a system with 60 tables it is normally a morning's work, and it is the difference between an estimate and a guess.
Step 2: characterize the reports before you move the data
Do this before the migration, not after. Pick the reports and exports that people actually use — the monthly billing run, the aging report, the file the warehouse system consumes — and save their current output to disk.
baseline/
2026-11-aging.csv
2026-11-billing-summary.txt
2026-11-warehouse-export.txt
These are your golden masters for the data tier. After the migration you regenerate them from the linked-table version and diff. A one-cent difference in a total is a rounding or type problem you want to find now, not in March. The same idea, applied to web pages instead of reports, is in golden-master tests for legacy web apps.
Step 3: fix the data model problems Access hid
SSMA will happily move a bad schema. Before you run it, deal with the usual four:
- Tables with no primary key. Access lets you get away with this; linked tables do not. A table with no unique key comes back read-only in Access after linking, and the form that edits it stops working. Add a key first.
- Repeated group columns.
Contact1,Contact2,Contact3should become a child table eventually, but not during this step. Move it as-is. One structural change at a time. - Text fields storing numbers or dates. Record them; do not convert them yet. Conversion changes sort order and comparison behavior, and you want that change to be its own visible event.
- Attachment and multi-valued fields. These have no SQL Server equivalent. Decide now: flatten to a child table, or move files to disk or blob storage with a path column.
Step 4: run the migration
In SSMA: connect to the Access file, connect to SQL Server, convert the schema, review it, then migrate the data. Two settings are worth stopping on.
Type mapping. Check what SSMA chose and override where it matters:
| Access | SSMA default | What we usually want |
|---|---|---|
| Text(255) | nvarchar(255) | Keep, unless the field is a code; then size it honestly |
| Memo / Long Text | nvarchar(max) | Keep |
| Number (Double) | float | decimal(19,4) for anything that is money |
| Currency | money | decimal(19,4) |
| Date/Time | datetime | datetime2(3), or date if there is never a time |
| Yes/No | bit | bit, and expect the sign problem below |
| AutoNumber | int IDENTITY | Keep |
The float row causes real problems. Access stores a lot of money in Double columns, and once totals are summed as float the pennies drift. Fix it in the target schema.
Link tables after migrating. Let SSMA replace the local tables in the Access file with ODBC links to SQL Server. The forms, queries and VBA keep working, because they still see table names. This is the same routing shim idea we use for web slices, applied to a desktop app.
Step 5: expect these five breakages
They happen on nearly every Access-to-SQL move, and they are all cheap once you know what you are looking at.
- Yes/No inverts or errors. Access stores true as
-1;bitstores1. Criteria written as= -1stop matching. Search the queries and VBA for-1andTruecomparisons. #12/31/2026#date literals. Access date syntax is not T-SQL. Any pass-through query orRunSQLstring with hash-delimited dates needs rewriting.- Nulls in numeric columns.
Nz()exists in Access and not in SQL Server. Pass-through queries needISNULLorCOALESCE. - Write conflicts on
bitandtimestamp. Access decides a row changed underneath it and shows "the record has been changed by another user". Add arowversioncolumn to each linked table; Access uses it for concurrency detection and the message goes away. - Forms that were fast are now slow. A form bound directly to a linked table pulls more than it used to. Bind slow forms to a view or a stored procedure that returns the rows the form shows, not the whole table.
Work through them, then regenerate the baseline reports from Step 2 and diff. When those files match byte for byte, the data tier migration is done.
Step 6: now the rebuild has somewhere to stand
With SQL Server as the system of record, the .NET 8 work stops being one big rewrite and becomes a list of independent pieces:
- The nightly export becomes a small console app or worker service, and the Access macro that ran it goes away.
- The one report the CFO needs from home becomes a Razor Pages or Blazor page hitting the same views.
- Order entry — the form with the real logic in it — gets rebuilt properly, with the VBA rules read, listed and confirmed with a person before they are reimplemented.
- Everything not yet rebuilt keeps running in Access, against the same tables.
There is no cutover weekend, because there is no single cutover. Each piece moves when it is ready, and the Access file shrinks until the day someone notices nobody has opened it in a month.
Where AI helps and where it does not
Large models are good at reading VBA and telling you what a 400-line procedure appears to do, at drafting the SQL rewrite of an Access query, and at listing every place a literal date or Nz() appears. We use them for exactly that, in bulk, because it is faster than grep plus reading.
They are not good at deciding which behaviors are contracts. The IIf in the pricing query that looks like a bug may be how the company has invoiced its three largest customers since 2011. A model cannot know that, and neither can we until we ask. The output of an AI pass is a list of candidates for a human to confirm, not a change set.
The short version
Move the data first. Keep the front end running on linked tables. Baseline the reports before you start, diff them after, and fix the five known breakages. Then rebuild in .NET 8 at whatever pace the business can absorb, with a real database underneath the whole time.
If you have an Access system in this position and want a second opinion on the sequence, tell us what it does and how many people use it. A senior engineer replies within one business day.