MS Access vs SQL Server

Quick answer: which should you choose?

• Stay on Access (single file or split)

  Roughly 1-15 concurrent users, database under ~1 GB, simple workflows, desktop on LAN or RDP, budget-sensitive. Optimize and split the database before buying SQL licenses.

• Access front-end + SQL Server back-end

  15-100+ users, multi-site access, heavy reporting, approaching 2 GB, corruption or slowdown under load. Keep forms/reports/VBA; move tables to SQL Server or Azure SQL.

• SQL Server + new UI (web or .NET)

  Mobile-first, public internet, hundreds of concurrent writers, or compliance requiring full server-side control. Access UI is retired over time.

What Microsoft Access actually is

Hard limits you will hit in the real world

• 2 GB file size

  An .accdb cannot exceed 2 GB including system objects. Linked tables do not count toward that cap; local tables and embedded objects do. Hitting 1.5 GB is a planning signal.

• Concurrent users

  Microsoft documents higher theoretical limits; in practice, 5-15 simultaneous users on one backend file over a LAN is typical before record locking and network traffic hurt reliability. More users require a split database at minimum.

• Record locking

  Default shared-mode databases lock at the page level. One long-running report or form edit can block others. Splitting the backend and moving to SQL Server shifts concurrency to row/versioning models.

• No built-in HA cluster

  Access is file-based. Failover, geo-redundancy, and automated patching are manual or depend on RDP/VM strategy-not native database clustering.

What Microsoft SQL Server actually is

Editions that matter for Access shops

• SQL Server Express

  Free. Suitable for many Access upsizing projects. Database size cap (10 GB per database in current Express versions), limited RAM/CPU use, no SQL Agent on Express (use Task Scheduler or external jobs for backups).

• SQL Server Standard / Enterprise

  Paid per-core licensing. Needed for larger RAM, advanced HA, TDE, advanced analytics, and high write volume. Typical when Express limits or compliance requirements appear.

• Azure SQL Database

  Managed cloud SQL. No server patching by you; pay by DTU/vCore tier. Access links via ODBC the same way as on-prem SQL-ideal when teams are distributed and VPN file shares are painful.

Side-by-side comparison

• Architecture

  Access: file (.accdb) on a share or PC. SQL Server: service on Windows Server or Azure; clients connect with connection strings.

• Max data volume

  Access: 2 GB per file. SQL Server Express: 10 GB per DB (check current Microsoft docs for your version). Standard/Enterprise/Azure: terabytes.

• Typical concurrent users

  Access: ~5-15 on LAN backend (split). SQL Server: tens to thousands depending on hardware, queries, and edition.

• Query language

  Access: SQL dialect in queries + VBA. SQL Server: Transact-SQL (T-SQL), stored procedures, functions, views.

• Security model

  Access: database password (weak), optional workgroup security (legacy), file-share permissions. SQL Server: Windows/SQL logins, roles, row-level security, encryption, auditing.

• Backup / restore

  Access: copy .accdb while users are out, or automated file copy. SQL Server: native BACKUP DATABASE, point-in-time restore, Azure automated backups.

• Development speed

  Access: fastest for departmental apps (forms in hours). SQL Server alone has no UI-you keep Access or build web/.NET.

• Licensing cost

  Access: included in many Office subscriptions. SQL Server: Express free; Standard/Enterprise and Azure are ongoing cost-often still cheaper than re-platforming to SaaS ERP.

The hybrid most businesses should know: split + SQL back-end

• Back-end (.accdb or SQL Server)

  Tables and relationships only-on a network share (Access) or SQL instance (SQL Server).

• Front-end (.accde or .accdb)

  Forms, reports, queries, VBA-installed on each user PC or launched from a read-only share. Re-link tables after backend moves.

• Linked tables

  Front-end shows a globe icon; data lives in SQL Server. Users see the same forms; queries run on the server when designed as pass-through or optimized.

' Set in Linked Table Manager or VBA relink procedure
' Driver name varies by ODBC driver version installed

Driver={ODBC Driver 18 for SQL Server};
Server=tcp:yourserver.database.windows.net,1433;
Database=Operations;
Uid=access_app_user;
Pwd=***;
Encrypt=yes;
TrustServerCertificate=no;

' On-prem with Windows auth (common on domain LAN):
Driver={ODBC Driver 17 for SQL Server};
Server=SQL01\INSTANCE;
Database=Operations;
Trusted_Connection=yes;

When MS Access is the right choice

• Departmental tools

  Inventory, job tracking, CRM-lite, quoting-owned by one team, predictable schema.

• Rapid delivery

  You need working forms and reports in days; IT will not provision a SQL instance this quarter.

• Desktop + RDP workflows

  Users already work inside Remote Desktop; a split Access app on the session host is stable and familiar.

• Tight Excel integration

  Heavy export/import with Excel; Access VBA and linked Excel are well understood (see our Access-Excel integration guide).

• Budget

  SQL Server licensing or Azure spend is not justified yet-optimize and split Access first.

When SQL Server is the better choice

