Guide · Access replacement
Access to SQL Server: is migrating enough?
What moving your data to SQL Server fixes, what it leaves alone, and how to tell which you need.
"Upsizing" an Access database to SQL Server has been the standard advice for twenty-odd years, and for good reason. It's relatively quick, it's relatively cheap, and it makes an immediate difference.
But it's worth being clear about what it changes. An Access database is really two things: the data (tables), and the application (forms, queries, reports and code). Moving to SQL Server swaps out the first. The second stays exactly as it was.
What it fixes, and what it doesn't
What SQL Server fixes
Corruption. The data no longer lives in a file on a shared drive that a dropped connection can damage.
Size. An Access file tops out at 2 GB. SQL Server Express, the free edition, goes to 10 GB per database, and the paid editions far beyond that.
Many users. SQL Server is built for lots of people writing at once, so most locking trouble goes away.
Backups and security. Proper scheduled backups, and logins controlled per person rather than "whoever can see the shared drive".
What it leaves alone
Access on every PC. Everyone still needs Access (or the free Runtime) on Windows, with the front end copied to each machine.
Remote working. Still a VPN or remote desktop. Still no browser, phone or Mac version.
The key person. The forms, reports and VBA are unchanged, so whoever understood them is just as essential as before.
Slow screens. These can actually get slower if the queries aren't reworked. See below.
How the migration works
The old Upsizing Wizard was removed from Access in the 2013 version. Microsoft's replacement is SQL Server Migration Assistant for Access (SSMA), a free tool that reads your Access file, creates matching tables in SQL Server, copies the data across and can relink the Access tables to point at the new database.
After that, Access talks to SQL Server through "linked tables" over ODBC. To the people using it, the forms look the same. Underneath, every query now goes across the network to a server.
The gotchas
SSMA does the heavy lifting, but a few things catch people out every time:
- Tables need a primary key. A linked SQL Server table without a unique key is read-only in Access. Old tables often don't have one.
- Queries that drag everything across the network. An Access query that joins a big linked table to a local one, or uses Access-only functions, can pull the whole table down to the PC to process it. The screen that took two seconds now takes thirty. The fix is to rework those queries to run on the server (as views, stored procedures or pass-through queries).
- Data types don't map perfectly. Yes/No fields, dates outside the usual range, attachments and multi-value fields all need decisions, not just a default conversion.
- VBA that assumes a local file. Code that opens tables directly, relinks to a fixed path, or relies on Access-specific record locking may need rewriting.
- Testing. Every form and report needs checking afterwards, because "it opens" isn't the same as "it gives the same numbers".
For a tidy small database, this can be a few days. For a large one with lots of code, a few weeks is more realistic.
When migrating is enough
SQL Server on its own is probably enough if:
- Your main problems are corruption, size or locking, not the screens.
- Everyone who uses it works on office Windows PCs.
- More than one person understands the front end, or it rarely needs changing.
- You need two or three good years while you plan something bigger.
You probably need a proper replacement if:
- People need it from home, on site, or on a phone.
- Customers or suppliers need to see or enter anything.
- One person holds the knowledge, and losing them would hurt.
- The business has changed and the database keeps getting in the way.
It doesn't have to be one or the other. Moving the data first is often a sensible first step in a full replacement, because the new web application can then be built against the same SQL Server database while Access carries on running. We've seen the opposite, too: a business that moved its data into a cloud SQL Server and put a low-code front end on top, only to lose the developer who built it. The database was fine. The problem had just moved. That project is the case study on our replacing Access page.
Access to SQL Server questions
Use Microsoft's free SQL Server Migration Assistant for Access (SSMA). It creates the tables in SQL Server, copies the data and can relink your Access front end. Then check that every table has a primary key, rework any slow queries to run on the server, and test every form and report.
For most small businesses, yes. It's free and handles up to 10 GB per database, five times the Access file limit. If you outgrow that, the paid editions or Azure SQL take you further.
Usually because some queries are pulling whole tables across the network to be processed on the PC. Rewriting them to run on the server, as views, stored procedures or pass-through queries, normally fixes it.
No. SQL Server holds the data, but the screens are still Access, which only runs on Windows desktops. Getting it into a browser means building a web front end. See converting Access to a web application.
Moving your Access data to SQL Server?
Tell us about the database and we'll tell you if that's enough.
What it does, how many people use it, and what's going wrong. We'll say whether a SQL Server move would sort it, or whether you're better off planning a replacement.
What we will cover:
- Whether SQL Server alone fixes your problems
- The queries and tables likely to cause trouble
- What a full replacement would look like instead
Rather talk now? Book a call or ring 01474 639 089.
We reply within one working day.
