MS Access – Improved SQL Editor

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

Like many features in Microsoft Access, the SQL Editor, SQL View has not made the slightest forward movement in 2-3 decades!

The Query by Example Design View is exceptional, but the SQL View is primitive (to put it politely). It doesn’t even support the most basic text editor functions that even Notepad has, and Notepad isn’t the thing to be aiming for when you consider other proper text editors such as NotePad++, PSPad, …! Currently, in the SQL View of the SQL Editor, you can’t even:

Use Ctrl+A to select all the text
Perform a Find/Replace
Use TABs to indent your SQL Statements
…

and we’re not even talking about other basic features you find in almost any text editor of the 20th century

Synthax highlighting
matching brackets
…

Add to this, that the request to improve the SQL Editor has been made numerous times on UserVoice and has been completely ignored. This is one of many reasons I analyzed the UserVoice statistics and concluded that it was a completely futile exercise! Below are just a few of the suggestions relating to the SQL Editor (sadly since the closing of uservoice the suggestions are lost with the proper vote count)

Provide a better SQL editor
Enhance the SQL Editor
Develop the SQL User Interface
… (there were others that are now gone forever)

these date back to 2015, in some cases, and not one has been done! That tells me everything I need to know about MS’ commitment to listening to their users.

This is why I wanted to demonstrate one approach to give us all a proper editor.

The solution

I was pleasantly surprised when I recently came across a really nifty javascript library that I knew I could incorporate into an Access WebBrowser control to get some of these missing features. CodeMirror is a versatile javascript library that enables one to build your own Editor. The beauty of the library is it is modular and you use the add-ons you wish. So you can select which language your editor will be working on and then plugin whatever add-ons you wish to provide to the end-user with the functionality you/they seek. Thus permitting you to create your own highly flexible and custom editor.

Below is the proof of concept of my Microsoft Access Improved SQL Editor.

As you can see by the image below, the editor includes feature such as:

Basic text editing (CTRL+A, Undo, …)
Active Line highlighting (active line is highlighted in light yellow)
Word matching (matching terms are highlighted in bright yellow)
Overall SQL term highlighting (purple)
Find/Replace
Auto Complete/Intellisense

Below illustrates the Auto Complete Feature in action.

When the Form initially loads, it passes the table and field listing to the Editor, so you will actually have real-time Auto Complete/Intellisense of all your database tables!

I should also point out that I have included the entire CodeMirror library (per my original download) so you can have access to everything, including demos to play around with and learn from, but for productions you could delete everything you don’t need (which is actually most of it) to lighten the footprint.

Lastly, since this is a web broswer driven solution, it is actually HTML, CSS & Javascript driven. As such, you have complete control over styling. You can simply edit the CSS files to change layout, colors, fonts, … and the likes with great ease. Making this solution that much more customizable.

Final Thoughts

So within a day, I was able to develop a FAR superior SQL editor than the Access’ native SQL View. So one has to ask why Microsoft ignored years of such requests (both private and public) when a home developer, with limited time & resources, could do it in under a day! Now I know it’s isn’t integrated in the QBE, sadly I can’t make those types of changes as only Microsoft has those capabilities, but this simply proves that it can be done and that “where there’s a will, there’s a way!”

Moral of the story, don’t hold your breath for Microsoft to fix things or implement the features we want and need. Instead, do it yourself!

Once again, I hope this proves useful to some of you out there.

Disclaimer/Notes:

If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

All code samples, download samples, links, ... on this site are provided 'AS IS'.

In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.

Download a Demo Database

Feel free to download a 100% unlocked demo copy by using the link provided below:

Download “Access - Improved SQL Editor” MSAccess_SQLEditor.zip – Downloaded 11633 times – 1.42 MB

Version History

Version	Date	Changes
V1.000	2019-10-15	Initial Release
V1.001	2019-10-15	Improved Auto-Complete Added basic instructions for Find/Replace and Auto-Complete Made active line and match colors more obvious
V1.002	2019-10-15	Added Table and Table fields to Auto-Complete feature Switched to using TinyMCE getContent to properly sanitize the returned HTML and setContent to push the HTML from the db to the editor
V1.003	2019-10-16	Added Queries and Query fields to Auto-Complete feature
V1.004	2019-10-16	MAJOR update Remove Browser Emulation hack as it isn’t required for this javascript library Code cleanup Fixed incompatibility with Edge emulation
V1.005	2019-10-30	Changed instructions for autocomplete key sequence to CTRL+Space Added matching bracket highlighting to the SQL editor

