As mentioned in the article, Current Feature Work: Monaco Editor + Modern Chart Improvements (accessforever.org), two significant new Access features are being 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 should be available soon.
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) in 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 25 Nov):
A large number of issues with Monaco have been reported, both whilst in Beta and since its release to the Current Channel. The Access team is currently working to fix all reported issues as a priority.
Known issues and fixes include:
a) It is 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.
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 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) Enabling the Monaco editor with a query open in SQL view crashes Access.
FIXED in Beta version W/C 18 Nov by blocking this action.
However, it is currently possible to bypass this by using code. Access will then crash.
i) Error messages such as 'The search key was not found in any record' and 'The command or action Send Object isn’t available now' have been reported by some users.
FIXED in Beta version W/C 18 Nov
j) The scrollbar did not respond to the mouse wheel or move with the mouse cursor position.
Mouse wheel support added in Beta version W/C 19 Nov but scrollbar also removed.
k) Lack of contrast between certain Monaco colors and the background color for users with dark grey or black themes
l) Standard Access shortcuts Ctr+F4 & Ctr+W used to close the active database window do not work in Monaco
m) Editing a listbox or combo box row source using the Monaco editor removes the space preceding keywords such as FROM and ORDER BY. This causes the SQL to be invalid and triggers an error.
n) The Monaco editor removes all backslashes from query SQL containing a path to a table in an external database. Once again, this causes the SQL to be invalid and triggers an error. This is because the backslashes are being treated as the escape character.
Fixed in in Beta version W/C 19 Nov. In other channels, use double backslashes in the path as a workaround.
o) Two sets of errors occur when using queries in an old Access 2000 format MDB file with Monaco enabled.
Opening or creating a query in any view results in an error message appearing after 2-3 seconds: Could not read definitions; no read definitions permissions for table or query MSysACEs
A compile error occurs if the query contains an expression.
These errors make old MDB files almost unusable at present. The easiest workaround is to disable Monaco.
p) Several users have reported issues with complex passthrough queries. Typically, the error message says comments are only allowed at the start of the query. However, this is often not relevant.
q) Standard Access shortcuts Ctrl+W and Ctrl+F4 used to close the active database window are currently not available in Monaco.
r) Pressing F1 to open the command palette without first clicking in the Monaco window crashes Access
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 article: NEW: Monaco SQL Editor (isladogs.co.uk)
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.