SQL Editor Add-In Recommendation

[MajP]

Do you use any third party Add-in or application for replacing/enhancing the SQL editor? This has been a long time complaint. Doing just a Web search I see there is quite a bit out there. Anyone have experience or recommendations? Anyone built one? Could be a good group project.
Also a year ago people were rumoring about a potential update to the User Experience to include QBE and SQL Editor.

Modernizing the Access User Experience

Microsoft MVPs and other Insiders, Is there any Scuttlebutt from the Temple in Redmond concerning an overhaul for the interface. Every year we watch the web based tool leave us further and further behind. I'm too old start all over. I guess management, or the pastures, is all that left for us...

www.access-programmers.co.uk

Any news?

To summarize from the discussion below. So far, I see these recommended solutions

1. Access Studio
Author: Chris Arnold
Cost: Free

Access Studio

Access Studio adds the functionality of SQL Server Management Studio to Access. Used as a reference library, it allows users to quickly switch between Access apps, use formatted SQL, run parts of more complex SQL code, add comments, see results in the same development window etc.

www.isladogs.co.uk

2. Access Improved SQL Editor
Author: Daniel Pineault
Cost: Free
This sounds like some people are having issues with it running on certain versions of Access. I have not checked this out because for some reason I cannot get it to download at all.

MS Access - Improved SQL Editor | DEVelopers HUT

A better RAW SQL Editor for MS Access!

www.devhut.net

3. Access SQL Editor
Author: Field Effect LLC
Cost: 10.00 USD
https://fieldeffect.info/wp/downloads/access-sql-editor/

4. Query Helper
Author: EBS17
Cost: Free
https://www.access-programmers.co.u...tor-add-in-recommendation.325545/post-1852794

Also if would be nice if an add-in would include the SQL to VBA (and back) converter. Here are some links

SQL to/from VBA Converter Add-In

This Access add-in is an updated version of my SQL to VBA And Back Again utility which makes it easy to convert query SQL to a VBA string (or vice versa). Additonal functionality includes creating example code from the VBA string and temp queries to test the SQL / VBA output.

isladogs.co.uk

SQL Converter - AccessUserGroups.org

Convert your Access/Jet SQL queries into T-SQL to run on Microsoft SQL Server. It supports converting the most commonly used functions.

accessusergroups.org

Did I miss any?
If I get time I will try to summarize the capabilities in each.

 

[ebs17]

I'm using a homegrown add-in, in stalled beta state. But it does enough for my needs. The focus is on the formatting of SQL statements, which makes it possible to use the QBE only for the very first routine work and then to ignore it afterwards.

I'm still undecided about further expansion, because the Monaco editor is supposed to come and should already be there. The interface is in German, which is due to my origin. However, it should still be self-explanatory.

 

[dobseh]

I'm still undecided about further expansion, because the Monaco editor is supposed to come and should already be there.

Monaco was removed from the Access Roadmap and marked as Cancelled. In fact if you look at the roadmap for Access it has nothing in the "In Development" section at all any more...

 

[CJ_London]

I posted my free version of Access Studio here - you add it as a library rather than an add-in

Access Studio - formatted SQL and more

Attached is a zip file containing 32 and 64bit versions of Access Studio as demonstrated at a number of Access user group meetings over the last few months. It is also available here https://accessusergroups.org/europe/ and also hosted here https://www.isladogs.co.uk/add-ins/index.html The zip...

www.access-programmers.co.uk

Still working on the advanced version which does a lot more

 

[sonic8]

Monaco was removed from the Access Roadmap and marked as Cancelled. In fact if you look at the roadmap for Access it has nothing in the "In Development" section at all any more...

The Monaco Editor is not "cancelled" it has just been deprioritized and deferred past a March 2023 release date. - That's bad enough, but still different from being entirely given up as the term "cancelled" implies.
The Access Team published their current priorities here: Our Road Ahead - Microsoft Access Engineering Priorities Oct 2022

 

[isladogs]

As well as @CJ_London's Access Studio mentioned in post #4, I use the Access SQL Editor which is an excellent add-in available at low cost

Field Effect, LLC is under construction

fieldeffect.info

@ebs17
The file you posted is 32-bit only. Do you have a 64-bit version available?

 

[dobseh]

The Monaco Editor is not "cancelled" it has just been deprioritized and deferred past a March 2023 release date. - That's bad enough, but still different from being entirely given up as the term "cancelled" implies.
The Access Team published their current priorities here: Our Road Ahead - Microsoft Access Engineering Priorities Oct 2022

The roadmap was marked as cancelled. The statement from the Access team was :

This roadmap is cancelled and removed from the roadmap at this time and will be republished when we have a new date.

Slightly poor wording on my part on the post, I admit, I should have said "which was cancelled", rather than "and was cancelled". But the fact that it's not on the roadmap at all, makes me feel that March 2023 is probably not going to be even close to when it releases. At this point they may as well rename it Access Star Citizen and start crowdfunding it.

 

[ebs17]

@isladogs
No. I've never touched 64-bit access. I am an interested individual, not a company. I stay in areas that are useful to me. Therefore, any major expansion of activity should yield a corresponding financial return. But this path is long and improbable.

Additionally, I would like to point out Improved SQL Editor by Daniel Pineault. With clear means, an astonishing result in terms of presentation is achieved here. Daniel's comment in the post speaks for itself.

 

[Gasman]

As well as @CJ_London's Access Studio mentioned in post #4, I use the Access SQL Editor which is an excellent add-in available at low cost

Field Effect, LLC is under construction

fieldeffect.info

@ebs17
The file you posted is 32-bit only. Do you have a 64-bit version available?