Other References on the Subject

CodeMirror

CodeMirror is a versatile text editor implemented in JavaScript for the browser. It is specialized for editing code, and comes with a number of language modes and addons that implement more advanced editing functionality.

16 responses on “MS Access – Improved SQL Editor”

Patrick Honorez October 18, 2019 at 4:30 am

Nice idea, but I couldn’t find out how to use it :-/
I opened the SQLEditor form and selected and existing query in the combo list…nothing happened.
Any hint ?
Thanks

Daniel Pineault Post authorOctober 18, 2019 at 8:18 am

Did you first unzip/extract the content to your hard drive? You can’t just open the zip file and work with it from there. It must be extracted first.

Did you enable the content or add the db to your list of Trusted Locations so the VBA code can run freely?

Once you select a query, the SQL for the selected query should automatically display in the editor so you can work with it.

Spiros October 21, 2019 at 3:34 pm

Amazing work !

Fabien Lemieux October 22, 2019 at 2:29 pm

Really cool. I’m already using it in my main prod db. Thanx.

Any plans to continue development, add more features? Are you taking requests?

Ben Sacherich December 5, 2019 at 12:50 am

This is a neat concept. I have built my own syntax highlighter in VBA using RichText for a T-SQL conversion tool. https://ibb.co/PZ4RyNh (sample image)
Is it ok to add your code to my tool? Is there a way that you can implement zoom with the mouse wheel that only changes the font size in the editor box and not the entire web frame?

Have you looked at an add-in called “Access SQL Editor”? It integrates nicely into Access. It uses the Scintilla engine as an editor (same as Notepad++), has syntax highlighting, and supports text zoom. It even allows you to save comments in your queries. There is a trial version available and the full cost is only $9.99. (I am not affiliated with the software.)

https://fieldeffect.info/wp/access-sql-editor-documentation/

Stephen Belli January 8, 2020 at 7:34 pm

Love your work, 1 question and 1 suggestion.
Where do I change the theme? sorry my HTML is not up to scratch.
Would it be possible to output the query results in a table below the editor?

Daniel Pineault Post authorJanuary 10, 2020 at 7:08 pm

It could be done. You’d need to run the query in VBA and build an HTML table and return it to the web browser. Or you could insert another subform below, perhaps datasheet, and push the query there.

Joko Ruwhof January 16, 2020 at 2:56 pm

Great job man! Thank you for sharing!

Ben Sacherich April 3, 2020 at 8:47 pm

Daniel,
Are you going to do any further development of this?
Can you tell us what the minimal required files are required for deployment? I would like to include this in an add-in I am working on. Would you be interested in providing some input?
Ben

Daniel Pineault Post authorApril 5, 2020 at 11:15 am

I truly don’t know. With things the way they are currently, all my work is on hold.

I have posted it here, unlocked, so anyone if free to take it and use it. So feel free to integrate it into your add-in.

The minimal files will depend on which options you choose to implement.

Addis January 4, 2022 at 2:27 am

SIr I have a question. My questions are I have 3 different tables in ms access stock management syste… so how to show opening and ending balance please help me..

Daniel Pineault Post authorJanuary 4, 2022 at 6:39 am

I’d urge you to ask your question in one of many Access devoted discussion forums, refer to MS Access Forums and Discussion Groups. Furthermore, you will need to give much more information regarding your structure, table names, fields, … relationships, … Good luck.

Maria Barnes March 31, 2022 at 9:28 am

Very interesting, Daniel! You might want to update the original post. The first 2 items on your list
Use Ctrl+A to select all the text
Perform a Find/Replace
you can now do in the Access SQL editor. I know not many improvements but some are better than none. I for one will be happy to get some more.

orange November 12, 2022 at 8:33 am

Daniel,
This is very interesting in light of the fact that the introduction of Monaco has been slowed. This might be a topic for your youtube channel. Any thoughts?

Daniel Pineault Post authorNovember 14, 2022 at 5:15 am

I too was looking forward to the improved SQL Editor and have once again had my hopes dashed by YEARS of waiting, delays, … Every time I think they are finally taking a step forward, …, well they don’t.

