Query conversion
Translate Access SQL to T-SQL with fewer runtime surprises after upsizing.
After tables move to SQL Server, MS Access queries are the hidden migration workload. Access SQL embeds Jet-specific functions, implicit joins, and domain aggregates that SQL Server cannot translate when executed remotely,or translates into slow plans that pull millions of rows to the client. Converting Access queries to T-SQL means deciding what stays in Access as pass-through queries, what becomes views and stored procedures on SQL Server, and what moves into application code during a future web conversion. This guide covers translation patterns, performance traps, testing discipline, and governance so reports match historical totals after cutover.
Access optimizes for the Jet/ACE engine on local tables. Linked SQL Server tables introduce a boundary: Access asks SQL Server to return rows, then may process remaining logic locally. Functions like Format(), IIf() in SQL, Date(), and domain aggregates (DSum, DCount) often block remote execution. Queries that ran in milliseconds on a local ACCDB become minutes when every row crosses the network twice.
Action queries,append, update, delete,need row-level compatibility and primary keys on SQL. Make-table queries may require SELECT INTO on SQL with appropriate permissions. Crosstab queries rarely translate cleanly; rebuild as pivot queries in T-SQL or reporting tools.
Treat query conversion as a workstream parallel to linking tables,see the linked tables setup guide and full SQL Server migration guide.
Access shows a “pass-through” icon on queries when it sends work to SQL Server. Hover tooltips in query design are insufficient for production,use SQL Profiler, Extended Events, or Azure SQL diagnostics to confirm server-side execution under realistic filters. If you see SELECT * from huge tables without WHERE on the server trace, fix the query.
Recordsets opened in VBA with dbOpenDynaset on linked tables follow similar rules. Dynamic SQL built in VBA must use T-SQL syntax when executed via pass-through Connection objects, not Access SQL wrappers.
Common mappings: IIf(expr,a,b) → CASE WHEN expr THEN a ELSE b END; Format(date,’yyyy-mm’) → FORMAT(date,’yyyy-MM’) or CONVERT(VARCHAR, date, 23) depending on version; Date() → CAST(GETDATE() AS DATE); Now() → GETDATE(); Len() → LEN(); Mid() → SUBSTRING(); InStr() → CHARINDEX(); Nz() → ISNULL(); DateAdd → DATEADD; DateDiff → DATEDIFF.
Domain aggregates DSum/DCount/DLookup become correlated subqueries, APPLY joins, or window functions SUM() OVER (). First/Last aggregate functions in Access reports do not map to SQL Server FIRST_VALUE without careful partitioning,often rewrite with ROW_NUMBER().
Wildcard filters with Like become LIKE with T-SQL escaping; Access * and ? map to % and _. Parameter queries remain useful in Access front-ends but underlying SQL should use declared parameters in pass-through for plan reuse.
| Pattern | Keep in Access FE | Move to SQL Server | Move to web/API later |
|---|---|---|---|
| Simple filtered lists | Short term OK | Preferred view | API endpoint |
| Heavy aggregates | Avoid | View or proc | Report service |
| Crosstabs | Rebuild | PIVOT / tabular SSRS | Dashboard |
| VBA-orchestrated steps | Interim | Stored proc chain | Workflow service |
| Security row filter | Weak in FE | Row-level security / views | App auth layer |
Access allows implicit joins in the query designer (tables linked by lines) that translate to explicit INNER JOIN in SQL,verify cardinality does not multiply rows. Outer join syntax (+) legacy is rare in saved queries but appears in old SQL strings,rewrite as LEFT JOIN.
UNION vs UNION ALL: Access UNION removes duplicates like UNION; performance-sensitive reports should use UNION ALL when duplicates are impossible by construction. Subqueries in SELECT lists may need indexing on correlated columns.
Create pass-through queries in Access with SQL Server syntax only,no brackets around table names unless needed, use schema-qualified names dbo.Table. Set ReturnsRecords property appropriately for SELECT versus action batches. Use for month-end report SQL, bulk updates, and calls to stored procedures.
Pass-through queries bypass Access translation,good for control, bad if developers embed environment-specific names without configuration. Store SQL text in version-controlled scripts and deploy via migration tooling where possible.
Reports wrong after SQL migration?
Book a query reconciliation reviewPromote repeated Access query SQL into views,linked tables in Access can point to views as if they were tables. Indexed views help heavy read workloads when requirements meet SQL Server constraints (schema binding, etc.). Stored procedures encapsulate multi-step updates with transaction control,better than chained Access append queries over VPN.
After the Upsizing Wizard, wizard-created schemas are drafts; views and procs capture the cleaned business logic you want web projects to inherit later.
Filter early with indexed columns,OrderDate, CustomerID, Status,not functions on columns in WHERE (sargable predicates). Replace SELECT * in production queries with explicit column lists. Batch writes in transactions on SQL rather than row-by-row Access loops. Use SET NOCOUNT ON in procs called from Access to reduce chatter.
Monitor with Query Store on supported SQL versions. Compare estimated rows to actual rows after migration,stale statistics cause bad plans. Schedule UPDATE STATISTICS after large imports.
Maintain a catalog of queries with owner, business purpose, and conversion status. For each converted query, store baseline outputs: CSV hash or aggregate checksums. Regression-test after schema changes,Linked Table Manager refresh alone does not catch logic drift.
Governance prevents shadow SQL: developers saving one-off pass-through copies with divergent logic. Centralize approved views on SQL; Access front-ends reference names consistently. When web migration begins, port views first,data layer already validated.
Our Access to SQL Server engagements include query workstreams because upsizing tables without query validation is how projects “go live” with silent report drift. Pair technical conversion with migration services phasing and, when ready, replacing queries in web apps for the final architecture.
For upsizing plus query conversion with validation, see our MS Access to SQL Server.
Access attempts to pass 'passable' SELECT statements to the server as pass-through queries on linked tables. Expressions using non-translatable functions, certain joins, or VBA in SQL force local processing,often slowly.
Not necessarily. Use views for shared logic, stored procedures for complex multi-step or security-sensitive operations, and pass-through queries for report batches. Keep simple selects in Access during transition if performance is acceptable.
Forms and reports bound to queries without WHERE clauses that pull entire tables client-side. Add indexed filters, pass-through queries, or server views limited to recent data.
Compare row counts and sums on key columns against Access baselines for the same filter parameters. Test edge cases: null dates, empty strings, and fiscal year boundaries.
T-SQL dialect is largely the same; connectivity and tooling differ. Translation rules in this guide apply to on-prem SQL Server and Azure SQL Database.
During web migration, some presentation logic belongs in APIs; analytical batch jobs remain in SQL. Use phased cutover: stabilize T-SQL while Access UI remains, then port proven views to the web data layer.