Wow, that is a good price. If I was still using access in work, I would buy that, might even do so any way. And it even works on 2007.

 

[jdraw]

@isladogs
No. I've never touched 64-bit access. I am an interested individual, not a company. I stay in areas that are useful to me. Therefore, any major expansion of activity should yield a corresponding financial return. But this path is long and improbable.

Additionally, I would like to point out Improved SQL Editor by Daniel Pineault. With clear means, an astonishing result in terms of presentation is achieved here. Daniel's comment in the post speaks for itself.

Regarding the Improved SQL Editor from Daniel, are there instructions for its installation and use. Perhaps I've missed them. If anyone using this software and/or has additional info, please share. Thanks in advance.

 

[Gasman]

Regarding the Improved SQL Editor from Daniel, are there instructions for its installation and use. Perhaps I've missed them. If anyone using this software and/or has additional info, please share. Thanks in advance.

Jack,
Did you see the first line?

Here’s another useful tool for those of you that work with RAW SQL and don’t use the QBE.

 

[jdraw]

Paul,
Are you using the editor from Daniel? Yes I saw the line. Downloaded, extracted and then????

 

[ebs17]

are there instructions for its installation and use

The download contains an Access file and folder, containing HTML files and a code library (Javascript). The form in the database uses a web control that gets its content from the index.html in the folder. These two must be synchronized according to the filing in your file system. That's actually all.

The functionality (colors, highlighting and some other things) result from the functionality of JavaScript, where there are many ready-made and far-reaching solutions due to the wide range of uses on the web and in different apps.

 

[Gasman]

Paul,
Are you using the editor from Daniel? Yes I saw the line. Downloaded, extracted and then????

No, as I tend to start any query via the qbe window, not type it in raw.

 

[GPGeorge]

The roadmap was marked as cancelled. The statement from the Access team was :

Slightly poor wording on my part on the post, I admit, I should have said "which was cancelled", rather than "and was cancelled". But the fact that it's not on the roadmap at all, makes me feel that March 2023 is probably not going to be even close to when it releases. At this point they may as well rename it Access Star Citizen and start crowdfunding it.

I think the change in the way the Roadmap is maintained was a mistake, precisely because it has led to sentiments like this one. In other words, they replaced one kind of ambiguity with another, and one that is even more problematic.

The MS position is that projects will only go on the Roadmap when there is a certain level of certainty (I believe that is 90% certain) they can hit the target for release of that project, or more precisely, that it will be shipped in the month specified on their Roadmap.

And nothing else is implied by that policy. It's the "nothing else" that introduced ambiguity, unfortunately. A project can be quite advanced, but not yet within the 90% certainty of a specific release month or it can be pie in the sky with no certainty it can ever be released. Yet neither project will appear on the roadmap. And that allows people to assume that both projects are hopelessly lost in space.

I suggest a less dramatic assumption is appropriate. Uncertainty about what will and won't be released, and when, is certainly a source of frustration. On the other hand, assuming the worst is probably not warranted.

 

[jdraw]

The download contains an Access file and folder, containing HTML files and a code library (Javascript). The form in the database uses a web control that gets its content from the index.html in the folder. These two must be synchronized according to the filing in your file system. That's actually all.

The functionality (colors, highlighting and some other things) result from the functionality of JavaScript, where there are many ready-made and far-reaching solutions due to the wide range of uses on the web and in different apps.

I extracted all files in the zip to a specific folder ...local\temp]DanielSQLEditor.
Opened his supplied database
C:\Users\JP\AppData\Local\Temp\DanielSQLEditor\MSAccess_SQLEditor_V1.005.accdb
???
I am looking at form SQL Editor??
If I go to form design view, I see buttons


Directory set up

I guess I am missing something obvious.
Reminds me of old IBM manuals, it's obvious once you've been through it once or twice.

 

[Gasman]

Too late a version for me anyway.
TBH for $10 I'd go for the one that Colin uses if I wrote raw sql.

 

[dobseh]

I think the change in the way the Roadmap is maintained was a mistake, precisely because it has led to sentiments like this one. In other words, they replaced one kind of ambiguity with another, and one that is even more problematic.

The MS position is that projects will only go on the Roadmap when there is a certain level of certainty (I believe that is 90% certain) they can hit the target for release of that project, or more precisely, that it will be shipped in the month specified on their Roadmap.

And nothing else is implied by that policy. It's the "nothing else" that introduced ambiguity, unfortunately. A project can be quite advanced, but not yet within the 90% certainty of a specific release month or it can be pie in the sky with no certainty it can ever be released. Yet neither project will appear on the roadmap. And that allows people to assume that both projects are hopelessly lost in space.

I suggest a less dramatic assumption is appropriate. Uncertainty about what will and won't be released, and when, is certainly a source of frustration. On the other hand, assuming the worst is probably not warranted.

I've worked in IT for 20+ years, assuming the worst with Microsoft is absolutely warranted. Keeping in mind the project was meant for GR over 18 months ago and it's not even listed as in development anymore.

It's not ambiguity - it's not on the roadmap, assume it doesn't exist/will never ship. A pessimist is never disappointed.

 

[jdraw]

A pessimist is never disappointed.

As others have said--If you set your sites low enough, everything is acceptable.

 

[ebs17]

@jdraw :​

Form SQLEditor contains control Webbrowser0

Public Sub Form_Load()
' ...       
30        Me.WebBrowser0.ControlSource = "=""" & Application.CurrentProject.Path & "\html\index.html"""
' ...

cbo_SelQuery => select a query

The existing queries are quite sparse. Import two or three tables and create a slightly larger query and save it. After reopening the form, select this. Click on a field name, which is highlighted in color with each occurrence.