Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 36 ยท Administration and Security

Upsizing Access to SQL Server

Move your Access data to SQL Server while keeping the Access front end. Learn when to upsize, data type mapping, the SSMA tool, linking, rowversion, pass-through queries and testing.

Access is a fine database for a department; it is not meant for hundreds of users, very large data, round-the-clock availability or strong per-user security. When you hit those limits you do not have to rewrite the application: you can upsize, which means moving the tables to a server database (usually SQL Server, including the free Express edition or Azure SQL) and keeping the Access front end linked to them. Forms, reports and most queries keep working, but the data engine, backups, security and scale are now the server's.

Signs it is time

  • The back end approaches the 2 GB limit.
  • More than about 15 to 25 people edit at once, or people connect over a WAN or VPN.
  • Corruption, slow networks or locking problems keep happening.
  • You need auditing, per-user permissions, point-in-time recovery, scheduled jobs or integration with other systems.
  • A single point of file failure is unacceptable.

If you only have performance trouble, first try splitting, indexes and better queries; upsizing needs a server and a person who can administer it.

Plan before you click

  1. Back up everything and work on a copy.
  2. Clean the data: remove duplicates and orphans, fix invalid dates (Access happily stores year 1900-ish junk), and make sure every table has a primary key. Tables without keys become read-only links.
  3. Record the inventory: tables, relationships, indexes, queries that use Access-only functions, VBA that opens dbOpenTable or uses Seek.
  4. Pick the target: SQL Server (on-premises or Express), Azure SQL Database, or another ODBC database such as PostgreSQL or MySQL.

Data type mapping

Access types do not exist one-to-one on the server; the migration tool picks these by default:

Access SQL Server Notes
Short Text nvarchar(n) Unicode; length from the field size
Long Text nvarchar(max)
Number (Long Integer) int
Number (Integer / Byte) smallint / tinyint
Number (Double) float
Currency money
Date/Time datetime2 or datetime choose datetime2 for new work
Yes/No bit Access Yes = -1, server bit True = 1; adjust queries that test = -1
AutoNumber int IDENTITY
Attachment, OLE Object varbinary(max) Attachments need a manual redesign
Hyperlink nvarchar(max) loses the link behavior

Two points need care. First, Yes/No: Access treats True as -1; always write = True in queries rather than = -1. Second, dates: Access Date/Time holds a date and time; make sure date-only fields do not contain stray time portions that break equality tests.

The migration tools

  • SQL Server Migration Assistant for Access (SSMA): a free Microsoft tool. Connect to the .accdb and the SQL Server, choose Convert Schema, review the report, Migrate Data, then Link Tables so the Access file points to the server tables. It also warns about unsupported features.
  • The older Upsizing Wizard (removed from recent Access) and Export to ODBC (External Data > Export > ODBC Database) copy one table at a time but not indexes or relationships.
  • Manual route: create the tables in SQL Server yourself, load them with INSERT from exported CSV or the SQL Server Import Wizard, then link.

After the data is on the server, relink in Access as described in linked tables and ODBC: pick the dbo tables, choose unique identifiers when asked and, ideally, use Windows authentication.

Things that change after upsizing

  1. Add a rowversion column (formerly timestamp) to every table that users edit through Access. Access uses it to detect changed rows, and without it you get "this record has been changed by another user" or #Deleted rows, especially with float and bit columns that have NULLs. Also give bit columns a default (0) and a NOT NULL constraint.
  2. Create views and stored procedures for heavy reports and link to the views, or call procedures from pass-through queries.
  3. Fix Access-only SQL. Functions such as IIf, Nz, Format, DLookup and #date# literals are evaluated by Access, which may pull many rows over the network. Move such logic into views using T-SQL CASE, COALESCE and FORMAT.
  4. VBA: DAO still works against linked tables (dbOpenDynaset); dbOpenTable and Seek do not work on linked tables. Use FindFirst or a parameterized query. Replace DoCmd.RunSQL with CurrentDb.Execute.
  5. Security moves to the server: create logins and give them roles and permissions on the tables.
  6. Transactions and locking are now server-side, which gives you isolation levels, long-running safety and no 255-user lock file.

The test for the Yes/No mismatch can be done in plain SQL; the same data stored as -1 versus 1 behaves differently when you compare:

SQL Why Access Yes = -1 can bite after upsizing

Press Run (or Ctrl + Enter) to see the result here.

Another typical rewrite is turning an Access query into a server view. Compare the two forms of the same business rule:

SQL Stand-in: moving IIf and Nz logic into a view

Press Run (or Ctrl + Enter) to see the result here.

In Access, link to v_order_summary and base forms and reports on it; the server does the calculation and sends only the results.

Testing and rollout

Run the old and the new system side by side on the same data and compare row counts and key totals (SELECT COUNT(*), SUM(Total) on both). Test every form with data entry, deletion and concurrency, and test slow connections. Keep the original .accdb back end as a fallback for a while. Roll out a new front end that has links already pointing to the server, tell users, and schedule the changeover in a quiet period. After going live, move backups, index maintenance and security reviews into the server's routine, and consider moving the heaviest reports to a reporting tool.

The server will not fix a badly designed schema: invest in normalization and integrity rules before you migrate, because they become constraints on the server.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What must a table have before upsizing so the Access link is updatable?

    Without a unique key, the linked table or view becomes read-only.
  2. 2. Why add a rowversion column to server tables edited from Access?

    Access uses the rowversion to find and compare rows, avoiding false conflicts and #Deleted rows.
  3. 3. A query uses WHERE Paid = -1. After upsizing the Yes/No column to bit it returns nothing. Why?

    Use = True or <> 0 so the query works with both representations.
  4. 4. A good way to speed up a heavy Access report on linked server data is:

    Let the server filter, join and aggregate, and send only the results over the network.

Summary

  • Upsize when you outgrow Access limits (size, users, availability, security), keeping the front end and linking to server tables.
  • Clean the data, give every table a key, map data types and use SSMA; then relink and test.
  • Add rowversion, fix -1 versus 1 booleans, move Access-only SQL into views or pass-through queries and revisit VBA that used Seek or dbOpenTable.
  • Compare old and new totals before switching users over.

Next: Project: a library database