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


UPDATE 31 Oct 2024
The Monaco SQL Editor is now being rolled out to Current Channel users. It may take several days before the rollout is complete.



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 three months with changes made in response to user feedback.

See the separate article on the Modern Chart Improvements feature which was rolled out to current channel users in late September with version 2409 build 18025.20104.

This article describes the new Monaco SQL Editor which is being rolled out to current channel users with version 2410 build 18129.20116 which was released on 28 October, 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, the following HTML comment 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 becomes 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:
1.   For security reasons, the Monaco editor is NOT available in an untrusted database and the checkbox option shown above is not visible.
      First trust the database by clicking Enable Content or moving it to a trusted location, then tick the Enable Monaco SQL Editor checkbox.

2.   The feature is only available in Access 365.
      Users running the same queries in other versions of Access (including Access 2024) will continue to see the existing monochrome SQL editor:




Known Issues:       (LAST UPDATED 19 Oct 2024)

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 can only be added at the start of the query SQL. It is not currently possible to comment out sections of a query (e.g. WHERE) clause for testing outcomes
      NOTE: SQL Comments are currently stored in a new system table MSysSQLComments
e)   Opening a query with a comment in an older version prior to 2408 results in a reserved error (-3001) - NOW FIXED
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.

g)   Certain special characters are not displayed correctly in the Monaco SQL editor.
      For example, this query has the special character '' in the Not In clause:


      The Monaco SQL Editor incorrectly displays that special character as something totally different:


      As a further test, I created another query with a large number of special characters available directly from the keyboard:


      In this case, Monaco just displayed a blank window. It could not display the SQL at all.


      The problem was that the back-tick character (`) wasn't then supported in Monaco. However, a fix has since been done for the back-tick character.

      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 Oct 2024

Return to Access Articles

Return to Top