Monaco SQL Editor Unwanted Side Effect

[Silversee]

I recently ran into an issue that seems to be an unintended side effect of the Monaco SQL editor.

In the latest version of Access I am using (MSO Version 2506 Build 16.0.18925.20076 64-bit), the Monaco editor has been enhanced to offer quite a lot of new functionality. One of these enhancements is that it automatically "pretty prints" SQL queries, adding line breaks and indentation to help the query become easier to read and to understand.

This is all well and good.

However, I have a few rather large and complex multi-table join queries intended to output specially formatted content for export to Microsoft Word. (For those curious, these include print-ready schedules for the prize lists for hunter-jumper equestrian competitions.) Some of the query fields are calculated, with chains of Immediate If conditions modifying the field's value based on various other field values. When accounting for all the fully qualified [query or table name].[field name] references, a few of these calculations might run up to 500 characters or so.

Well, Access has a limit of 1024 characters for any specific field in the Designer.

I found that after opening one of these queries in the Monaco editor and making any minor change, the query would no longer load in the Designer, triggering the Access 1024-character limit error. (Access helpfully does not tell you which field is too large either, haha.)

It turns out that by default the Monaco editor is using spaces for indentation, and the query is apparently saved in this formatted state. The character limit was being triggered by all the additional white space characters.

By right clicking in the SQL editor, I could open the editor's Command Palette, and found the command to convert all of the spaces to tabs. This solved the immediate issue in my case, but of course there are still quite a few tab characters added. I suppose it could mess with someone else's even bigger queries.

In any case, posting here as a heads up.

Edit: I may have posted my "solution" a bit too soon, haha. It seems that the editor wants to change everything back to spaces again the next time the query is opened, so the problem reappears. (My queries are already aliased to short abbreviations in the query design, so that isn't a way to save characters.)

 

[isladogs]

@Silversee
TLDR: For now, disable Monaco and manually remove the additional spaces.

The full answer:
You didn't mention it but you are on an insider channel: Current Channel (Preview) and as such can expect to get bugs in new features.
I am on the faster Beta Channel so get newer features but also even more bugs before most users!

I reported a very similar issue to the Access team a few weeks ago regarding the addition in design view of spaces in subqueries triggered by the new expanded (pretty) formatting feature. For example:

UPDATE tblCarsModel
SET
    tblCarsModel.Colours = (
        SELECT
            qryCars.Manufacturer,
            qryCars.Year,
            qryCars.Model,
            Count(qryCars.Colour) AS Colours
        FROM
            qryCars
        GROUP BY
            qryCars.Manufacturer,
            qryCars.Year,
            qryCars.Model
        ORDER BY
            qryCars.Manufacturer,
            qryCars.Model
    );

is displayed like this in design view:

I'm am also now unable to run affected queries and for now disable Monaco when necessary. I have also had to remove the unwanted spaces.
One approach is to open in the old SQL editor, press Ctrl+H and replace all double spaces with single spaces.
You may need to do this several times to fix all the additional unwanted spaces.

Unfortunately this bug isn't yet fixed in Beta Channel version 2507 build 19024.20000. I will follow this up again today.
It would be really useful if you could provide a cut down repro database with just what is needed to display your specific issue that I can forward to the Access team

 

[cheekybuddha]

UPDATE tblCarsModel
SET
    tblCarsModel.Colours = (
        SELECT
            qryCars.Manufacturer,
            qryCars.Year,
            qryCars.Model,
            Count(qryCars.Colour) AS Colours
        FROM
            qryCars
        GROUP BY
            qryCars.Manufacturer,
            qryCars.Year,
            qryCars.Model
        ORDER BY
            qryCars.Manufacturer,
            qryCars.Model
    );

Did you ever expect this query to work, or were you using an invalid query to demonstrate that the editor attempts to display it in grid view and falls to alert that it is invalid?

 

[isladogs]

That particular query was chosen as it clearly illustrated the addition of unwanted spaces in the query designer caused by the pretty formatting in Monaco. It was originally created to demonstrate a different Monaco bug (since fixed) and has been useful for testing several Monaco related issues

All subqueries that I've tested since the expanded format feature was added exhibit various issues caused by the additional spaces in design view

 

[cheekybuddha]

It was originally created to demonstrate a different Monaco bug (since fixed) and has been useful for testing several Monaco related issues

Still unclear why you would use a nonsense query to demonstrate.

That aside, I wonder if you were to have copied the contents of the Update To: field in the query designer and then pasted it, whether it would have maintained the formatting and indentation (ie the line breaks are there, but invisible)?

 

[isladogs]

@Silversee
The Access team have requested a simple repro database. Are you able to upload that here for me to pass on to them.

 

[isladogs]

Here is another (working) subquery based on Northwind 2.4 Dev edition

SELECT
    Orders.CustomerID,
    Orders.OrderDate,
    Orders.OrderID
FROM
    Orders
WHERE
    Orders.OrderID IN (
        SELECT
            TOP 3 OrderID
        FROM
            Orders AS Dupe
        WHERE
            Dupe.CustomerID = Orders.CustomerID
        ORDER BY
            Dupe.OrderDate DESC,
            Dupe.OrderID DESC
    )
ORDER BY
    Orders.CustomerID,
    Orders.OrderDate,
    Orders.OrderID;

It again demonstrates the issue with unwanted spaces in design view after expanding the formatting in Monaco.

However, the query will still run correctly despite the additional spaces.

 

[cheekybuddha]

Did you try copying the criteria for OrderID and seeing whether it retains indentation/new lines on pasting in a text editor? (Just for fun!)

 

[isladogs]

No but I have now. The additional spaces persist both in the legacy editor and in a text editor such as Notepad

Just to make matters worse if I paste the subquery SQL into a new query it is truncated possibly due to a character limit?) and the final part is altered

