First Published 28 Aug 2024                     Last Updated 3 Dec 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 released.

UPDATE 25 Nov 2024: Additional information including newly discovered bugs and details of known issues that have been fixed.

UPDATE 27 Nov 2024: Added information about expanding/collapsing SQL clauses with indentation and saving formatting.

UPDATE 3 Dec 2024: Added details of newly discovered bugs and details of known issues that have been fixed.



Section Links:
        Introduction
        Main Features
        Command Palette
        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 was rolled out to current channel users with version 2410 build 18129.20116 released on 28 October, though certain features are still being worked on.

By default, Monaco is enabled in all existing and new databases that are trusted. It is not currently possible to disable Monaco globally for all databases.

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) now also in BLUE. Changed from PURPLE and before that PALE GRAY 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 has 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 –

Line breaks and indentations can be added where this helps improve the legibility of lengthy SQL clauses.

After indenting part of a SQL clause, a downward arrow and control tip text appears if you hover over the space to the right of the line number.
Click the arrow to collapse the indented SQL range.


The arrow changes to horizontal, the control tip is updated and the line now ends in an ellipsis...


Another benefit is that if you then save the query, this formatting is (usually) retained when the query is reopened.

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:


For security reasons, the Monaco editor is NOT available in an untrusted database. The checkbox is not visible until it is trusted:


The Monaco editor can also be enabled / disabled in code. To disable the editor, use the following code:

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

To enable Monaco in code, just set the property True.

WARNING:
Do NOT enable the Monaco editor in code whilst a query is open in SQL view. Doing so will cause Access to crash (see Known Issues below)



3.   Command Palette                                                                                                                                                     Return To Top

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:




4.   Known Issues:       (LAST UPDATED 3 Dec 2024)                                                                                               Return To Top

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.

The following issues have all been reported to the Access team in recent weeks with several already fixed:

a)   The editor is slow to load, typically taking about 3 seconds.
      The feature depends on JavaScript code from the Edge browser and it is hoped this will be further optimized in the coming months.
      However, this work is outside the remit of the Access team.

      NOTE:
      If you change from SQL view to design or datasheet view then back to SQL view, the Monaco editor loads instantly. The layout has been saved and loads without delay.

b)   Changing to SQL view requires the query to be saved first. FIXED in Beta Channel - version 2412 build 18324.20012 (27 Nov 2024). Hooray!!!!!

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. Attempting to add inline comments will result in this error when the query is run:


      This means it is not currently possible to comment out sections of a query (e.g. WHERE) clause for testing outcomes.
      NOTE: SQL Comments are stored in a new system table MSysSQLComments.

e)   Line breaks and indentations made by users are not retained when the editor is reopened. However, formatting is often saved if you add line breaks and indentation.

f)   Opening a query with a comment in an older version prior to 2408 resulted in a reserved error (-3001) - NOW FIXED.

g)   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.

h)   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.

i)   Enabling the Monaco editor whilst a query was open in SQL view ALWAYS crashed Access. This serious bug was 'FIXED' in Beta Channel W/C 18 Nov.
      The 'fix' is actually a workaround to block this action with a warning message:


      The crash only occurred when Monaco was ENABLED whilst a query was open in SQL view.
      However in the current implementation, the message appears when enabling OR disabling Monaco with a query open in any view (datatasheet / design or SQL).

      IMPORTANT: FIXED in Beta Channel - version 2501 Build 18330.20000 (3 Dec 2024).
      Enabling Monaco in code whilst a legacy SQL editor window is open will crash Access with no warning message.

j)   FIXED in Beta Channel - version 2412 build 18324.20012 (27 Nov 2024).
      Access will crash and offer to create a backup in the following situation:
      Open with Monaco disabled then enable Monaco using the checkbox option or in code.
      Now open a query in SQL view then immediately press F1 to open the command palette without first clicking in the Monaco window.

      Workaround - click anywhere in the Monaco window before pressing F1.

k)   Several people had reported seeing the following very unhelpful messages: (FIXED in Beta channel W/C 18 Nov)
      - '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 wasn't always clear, it was often necessary to forcibly close Access to continue.

l)   Vertical scrollbar issues: ALL FIXED in Beta Channel - version 2501 Build 18330.20000 (3 Dec 2024).
      - Did not respond to the mouse wheel (FIXED in Beta channel W/C 18 Nov)
      - 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

      As a temporary workaround, the vertical scrollbar was removed in the last Beta channel version 2412 build 18314.20000 released W/C 18 Nov.
      Although the Ctrl+Home and Ctrl+End shortcuts do work, there is currently no visual indication when part of the SQL is 'off screen' below the Monaco window.

m)  Several users have reported issues with passthrough queries including simple queries based on stored procedures e.g. EXEC spGetStudentAttendance 12875;

      Issues are sometimes manifested with the following error message when the query is run:


      The message is often misleading and can occur even if the comments are only at the start or there are no comments in the query SQL.
      The issue appears to be that the Returns Records property is not being correctly managed by Monaco. Where Returns Records is set to No, the error occurs.

      Workaround - Set Returns Records to Yes (even for action type passthrough queries).
      In my tests, this worked reliably, but for some passthrough queries, it is possible this will create an error. If so, use error handling.

      Another report indicates very long passthrough queries of 300+ lines are not displayed in the Monaco editor which appears blank.

n)   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.


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

p)   FIXED in Beta Channel - version 2501 Build 18330.20000 (3 Dec 2024).
      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 and 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:


      Here is a short video (4:23) to demonstrate this bug for both combos & listboxes

       

q)   FIXED in Beta channel version 2412 build 18314.20000 released W/C 18 Nov.
      Query SQL containing a path to a table in an external database was modified using the Monaco editor so that all backslashes were removed.
      Once again, this caused the SQL to be invalid and triggered an error. For example:


      The query SQL was changed to:


      This is because the backslashes in the path were being treated as the escape character.

      Related to the above bug, in certain cases, strange characters were added to the query SQL.
      For example, using the character sequence '\f' in the the following SQL before the above fix was done:


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


      In addition to issues with '\f', it is possible that certain other escape key sequences will be wrongly interpreted by Monaco.

      If you do not yet have this fix, a workaround is to replace each backslash with a double backslash in the native SQL editor.
      This solves the issue as only the first of each 'backslash pair' is then removed by Monaco.

r)   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!

s)   Subqueries do not display fields in design view if a line break is added before the FROM keyword in the subquery SQL in Monaco.
      Line breaks do not cause issues where line breaks are added elsewhere in the main querry or the subquery.

t)   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.



5.   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 3 Dec 2024

Return to Access Articles

Return to Top