First Published 28 Aug 2024                    Last Updated 31 Aug 2024                       Difficulty level: Easy

As mentioned in my earlier article, Two New Access Features Are Now Available in the Beta Channel, two significant new features are being are being released this Autumn. Both have been available to the two 'Insider Channels', Beta and Current Channel (Preview), for about two months with changes made in response to user feedback.

See the separate article on the Modern Chart Improvements feature which is expected to be rolled out to current channel users in early-mid September as an update to version 2408 build 17928.20014.

This article describes the new Monaco SQL Editor which may be rolled out to the Current Channel with version 2409, though certain features are still being worked on.

The Monaco SQL Editor 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.


When using the Colorful theme, the colors used include:
•   Keywords (SELECT, FROM, WHERE etc) in BLUE
•   Operators (INNER/LEFT/RIGHT JOIN, LIKE, NOT etc) in PURPLE. Originally PALE GRAY - changed to improve legibility
•   Functions e.g. Replace / IIf in MAGENTA
•   String values in RED
•   Number / date values in GREEN
•   Nested brackets (various colors)

The Monaco SQL editor will also have limited support for comments (in GREEN) but only at the start of the query.
The following comment syntaxes are both valid:--Example Comment and /*Example Comment*/
However, this syntax causes an error:


The font size may be increased using Ctrl + or decreased using Ctrl –

The background colour (and some foreground colors) also change according to the Office theme in use. For example using the black theme:


The Monaco SQL Editor can be toggled on/off using the checkbox in Access Options | Current Database:


The change becommes effective after reopening any query in SQL view. This is the same query SQL as in the previous screenshot but with the Monaco editor disabled:


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.



Here are a few examples of using the command palette to find/highlight all occurrences of a string and identify colors used by the editor:

a)   Highlight all occurrences of the string 'First'


b)   Find and highlight next occurrence of the string 'ObjName'


c)   Identify color used for predefined functions e.g. Mod


d)   Use Intellisense to list available table, field and control names


e)   Indent parts of the query SQL


NOTE:
The feature will only be available in Access 365. Users running the same queries in older versions of Access will continue to see the existing monochrome SQL editor:




Known Issues:

The following issues have already been reported to the Access team:
a)   The editor is slow to load, taking about 3 seconds on my machine.
      The feature depends on Javascript code from the Edge browser and it is hoped this will be further optimized in the coming months
b)   Changing to SQL view requires the query to be saved
c)   Changes in font size do not persist e.g. if the query window size is altered
d)   Comments are only partly implemented at present. It is not currently possible to comment out sections of a query (e.g. WHERE) clause for testing outcomes
e)   Opening a query with a comment in an older version prior to 2408 results in a reserved error (-3001)
f)   If a table or query is dragged and dropped into the Monaco window this adds the fields as a list


      Repeating the process with another table / query either adds a second list on line 2 or it may output the table / query values:


      In each case, errors will occur if the query is changed to design view or is opened normally.

      As with any new feature, there may be some remaining bugs that will be discovered once the Monaco editor is tested by a larger number of users in the current channel.
      If you do discover any bugs, please use the Feedback button below to provide me with the details. I will pass on any issues to the Access team



Feedback

      Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

      Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 31 Aug 2024

Return to Access Articles

Return to Top