How to View and Edit SQL in Microsoft Access

Tweak an Access Query by Editing the Underlying SQL Code

Mike Chapple

Many Microsoft Access database developers rely on the program's built-in wizards to create queries and forms, but in some situations, the wizard's output may not be precise enough. Every query in an Access database reveals its underlying code, which is written in Structured Query Language, so you can tweak it into a perfect Access query.

How to View and Edit the Underlying SQL

To view or edit the SQL underlying an Access query:

  1. Locate the query in Object Explorer and double-click it to run the query.
  2. Pull down the View menu in the upper left corner of the ribbon.
  3. Select SQL view to display the SQL statement corresponding to the query.
  4. Make any edits you wish to the SQL statement in the query tab.
  5. Click the Save icon to save your work.

Access Considerations

Microsoft Access 2013 and later versions support ANSI-89 Level 1 syntax with several modifications. Access runs on the Jet database engine, not the SQL Server engine, so Access is more accommodating of ANSI-standard syntax and doesn't require Transact-SQL specific language.

Deviations from the ANSI standard include:

  • You can't use the DISTINCT clause within an aggregate function.
  • You can't use the LIMIT TO xx ROWS clause.
  • The single character wildcard in Access is ? rather than _.
  • The zero or more character wildcard in Access is * rather than %.
  • Access permits grouping and ordering on expressions.
  • Access allows the TRANSFORM statement and the PARAMETERS declaration.
  • Access supports additional aggregate functions including standard deviations and variances.
  • Access and ANSI use slightly different lists of reserved keywords.

Wildcards in Access can follow ANSI conventions only if your queries exclusively use ANSI syntax. If you merge conventions, queries will fail, and the Access standard governs.