• Growth signals

  Frequent “database is locked” errors, compaction does not help, backend file over 1 GB, or users in multiple offices hitting the same .accdb over VPN.

• Data integrity under load

  Many simultaneous writers, complex transactions, or need for foreign keys enforced server-side with minimal client trust.

• Compliance

  Requirements for encryption at rest, granular audit trails, separation of duties, or HIPAA/SOX-style controls beyond file permissions.

• Integration hub

  Power BI, .NET services, mobile apps, and ETL tools connect more cleanly to SQL Server than to a file share.

• 24/7 availability

  Backups, failover, and maintenance windows without kicking everyone out of a shared .accdb.

Access SQL vs T-SQL: what breaks when you upsize

• IIf → CASE

  Access IIf(expr, true, false) becomes CASE WHEN in SQL Server views and procedures.

• Date functions

  Date(), Now(), DateAdd, DateDiff syntax differs. Use GETDATE(), DATEADD, DATEDIFF in T-SQL.

• Boolean fields

  Access Yes/No maps to BIT. Filters using True/False in VBA still work via ODBC; verify linked query results.

• AutoNumber → IDENTITY

  SQL Server IDENTITY columns need correct seed after data import so new inserts do not collide.

• Pass-through queries

  For heavy reports, set Query Type to SQL Pass-Through so SQL runs on the server instead of Jet pulling all rows to the client.

-- Access (QBE or SQL view)
SELECT IIf([Qty] > 0, "Active", "Empty") AS Status
FROM tblOrders
WHERE OrderDate >= Date() - 30;

-- SQL Server view or procedure
SELECT
  CASE WHEN Qty > 0 THEN N'Active' ELSE N'Empty' END AS Status
FROM dbo.tblOrders
WHERE OrderDate >= DATEADD(day, -30, CAST(GETDATE() AS date));

Performance: myths and measurements

• Before migrating, benchmark

  Note open times for key forms and run times for top five reports. Repeat after linking to SQL-compare apples to apples.

• Index strategy

  Index foreign keys, status fields, and date ranges used in WHERE clauses. Access often relied on small data; SQL needs explicit indexes at scale.

• Network

  SQL sends result sets, not whole files. WAN performance improves versus copying a 800 MB .accdb over VPN-but latency still matters; Azure SQL in the wrong region hurts.

Security compared honestly

• Access database password

  Obfuscation, not encryption. Do not rely on it for sensitive data.

• Share permissions

  Anyone who can copy the .accdb can attempt offline analysis. Split backend + NTFS permissions reduce casual access.

• SQL Server logins

  Per-user or per-app SQL accounts with least privilege (db_datareader / custom roles). Map Windows groups on domain deployments.

• Encryption

  TDE and Always Encrypted on supported SQL editions; Azure SQL encrypts by default. Plan key management with IT.

Cost: what you will actually pay

• Access only

  Office/Microsoft 365 subscription you may already own; server share or RDP host for multi-user.

• SQL Server Express

  No license fee; cost is Windows Server VM, backups, and DBA time-or Azure SQL consumption.

• SQL Server Standard

  License + CAL or core licensing; worth it when Express caps or HA requirements appear.

• Migration project

  One-time professional migration (schema, data, relink, query fixes, testing) is usually far less than rebuilding in a low-code SaaS platform or custom web app-especially with 50+ forms.

Migration path: start with Access, upgrade the backend

• 1. Split the database

  Database Splitter: front-end + back-end .accdb on the server. Fixes many corruption and deployment issues even before SQL.

• 2. Clean the schema

  Remove unused objects, fix orphaned keys, document queries that feed reports.

• 3. Upsize to SQL Server

  Use Access Database Tools → SQL Server (Upsizing Wizard) or SSMA for complex databases. Link tables back to the front-end.

• 4. Tune and test

  Convert problem queries, add indexes, run user acceptance on every critical form and report.

• 5. Plan the UI future (optional)

  Power BI for analytics; web app later if mobile or external users require it.

When NOT to migrate yet

• Single user, small data

  Splitting or compacting may be enough; SQL adds ops overhead with no benefit.

• Unstable requirements

  Schema churn weekly-fix the process first, then freeze for migration.

• No one owns the database

  Migration without a business owner fails UAT. Assign an internal champion.

• Replacing with ERP in 6 months

  Do not pay for SQL + migration if the app is being retired-export archives instead.

Access vs SQL Server vs other options

• Excel as database

  Not a replacement for either; use Excel for analysis. If data lives only in Excel, consider Access or SQL first (see replace large Excel workbook articles).

• SharePoint lists

  Fine for light lists; poor for relational reporting and VBA automation at scale.

• Power Apps + Dataverse

  Microsoft’s low-code path; higher per-user cost, different skill set. Good for greenfield mobile; expensive to replicate 80 Access forms.

• Custom web app

  Best for internet-facing scale; highest build cost. SQL Server is still the usual data tier.

Frequently asked questions

Can I use Access without SQL Server forever?

Does SQL Server replace Access forms?

Is Azure SQL the same as SQL Server?

How long does migration take?

What about Microsoft 365 / Access runtime only users?

Bottom line