and in Notepad....

I have already forwarded all of this info as an unwanted side effect of Monaco to the Access team

 

[cheekybuddha]

Fancy giving it a go?

 

[isladogs]

See above

 

[cheekybuddha]

I'd consider the retention of formatting a good thing (though obviously not how it is displayed!)

Interesting that the query is truncated in the middle of the ORDER BY clause in your paste into Notepad. That does seem like a bug.

 

[Pat Hartman]

Have you tried using alias' so you can reduce the length of qualified names?

 

[isladogs]

Have you tried using alias' so you can reduce the length of qualified names?

Not sure who that comment is aimed at. In my case, it wouldn't have made the slightest difference.
For the OP it might have reduced the characters for the field below 1024 but it would still have displayed the issues in my examples

Hopefully the OP will return at some point to respond to later posts.

 

[Pat Hartman]

I was talking to the OP. None of your examples has exceptionally long field names or expressions so of course they wouldn't have a problem.

 

[Silversee]

Have you tried using alias' so you can reduce the length of qualified names?

Thanks for your interest, Pat. Yes, I have used aliases in my query.

 

[Silversee]

@Silversee
The Access team have requested a simple repro database. Are you able to upload that here for me to pass on to them.

I will see what I can do. The application is a front-end to SharePoint Online and so credentials would be needed. Perhaps I can copy relevant tables data into a local test database to demonstrate the issue.

 

[Gasman]

You can use this to obfuscate the data.

How to arrange order of records - Page 2

Hi, I've got a tricky to explain problem, so I'll try to explain with an example. Basically, I have a payments table, with various payment types (fee, porterage, travel, early start etc). What I want to do eventually, is to create separate remittance advice reports for each supplier...

www.accessforums.net

 

[isladogs]

You only need to provide the problem queries saved in SQL view together with the related tables containing a couple of records of dummy data.
If it helps disable Monaco in the repro database
No forms/reports/code needed

 

[Pat Hartman]

Thanks for your interest, Pat. Yes, I have used aliases in my query.

And that didn't shorten your names sufficiently?