Microsoft Access remains, in many businesses, the quick querying tool for leveraging local files or ODBC sources. The ACE engine (successor to Jet), still integrated with Microsoft 365, runs internal applications that are sometimes decades old. Learning to write SQL queries directly in Access allows for an understanding of what the QBE grid generates behind the scenes and enables intervention when the result does not match expectations.
Jet/ACE SQL and standard SQL: what Access accepts and what it rejects
Most online SQL tutorials use MySQL, PostgreSQL, or SQL Server as a reference. The examples often work as-is on these engines, but not always on Access. Access’s SQL dialect (Jet/ACE SQL) has peculiarities that can confuse beginners used to copying and pasting code found on forums.
Access does not support CTEs (WITH) or window functions available in SQL Server or PostgreSQL. Any recursive or analytical query found online will need to be rewritten, often using nested subqueries or saved temporary queries.
On the other hand, Access offers the TRANSFORM clause for crosstab queries, a syntax absent from the SQL standard. This clause allows for pivoting rows into columns without using a spreadsheet. Those who want to learn SQL queries on Access save time by identifying these discrepancies from the start, rather than searching for why a generic example causes a syntax error.
Another notable difference is the join syntax. Access often requires nested parentheses whenever multiple JOINs are chained in a single query, whereas other engines accept a linear writing style. Forgetting these parentheses triggers a compilation error without an explicit message.
Access SQL Mode: switching from graphical to direct writing
The graphical query designer (QBE grid) is the usual entry point in Access. Every action, dragging a field, checking a sort box, setting a criterion, generates SQL in the background. Switching to SQL mode (tab “View” then “SQL View”) displays the query as the ACE engine will execute it.
Read before writing
Before writing a query from scratch, the most effective method is to build the query in graphical mode and then read the produced SQL. This reading helps associate each visual element (table, link, criterion) with its SQL translation. One quickly identifies the SELECT, FROM, WHERE, ORDER BY structure.
Once this correspondence is established, modifying the SQL directly becomes natural. One adds a function, replaces a criterion, inserts a GROUP BY, all without going back to the grid.
Basic commands in the Access context
- SELECT and FROM: select fields from one or more tables. Access uses the dot to separate the table name from the field name (Clients.Name), and brackets to enclose names containing spaces or special characters ([Order Date]).
- WHERE: filter records. Classic comparison operators work, but wildcards differ: Access uses the asterisk (*) instead of the percentage (%) with the LIKE operator.
- JOIN: link tables together. Access favors the INNER JOIN, LEFT JOIN, RIGHT JOIN syntax with the constraint of multiple parentheses mentioned earlier.
- ORDER BY and GROUP BY: sort and group. The HAVING clause works as in standard SQL, which simplifies filtering after aggregation.
Common errors when starting SQL on Access
Some errors consistently occur among beginners. Identifying them in advance avoids hours of frustrating debugging.
The first concerns missing brackets around field names. Whenever a column name contains a space, an accent, or a reserved character, Access requires brackets. Writing Order Date instead of [Order Date] causes an immediate error.
The second relates to data types. Access distinguishes double quotes for text in some versions and single quotes in other contexts. The hash (#) encloses dates: WHERE [Order Date] > #01/01/2024#. Using quotes instead of the hash for a date is a very common source of confusion.
The third error concerns multiple joins. Each pair of JOINs must be encapsulated in parentheses when exceeding two tables. The error message displayed by Access in this case is not very explicit, which leads many beginners to abandon the SQL query and return to the graphical grid.
Progressing beyond SELECT queries on Access
Once selection queries are mastered, Access opens the door to action queries: INSERT INTO, UPDATE, and DELETE. These commands work similarly to standard SQL, with one additional caution: Access executes action queries without asking for confirmation by default if warnings are disabled in the database options.
Testing first in selection mode (temporarily transforming the UPDATE into SELECT to check the targeted rows) remains a good practice before any data modification.
Crosstab queries, accessible via the TRANSFORM clause, deserve attention. They allow for producing a directly usable summary table, without exporting to Excel. The syntax is specific to Access: TRANSFORM followed by the aggregation function, then a classic SELECT completed by a PIVOT on the field to transpose into columns.
For those wishing to connect Access to external sources (SQL Server, CSV files via ODBC), passthrough SQL queries are an advanced option. They send the SQL directly to the remote server, thus bypassing the limitations of the ACE engine. The SQL written in this mode must adhere to the syntax of the target server, not that of Access.
The SQL dialect of Access has its limits, but it offers a concrete learning ground for those who already work with this tool daily. Mastering the peculiarities of Jet/ACE SQL prevents blindly reproducing unsuitable examples and provides a solid foundation for later migrating to more powerful engines.