How To View and Edit SQL in Microsoft Access

Mike Chapple

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

Procedure

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.

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 must govern.

Format
mla apa chicago
Your Citation
Chapple, Mike. "How To View and Edit SQL in Microsoft Access." ThoughtCo, Nov. 27, 2016, thoughtco.com/view-edit-sql-in-microsoft-access-1019934. Chapple, Mike. (2016, November 27). How To View and Edit SQL in Microsoft Access. Retrieved from https://www.thoughtco.com/view-edit-sql-in-microsoft-access-1019934 Chapple, Mike. "How To View and Edit SQL in Microsoft Access." ThoughtCo. https://www.thoughtco.com/view-edit-sql-in-microsoft-access-1019934 (accessed January 18, 2018).