First Published 28 Aug 2024                     Last Updated 11 Dec 2024                                             Difficulty level: Easy


UPDATES:           DETAILS:

2024-10-31       The Monaco SQL Editor has now been rolled out to all Current Channel users.

2024-11-12       Further items have been added to the Known Issues section. Link to MS documentation added.

2024-11-16       Another issue with old A2000 format MDB files has been added to the Known Issues section. Some fixes have begun to be released.

2024-11-25       Additional information including newly discovered bugs and details of known issues that have been fixed.

2024-11-27       Added information about expanding/collapsing SQL clauses with indentation and saving formatting.

2024-12-03      Added details of newly discovered bugs and details of known issues that have been fixed.

2024-12-11       Several new fixes. Moved Fixed Issues to separate section



Section Links:
        Introduction
        Main Features
        Command Palette
        Known Issues
        Fixed 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 in all themes. 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 in matching pairs)

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.



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 11 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. As items are fixed, these will initially be moved to the Fixed Issues section below, before eventually being removed from this article completely.

The following items have all been reported to the Access team in recent weeks but are not yet 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)   Changes in font size do not persist e.g. if the query window size is altered.

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

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

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

f)   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:


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

h)   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.   Fixed Issues:       (LAST UPDATED 11 Dec 2024)                                                                                                 Return To Top

Usually, issues are fixed in the Beta Channel first, reaching the Current Channel a few weeks later.
However, in some cases, both channels receive the fix in the same week or the current channel is fixed first.

Item	Reported Issue	Fixed Channel (Build / Date)
i)	Changing to SQL view required the query to be saved first.	Beta Channel - version 2412 build 18324.20012 (27 Nov). Current Channel - not yet fixed.
ii)	Opening a query with a comment in an older version prior to 2408 resulted in a reserved error (-3001)	Beta Channel - version 2410 Build 18129.20012 (8 Oct) prior to Current Channel release.
iii)	The back-tick character (`) wasn't supported in Monaco which could not display the SQL at all. Instead a blank window was displayed.	Beta channel v2411 Build 18227.20000 (29 Oct). Current Channel v2411 Build 18227.20152 (5 Dec).
iv)	Enabling the Monaco editor using Access options or in code whilst a query was open in SQL view ALWAYS crashed Access. Fixed by blocking action with warning message:	Beta channel v2412 Build 18314.20000 (19 Nov). Current Channel v2411 Build 18227.20152 (5 Dec).
v)	After enabling Monaco, opening a query in SQL view then immediately pressing F1 to open the command palette without first clicking in the Monaco window caused Access to crash.	Beta Channel - v2412 build 18324.20012 (27 Nov). Current Channel v2411 Build 18227.20152 (5 Dec).
vi)	Several people had 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' The cause of these messages wasn't always clear but it was often necessary to forcibly close Access to continue.	Beta channel v2412 Build 18314.20000 (19 Nov). Current Channel v2411 Build 18227.20152 (5 Dec).
vii)	Vertical scrollbar issues: - Did not respond to the mouse wheel - Did not change size when additional text is added to the Monaco window - Did not move when the cursor is moved e.g. to the end of the query so you couldn’t scroll back to the top The vertical scrollbar was temporarily removed in the last Beta channel version 2412 build 18314.20000 released W/C 18 Nov. Now restored and working correctly in Beta Channel.	Beta Channel - version 2501 Build 18330.20000 (11 Dec). Current Channel - not yet fixed.
ix)	Issues with passthrough queries including simple queries based on stored procedures Errors were sometimes associated with this message when the query is run: The message was often misleading and occurred even if the comments were only at the start or there were no comments in the query SQL. The real issue was that the Returns Records property was not correctly managed by Monaco. Where Returns Records is set to No, the error occurred. Another report had very long passthrough queries of 300+ lines not displayed in the Monaco editor which appeared blank.	Beta Channel - version 2501 Build 18330.20000 (11 Dec). Current Channel v2411 Build 18227.20152 (5 Dec).
x)	Lack of contrast between the Monaco colors and the black / dark grey background theme colors. This was especially acute in dark grey theme with the operators (PURPLE) particularly hard to read. Operators are now the same colour as keywords (BLUE) in all Access themes which has improved legibility.	Beta Channel - version 2501 Build 18330.20000 (11 Dec). Current Channel - not yet fixed.
xi)	Editing a listbox or combo box row source using the Monaco editor removed the space preceding keywords such as FROM and ORDER BY. This caused the SQL to be invalid and triggered an error.	Beta Channel - version 2501 Build 18330.20000 (11 Dec). Current Channel v2411 Build 18227.20162 (10 Dec).
xii)	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.	Beta channel version 2412 build 18314.20000 W/C 18 Nov. Current Channel v2411 Build 18227.20152 (5 Dec).
xiii)	Two sets of errors occurred when using queries in an old Access 2000 format MDB file with Monaco enabled. a) Opening a query in any view resulted in an error message appearing after 2-3 seconds and was related to permissions in the MSysACEs system table: The same error was triggered when creating a new query. b) Compile errors may also be shown if the query contained an expression. However, this may not be Monaco related.	a) Current Channel v2411 Build 18227.20152 (5 Dec). Beta Channel v2501 Build 18407.20002 (10 Dec). b) Not yet fixed
xiv)	Subqueries did not display fields in design view where a line break was added before the FROM keyword in the subquery SQL in Monaco. Line breaks did not cause issues where line breaks are added elsewhere in the main query or the subquery.	Beta Channel - v2501 Build 18330.20000 (11 Dec). Current Channel v2411 Build 18227.20152 (5 Dec).
xv)	Union queries where the first SELECT clause was enclosed in parentheses triggered the same Inavild SQL query error message as in ix) above and could not be saved.	Beta Channel - v2501 Build 18330.20000 (11 Dec). Current Channel - not yet fixed.

6.   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 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 11 Dec 2024



Return to Access Articles Return to Top