Disable SQL Automatic Code Formatting

[SparklySpartan]

Hello again! Nice to be back on the forum.

This might be a very simple question but has anyone else noticed in the SQL view that Access is now automatically inserting some indentation and line breaks. I was curious if there's a way to disable this. I didn't see anything in the Options->Object Designers->Query design section, which is where I though I'd find something.

Is this a new thing? It definitely was not happening to me before, but I couldn't tell you exactly when it started happening.

If anyone has any ideas, please advise!

Thanks,

-SparklySpartan

 

[Gasman]

Are you using the Monaco editor?
If so, you can unselect it in dB options.

 

[isladogs]

The new automatic expanded formatting feature was one of the most frequent requests from Access developers. It was added in version 2506 along with partial sql execution.

For more details, see my article

Monaco SQL Editor Improvements

This short article outlines three significant improvements to the Monaco SQL Editor recently announced by the Access team and which will roll out to all channels in the coming months.

www.isladogs.co.uk

Although the expanded format is often really useful, there are times when developers may wish to disable it. As a result there will soon be an option to enable/disable the expanded format with the default set to expanded.

For now, you can disable Monaco if you find this a problem.

 

[KitaYama]

Although it (Monaco) is often really useful,

To be honest, Outside the realm of Latin characters, it's a nightmare.
It even fails to save a query as simple as the following image. If I turn Monaco off, it saves and runs. But with Monaco......Oh my...

Microsoft 365 Current channel 2505 on Win 11

 

[isladogs]

Do you get errors with Japanese language Office or with Japanese characters in English Office?

Please can you provide a repro database with both object and field names in non-Latin characters and including a few problem queries. No confidential data.
I will test and forward to the Access team.

Similarly if any members using other non-Latin languages such as Greek, Arabic, Korean, Russian have similar issues with Monaco, please provide an example database showing issues for me to pass on

Thank you

 

[KitaYama]

Please can you provide a repro database with. both object and field names in non-Latin characters and including a few problem queries. No confidential data.

I'd like to, but I can't. Because Access doesn't save the query and shows the above error message.
If I turn off Monaco, Access saves the query and runs it without problem.
If I try to edit the sql of the query while Monaco is ON, it fails to save and shows the exact same error.

I think you can reproduce the error.
1- Create a blank database.
2- Add a new query and type some non-Latin characters in sql view.
3- Try to save it. The above error message shows.

If you don't have non-Latin keyboard installed, copy and paste the following:

SELECT
    [注文番号] AS xlOrderNo
FROM
    [TEXT;DATABASE=Y:;HDR=Yes].KediCancelledOrders.csv;

OR

SELECT
    [注文番号] AS xlOrderNo,
    [製番] AS xlManuNumber,
    [納期1] AS xlDelivery,
    [単価] AS xlUnitPrice,
    [注文数量（受注数量）] AS xlQuantity,
    [発注者用バーコード情報] AS xlDuplicateNo,
    [図番+(変記)] AS xlDrawingNo,
    [要求元] AS xlOrderedBy,
    [購買担当（漢字）] AS xlPersonInCharge,
    [品名（品名仕様）（漢字）] AS xlDrawingName,
    [発注品仕様（詳細）] AS xlTreatment1,
    [形式・材質・仕様・他] AS xlTreatment2,
    [発注者用備考（漢字）] AS xlRemarks
FROM
    [TEXT;DATABASE=Y:;HDR=Yes].KediNewOrders.csv;

Edit:
I'm away from my PC for a while. I was able to add a database with a query with a remote desktop app.
Open the query in sql view, edit the sql of the query, for example change it to "AS xlOrderNo2" and try to save the query.
You'll see the error.
I don't think you need the csv file, because you don't want to run the query. If needed, I can upload a csv too.

Edit2 :
Forgot to answer your question
I'm on English version of Windows 11 - English version of Microsoft 365
Everything on this machine is in English, except the locale in control panel is set to Japanese.

ChatGpt is telling me there are same reports on Chinese, Korean characters in Monaco too. I hadn't enough time to drill further.

 

[isladogs]

Yes, I assumed you would include queries created in the legacy editor.

I'm using 365 Version 2508 build 19119.20000 Beta Channel - Office is English (United Kingdom)
I do have non-Latin keyboards installed including Arabic and Greek, but not Japanese.

Anyway, I'm unable to replicate your issues in this build.

I viewed and saved the query in the simple repro database without error.
I then created and saved a second query in Monaco using your suggested SQL. Again no error

Yes I would also like example csv files so I can test whether the queries run correctly
Perhaps you could also supply some more complex queries for me to test
Thanks

 

[KitaYama]

Again no error

Maybe something has changed after my version (2505). I'll ask the IT to update my PC and will check again.

Yes I would also like example csv files so I can test whether the queries run correctly

A csv file for the second query is attached. Change .txt to .csv

Thanks for testing.

 

[isladogs]

Yup. That works! Design view:

However, as expected I cannot link to or import the csv file using the import text wizard due to the non-Latin character set.
From past experience I know that where text files fail, using Excel files often works!

I converted to .xlsx and linked to the file then created a SQL query from that file

Once again. No errors