First Published 28 Aug 2024                     Last Updated 16 Nov 2024                                             Difficulty level: Easy

UPDATE 31 Oct 2024: The Monaco SQL Editor has now been rolled out to all Current Channel users.

UPDATE 12 Nov 2024: Further items have been added to the Known Issues section. Link to MS documentation added.

UPDATE 16 Nov 2024: Another issue with old A2000 format MDB files has been added to the Known Issues section. Some fixes have begun to be implemented. These will be noted in the next week or so.



Section Links:
        Introduction
        Main Features
        Known Issues
        Feedback



1.   Introduction                                                                                                                                                             Return To Top

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 official Microsoft doumentation is now available: Use the Monaco SQL query editor - Microsoft Support



2.   Main Features                                                                                                                                                           Return To Top

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:




3.   Known Issues:       (LAST UPDATED 16 Nov 2024)                                                                                               Return To Top

The following issues have been reported to the Access team in recent weeks:
•   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.
•   Changing to SQL view currently requires the query to be saved first.
•   Changes in font size do not persist e.g. if the query window size is altered.
•   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.
•   Opening a query with a comment in an older version prior to 2408 results in a reserved error (-3001) - NOW FIXED
•   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.

•   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 - NOW FIXED.

•   Enabling the Monaco editor will ALWAYS crash Access in a specific situation. This serious bug should be fixed in the next update during the week commencing 16 Nov.
     To reproduce this issue, open a form and maximize it. Next open a query in SQL view then tick the Enable Monaco SQL Editor checkbox.
     The usual warning message appears:


     At the same time, the SQL disappears from the query wndow leaving an empty window.
     Within a second or two, whether or not you click OK, Access will crash wthout warning and offer to create a backup.

•   Several people have reported seeing the following very unhelpful messages:
    - 'The search key was not found in any record'
    - 'The command or action Send Object isn’t available now'
    - 'Query must have at least one destination field'

     Whilst the cause of these messages isn't always clear, it may be neccessary to forcible close Access to continue

•   Vertical scrollbar issues:
    - Does not respond to the mouse wheel
    - Doesn’t change size when additional text is added to the Monaco window
    - Does not move when the cursor is moved e.g. to the end of the query so you can’t scroll back to the top

•   Lack of contrast between the Monaco colors and the black / dark grey background theme colors
     This is particularly acute in dark grey theme with both the operators (PURPLE) and keywords (BLUE) very hard to read.


•   The standard Access shortcuts Ctrl+W and Ctrl+F4 used to close the active database window are not available in the Monaco window.

•   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. For example:


     I changed both instances of 'Short Time' to 'Medium Time'. The combo row source was invalid andd could only be viewed using the Zoom window.
     This showed that spaces had been removed before the FROM and ORDER BY keywords with the SQL changed to:


•   Query SQL containing a path to a table in an external database is modified using the Monaco editor so that all backslashes are removed.
     Once again, this causes the SQL to be invalid and triggers an error. For example:


     The query SQL is changed to:


     This is because the backslashes in the path are being treated as the escape character.
     Replacing each with a double backslash in the native SQL editor solves this issue for now as only the first of each 'backslash pair' is removed by Monaco.

     In certain cases, strange characters are added. For example, using the character sequence '\f' in the the following SQL:


     In this case, the query SQL is changed, with the inclusion of a highlighted special character, to:


     In addition to '\f', it seems likely that certain other escape key sequences will be wrongly interpreted by Monaco.

•   Two sets of errors occur when using queries in an old Access 2000 format MDB file with Monaco enabled.
     Opening a query in any view results in an error message. This appears after 2-3 seconds and is related to permissions in the MSysACEs system table:


     The same error is triggered when creating a new query.

     You may also see a compile error if the query contains an expression. For example:


     These errors make old MDB files almost unusable at present. The easiest workaround is to disable Monaco.
     Alternatively change from datasheet or design view to SQL view then back again. The MSysACEs error message no longer appears . . . until the query is next opened!

NOTE: If you want to disable Monaco, either untick the checkbox in Access Options or use the following code:

Application.SetOption "Option to enable Monaco SQL Editor", False

4.   Feedback                                                                                                                                                                 Return To Top

As with any new feature, there may be some remaining bugs that will be discovered with the Monaco editor now being more widely used in the current channel.
If you do discover any further bugs, please use the Feedback button below to provide me with the details. I will pass on any issues to the Access team.

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



Colin Riddington           Mendip Data Systems                 Last Updated 16 Nov 2024

Return to Access Articles

Return to Top