I’ll see what I can do.
1. orange November 18, 2022 at 6:26 pm
  
  Thanks Daniel for the awesome YouTube video. I’m sure you’ve awakened many who have long criticized the standard, long-lived SQL view/editor in MS Access.

Patrick Honorez October 18, 2019 at 4:30 am

Nice idea, but I couldn’t find out how to use it :-/
I opened the SQLEditor form and selected and existing query in the combo list…nothing happened.
Any hint ?
Thanks
1. Daniel Pineault Post authorOctober 18, 2019 at 8:18 am
  
  Did you first unzip/extract the content to your hard drive? You can’t just open the zip file and work with it from there. It must be extracted first.
  
  Did you enable the content or add the db to your list of Trusted Locations so the VBA code can run freely?
  
  Once you select a query, the SQL for the selected query should automatically display in the editor so you can work with it.
Spiros October 21, 2019 at 3:34 pm

Amazing work !
Fabien Lemieux October 22, 2019 at 2:29 pm

Really cool. I’m already using it in my main prod db. Thanx.

Any plans to continue development, add more features? Are you taking requests?
Ben Sacherich December 5, 2019 at 12:50 am

This is a neat concept. I have built my own syntax highlighter in VBA using RichText for a T-SQL conversion tool. https://ibb.co/PZ4RyNh (sample image)
Is it ok to add your code to my tool? Is there a way that you can implement zoom with the mouse wheel that only changes the font size in the editor box and not the entire web frame?

Have you looked at an add-in called “Access SQL Editor”? It integrates nicely into Access. It uses the Scintilla engine as an editor (same as Notepad++), has syntax highlighting, and supports text zoom. It even allows you to save comments in your queries. There is a trial version available and the full cost is only $9.99. (I am not affiliated with the software.)

https://fieldeffect.info/wp/access-sql-editor-documentation/
Stephen Belli January 8, 2020 at 7:34 pm

Love your work, 1 question and 1 suggestion.
Where do I change the theme? sorry my HTML is not up to scratch.
Would it be possible to output the query results in a table below the editor?
1. Daniel Pineault Post authorJanuary 10, 2020 at 7:08 pm
  
  It could be done. You’d need to run the query in VBA and build an HTML table and return it to the web browser. Or you could insert another subform below, perhaps datasheet, and push the query there.
Joko Ruwhof January 16, 2020 at 2:56 pm

Great job man! Thank you for sharing!
Ben Sacherich April 3, 2020 at 8:47 pm

Daniel,
Are you going to do any further development of this?
Can you tell us what the minimal required files are required for deployment? I would like to include this in an add-in I am working on. Would you be interested in providing some input?
Ben
1. Daniel Pineault Post authorApril 5, 2020 at 11:15 am
  
  I truly don’t know. With things the way they are currently, all my work is on hold.
  
  I have posted it here, unlocked, so anyone if free to take it and use it. So feel free to integrate it into your add-in.
  
  The minimal files will depend on which options you choose to implement.
Addis January 4, 2022 at 2:27 am

SIr I have a question. My questions are I have 3 different tables in ms access stock management syste… so how to show opening and ending balance please help me..
1. Daniel Pineault Post authorJanuary 4, 2022 at 6:39 am
  
  I’d urge you to ask your question in one of many Access devoted discussion forums, refer to MS Access Forums and Discussion Groups. Furthermore, you will need to give much more information regarding your structure, table names, fields, … relationships, … Good luck.
Maria Barnes March 31, 2022 at 9:28 am

Very interesting, Daniel! You might want to update the original post. The first 2 items on your list
Use Ctrl+A to select all the text
Perform a Find/Replace
you can now do in the Access SQL editor. I know not many improvements but some are better than none. I for one will be happy to get some more.
orange November 12, 2022 at 8:33 am

Daniel,
This is very interesting in light of the fact that the introduction of Monaco has been slowed. This might be a topic for your youtube channel. Any thoughts?
1. Daniel Pineault Post authorNovember 14, 2022 at 5:15 am
  
  I too was looking forward to the improved SQL Editor and have once again had my hopes dashed by YEARS of waiting, delays, … Every time I think they are finally taking a step forward, …, well they don’t.
  
  I’ll see what I can do.
  1. orange November 18, 2022 at 6:26 pm
    
    Thanks Daniel for the awesome YouTube video. I’m sure you’ve awakened many who have long criticized the standard, long-lived SQL view/editor in MS Access.