First Published 28 Aug 2024 Last Updated 19 Sept 2024 Difficulty level: Easy
UPDATE 19 Sept 2024
The Monaco SQL Editor has been temporarily disabled due to some serious issues arising in the last two beta channel builds. I reported these on 18 Sept and the Access team responded within a few hours. The feature will be re-enabled once a fix has been fully tested - probably in the first half of October.
For more details, see my article: Monaco SQL Editor Temporarily Disabled
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 19 Sept 2024
Return to Access Articles
Return to Top