Linked tables
Configure reliable Access front-end to SQL Server back-end linked tables.
Linking MS Access to a SQL Server back-end is the workhorse pattern for scaling departmental databases without rewriting every form on day one. Done well, users keep familiar screens while data benefits from SQL concurrency, backups, and size limits. Done poorly, linked tables feel slower than the old ACCDB, reports time out, and helpdesk blames SQL when the real issue is ODBC settings, missing indexes, or forms that pull entire tables across the network. This guide covers split-database prerequisites, ODBC and connection strings, performance tuning, deployment discipline, and troubleshooting,the practical steps between upsizing data and optionally moving to a web app later.
Linked tables let Access forms, reports, and most VBA continue addressing familiar table names while SQL Server stores rows. You gain row-level locking suited to concurrent writers, automated backups, and removal of the 2GB ACE ceiling. Upsizing projects often complete linking in days while web UI projects run months,making links the pragmatic first milestone.
Linking is not zero maintenance. Schema changes require refresh cycles, and performance depends on network latency between users and SQL. Remote users on Wi-Fi feel pain desktop LAN users never noticed,plan hosted desktop or web when latency dominates.
A monolithic ACCDB mixes UI objects and data. Split into front-end (forms, queries, VBA) and back-end (tables) using the Access Database Splitter or manual export. Place only tables,or eventually SQL-linked tables,in the back-end file users never open directly.
Follow the split database best practices: relative paths break after SQL migration, so plan connection strings via ODBC DSN or linked table connection strings embedded during relink. Each user should run a local or Citrix-cached copy of the front-end, not a shared front-end on a network share that corrupts when two people open it.
Install Microsoft ODBC Driver for SQL Server (17 or 18) consistently on every workstation or terminal server. System DSNs simplify IT deployment; file DSNs travel with front-ends but leak secrets if distributed carelessly. Connection strings should enable encryption,Encrypt=yes,and trust server certificates appropriately in dev versus prod.
For Azure SQL, configure firewall allow lists, private endpoints, or hybrid gateway when Access runs on-prem. Document service accounts versus integrated Windows authentication choices; SQL authentication with rotated passwords in a secrets vault is common in hosted scenarios.
After SQL schema exists,via upsizing wizard alternatives or professional migration,open the Access front-end, External Data → ODBC Database, select Link to the data source. Choose tables and views needed for forms and reports. Access stores connection metadata in MSys objects; relink via Linked Table Manager when passwords or servers change.
Compare primary keys: every linked table should have a unique index SQL recognizes so Access can update rows. Identity columns map from AutoNumber; verify insert forms supply keys or rely on IDENTITY correctly. Review the complete SQL Server migration guide for validation scripts post-migration.
Avoid linking unnecessary historical tables “just in case.” Each linked table is a network dependency; archive cold data to separate databases or filtered views.
| Method | Pros | Cons |
|---|---|---|
| System DSN | Central IT control | Per-machine setup |
| DSN-less in front-end | Portable FE copies | Credential rotation harder |
| SQL auth + vault | Works cross-domain | Password hygiene required |
| Windows integrated auth | No SQL password | Domain trust required |
Index foreign keys and frequent filter columns on SQL,not only primary keys. Use pass-through queries for heavy aggregations so SQL executes set-based work instead of Access pulling rows client-side. Replace forms bound to wide queries with parameterized queries or views filtered to recent records.
Set ODBC timeout values realistically; long-running reports may need batch jobs. RecordCount on large linked tables is expensive,avoid displaying full counts on form open. The T-SQL conversion guide helps rewrite Access SQL that cannot translate to remote execution.
Monitor SQL with DMVs or Query Store for recurring slow statements after go-live. Access users will not file tickets saying “missing index on CustomerID”,they will say “the app is broken.”
Linked tables slow after SQL migration?
Request a performance reviewVersion front-end files (FE_v2026_03.accdb) and distribute via script or login batch. When SQL adds columns, refresh links before users open forms,otherwise #Name? and #Deleted errors spread. Maintain a changelog tied to SQL migration scripts so dev and prod stay paired.
Terminal server farms need single-writer discipline for any shared resource still on disk. Prefer per-user front-end copies in user profiles. Automate relink after disaster restore with documented connection string updates tested in the restore drill from our backup strategy guide.
“ ODBC,call failed ” often traces to driver mismatch, VPN drops, or expired passwords. “Record cannot be updated” signals missing unique index or read-only view. Timeout errors during reports suggest pass-through or indexed filters. #Deleted on a form usually means another user deleted the row or keys changed,less common on SQL but still possible with cascading deletes.
Keep a staging SQL environment mirroring production cardinality with anonymized data so developers reproduce issues without touching live tables. Compare execution plans for slow queries before blaming network latency alone.
Linked Access front-ends plateau when user counts, mobile needs, or audit requirements exceed desktop patterns. SQL schema you validated becomes the foundation for web app conversion,forms rebind to APIs, data stays. Our Access to SQL Server service and migration guides support link-first, web-second phasing so you never repeat a risky big-bang weekend.
For end-to-end upsizing with linked tables validated, see our MS Access to SQL Server.
Modern Access versions commonly use ODBC Driver 17 or 18 for SQL Server. Consistency across dev and production matters more than the label,document the exact driver version in your deployment runbook.
Access links tables and views similarly. Stored procedures are accessed via pass-through queries, not standard linked tables. Many reporting optimizations move logic into views or procedures.
Views without updatable keys, missing unique indexes, or certain join definitions make Access treat tables as read-only. Add primary keys on SQL tables and avoid linking complex views for editable forms until tested.
Use Linked Table Manager in Access to refresh individual tables or relink all. Distribute updated front-ends so users do not run stale field lists that cause #Name? errors.
Yes for linking patterns, with extra attention to firewall rules, encryption, and gateway requirements if Access runs outside Azure’s network.