Migrating From Access to SQL Server

The Access forms are useful, but the shared file is carrying too much work. Migrating from Access to SQL Server can move the tables while keeping the front end people know. The hard part is checking every query and form after the links change.

Inventory the Application Before Migrating From Access to SQL Server

List tables, relationships, forms, reports, saved queries, macros, VBA code, and external links. Identify which tables hold shared business data and which are local lookup or temporary tables. Record who opens the front end and where the back-end file lives. A migration plan that counts tables but ignores forms will miss the work users actually perform.

I ask which screen people use at the busiest time. That screen is a good rehearsal test because it combines reads, writes, and usually a hidden query. Which Access query runs when the user clicks Save? Trace it before moving the table. A successful table import is not proof that the form will update through ODBC.

Separate Front End From Data

A practical first step in migrating from Access to SQL Server is to keep an Access front end and put shared tables on the server. Link the Access tables to their SQL Server counterparts through a supported ODBC driver. Give each user a local front-end copy under the established deployment method, rather than having everyone edit one shared application file. Maintain a clear version process for the front end.

I prefer a staged move because users can keep familiar forms while the database gains server-side backup, security, and concurrency controls. That does not remove the need to test. An Access form can rely on a local query or type assumption that behaves differently when rows come from SQL Server.

Choose Keys Before Linking

SQL Server tables need stable primary keys for updates and relationships. Check Access AutoNumber columns, composite keys, and fields with duplicate or null values. During migration, keep source keys when they identify existing records and plan how new keys are generated. A linked table without a reliable unique row identifier can behave badly in update forms.

The T-SQL query below lists primary keys on the target. Compare the result with the source relationships and the forms that edit data. I do this before blaming an Access form for a failed update. The table design should tell the form which row it is changing.

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    kc.name AS PrimaryKeyName
FROM sys.tables AS t
LEFT JOIN sys.key_constraints AS kc
  ON kc.parent_object_id = t.object_id
 AND kc.type = 'PK'
ORDER BY SchemaName, TableName;

Check Data Types and Null Rules When Migrating From Access to SQL Server

Access Yes/No, Date/Time, long text, currency, and attachment behavior need explicit target decisions. Test empty strings, nulls, decimals, dates, and Unicode values. SQL Server defaults and constraints can reject rows that Access accepted. That rejection can be a useful data quality signal if you handle it deliberately rather than weakening the target schema without review.

I keep a list of rejected records and the rule behind each correction. Do not let an import wizard’s success count become the only validation. Compare representative values in the Access form after linking. The server can store the right value while an old form control formats it misleadingly.

Rework Queries That Assume Access Syntax

Saved Access queries can use functions and syntax that SQL Server does not understand. A local Access query over linked tables can also pull many rows across the network before filtering them. Identify the expensive or frequently used queries, then move suitable logic into SQL Server views or pass-through queries. Test result order, null handling, dates, and parameters.

I watch for a form that suddenly becomes slow after linking. The database can be fine while the front end issues a chatty series of requests. Inspect the actual SQL reaching the server and the query plan. Rewrite the narrow hot path first, then measure again. Do not rewrite every Access query on day one without evidence.

Give the Front End a Small Identity

Create a SQL Server identity or integrated authentication path for the Access workload. Grant only the tables, views, and procedures it needs. Do not embed an administrator password in a distributed front end. Review how linked tables store connection information and how users authenticate after a password or certificate change.

The next query shows explicit target database permissions. It helps review the grants after setup, but role membership and server rights also matter. I test with an ordinary user account on an ordinary workstation. The DBA desktop is an excellent place to miss a missing ODBC driver.

SELECT
    USER_NAME(grantee_principal_id) AS Grantee,
    permission_name,
    state_desc,
    OBJECT_SCHEMA_NAME(major_id) AS SchemaName,
    OBJECT_NAME(major_id) AS ObjectName
FROM sys.database_permissions
WHERE class_desc = 'OBJECT_OR_COLUMN'
ORDER BY Grantee, SchemaName, ObjectName;

Test Forms, Reports and Concurrency

Reopen the critical forms through the new linked tables. Create, edit, and delete under a test account where the workflow permits it. Test two users editing related records and confirm the application’s conflict message is understandable. Run reports with realistic date ranges and compare them with the source under a defined cutoff. Include printing and export paths if people depend on them.

I ask users to perform a normal task, not only to inspect a screenshot. A form can load and still fail when it saves. Capture each difference, decide whether it is a defect or intentional rule change, and retest. Keep a rollback copy of the front end until the new links have passed.

Finish Migrating From Access to SQL Server Without Two Writable Sources

Freeze Access writes at the agreed point, move the final data changes, validate the SQL Server tables, and distribute the front end with new links. Confirm every workstation uses the intended front-end version. Retire or lock the old shared data file so users do not continue writing into a second source. Document the backup and recovery owner for SQL Server.

The familiar screen can make migrating from Access to SQL Server look smaller than it is. That is useful for adoption, but it must not hide the new operating model. The move is complete when data, forms, reports, permissions, and recovery all work together.

Related reading on this blog: Migration Assistant for Access, MySQL, Oracle, Sybase and MS Access: Count Distinct Values.

An Access migration is not only an upsized table, it is a tested front end on a managed data service.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.