As mentioned in the article, Current Feature Work: Monaco Editor + Modern Chart Improvements (accessforever.org), two significant new Access features have been released this Autumn.
See the separate article on Modern Chart Improvements for details of that feature.
This article describes the Monaco SQL Editor feature which was rolled out to current channel users with version 2410 build 18129.20116 released on 28 October.
Official Microsoft documentation for this feature is now available.
Monaco is the code editor from Visual Studio Code which has been adapted for Access. It has standard IDE functionality such as syntax highlighting, IntelliSense and autocomplete.
The most obvious change is the use of color for different sections of the query SQL.
For example:
Keywords (SELECT, FROM, WHERE etc) in BLUE
Operators (INNER/LEFT/RIGHT JOIN, LIKE, NOT etc) also in BLUE. (Previously PURPLE)
Functions e.g. Replace / IIf in MAGENTA
String values in RED
Number / date values in GREEN
Nested brackets (various colors)
There is also limited support for comments (in GREEN) at the start of the query. Comments are stored separately in a new system table MSysSQLComments.
The font size may be increased using Ctrl + or decreased using Ctrl –.
The feature can be toggled on/off using the Enable Monaco SQL Editor checkbox in Access Options | Current Database.
It can also be enabled in code:
Application.SetOption "Option to enable Monaco SQL Editor", True
The background color, and some foreground colors, change according to the Office theme in use e.g. black.
Using the right click context menu or clicking F1 gives access to the command palette with many additional options. The most recently used options are listed at the top.
The command palette provides access to IntelliSense listing available table, field, form control and function names.
It also allows users to find/highlight all occurrences of a string, identify the colors used by the editor etc.
NOTE:
The feature is only available in Access 365. Users running the same queries in all other versions of Access will continue to see the existing monochrome SQL editor.
Known Issues (last updated 11 Dec):
The Access team is currently working to fix all reported issues as a priority.
We only list issues here that are still open in the latest Current Channel version 2411 Build 18227.20162 (released 10 Dec). For information on issues that have already been fixed and on beta versions, see my detailed article linked below.
Outstanding issues include:
a) Slow to load, typically taking about 3 seconds. The feature depends on JavaScript code from the Edge browser which will hopefully be further optimized in the coming months.
b) Changing to SQL view requires the query to be saved due to the use of JavaScript. FIXED in Beta Channel - version 2412 build 18324.20012 (27 Nov)
c) Changes in font size do not persist e.g. if the query window size is altered or changing views.
d) Comments can only be added at the start of a query. It is not currently possible to comment out sections of a query (e.g. WHERE) clause for testing outcomes.
e) Line breaks and indentations made by users are not usually retained when the editor is reopened.
f) Drag and drop a table or query into the Monaco window creates a list of fields. Repeating this either adds a second list on line 2 or it may output the table/query values.
g) Not all special characters are currently supported in the Monaco editor.
h) The scrollbar does not respond to the mouse wheel or move with the mouse cursor position. FIXED in Beta Channel - version 2501 Build 18330.20000 (11 Dec).
i) Lack of contrast between certain Monaco colors and the background color for users with dark grey or black themes. IMPROVED in Beta Channel - version 2501 Build 18330.20000 (11 Dec).
j Standard Access shortcuts Ctr+F4 & Ctr+W used to close the active database window do not work in Monaco.
Some users may prefer to disable Monaco until all major issues are fixed. This can be done by either unticking the checkbox in Access Options or using code:
Application.SetOption "Option to enable Monaco SQL Editor", False
However, at present, the option to disable Monaco only applies to the current database.
The Access team has been asked to reconsider this decision so that users can choose to disable Monaco globally if it does not meet their needs.
For further details about the Monaco SQL editor, see:
a) the official MS documentation: Use the Monaco SQL query editor - Microsoft Support
b) Colin Riddington’s detailed article: NEW: Monaco SQL Editor (isladogs.co.uk) including details of all known and fixed issues.
Hi Colin, Regarding the option to enable and disable the Monaco SQL query editor, you say that the disabling of the option only applies to the current database. What about enabling the option? Is that a global setting? So that enabling the option applies globally, but disabling only for the current database?
The new editor is eyecandy only. It doesn't support subqueries. For example, when switching this SQL query "SELECT * FROM (SELECT PrimaryKeyField, Field1, Field2 FROM Table) AS ReqTable" in design mode, you will see the subquery "ReqTable" but you won't see the fields in it (PrimaryKeyField, Field1, Field2). So it's impossible to create a relation on "ReqTable.PrimaryKeyField" with another table from there... Very disappointing and useless for me...
Lots of bugs on this one. When I get a popup to suggest a table or column, I get every table and column under the sun. Not just the ones related to the table in the FROM or JOIN clauses.