Access SQL Editor

Documentation

Note: You may request a feature or report bugs in the FogBugz bug database.  You may also contact me directly at  signature.

Access SQL Editor is an add-in for Microsoft Access, which allows you to save formatted SQL queries in a Microsoft Access (Jet/ACE) database, with syntax highlighting and comments.


Features

  • Syntax Highlighting
  • Comments
  • Line numbers
  • Search-and-replace, with regular expression syntax available in the “Find” text box
  • Hotkeys
    • Save (Ctrl-S)
    • Add line comments (Ctrl-Q)
    • Remove line comments (Ctrl + Shift + Q)
    • Undo (Ctrl-Z)
    • Redo (Ctrl-Y)

System Requirements

  • Windows with 32 or 64-bit Microsoft Access 2003, 2007, 2010, 2013, or 2016
  • Microsoft .NET 4.0 Runtime (free download from Microsoft)


Screenshots

Editing comments

Editing comments

Dragging tab window

Dragging tab window

Undocked SQL Editor, editing full-screen

Undocked SQL Editor, editing full-screen

Customizable docking layout

Customizable docking layout

Filter tables by the fields within them, or by the table names

Filter tables by the fields within them, or by the table names

Modify ODBC properties

Modify ODBC properties


Installation

  1. Register for a trial or purchase a license for Access SQL Editor
  2. After you fill out the form, you will be allowed to download your license, and the software.  Save them in a convenient location on your computer
    Download License
  3. Download the 32 or 64 bit version of Access SQL Editor. If your copy of Office is 32-bit, download 32 bit; otherwise download 64 bit. If you are in doubt, try the 32-bit version first
  4. Double-click the .MSI file to begin installation
    msi file
  5. Follow the instructions on the installation screens
    Open File - Security WarningAccess SQL Editor Install Screen 1Access SQL Editor Install Screen 2Access SQL Editor Install Screen 3Access SQL Editor Install Screen 4Access SQL Editor Install Screen 5Access SQL Editor Install Screen 6
  6. Open up a Microsoft Access database
    Microsoft Access - Northwind Database (Access 2007 - 2010)
  7. Open Access’s Add-Ins menu, and select Access SQL Editor.  In Access 2010, the Add-Ins Menu is on the Database Tools tab
    Add-Ins Menu
  8. Click the button that says “Please click here to install your license”
    Add-In First Time
  9. Choose the MsaSQLEditor-license.xml file that you downloaded earlier, and click “Open”
    Open License
  10. The add-in will tell you that your license has been installed. Click OK, and the screen will close. You are now ready to use the add-in
    License Installed
  11. Go to Add-Ins/Access SQL Editor to use it
    Opened Add-In


Usage

  1. To open Access SQL Editor, use the Add-Ins menu in Microsoft Access.
    Add-Ins Menu
  2. Once it is open, it will show you the tables in the Access (Jet) database that you are currently using
    Open Access SQL Editor Window
  3. To open a query for editing, click the name of the query once to highlight it, then click “Open”. Another way to edit a query is to right-click it, and select “Design”
    Open a Query for EditingOpen a Query for Editing Right-Click
  4. You may now edit your query. When complete, click “Save” or press Ctrl + S
    Editing Query
  5. To add comments to several lines of SQL at once, highlight all of the lines you wish to comment, and press Ctrl + Q
    Block Comment
  6. To remove them, highlight the text and press Ctrl + Shift + Q
  7. To create a new query, click “New”
    Create a New Query
  8. To find text, click “Find,” or Ctrl + F
  9. To find and replace text within your query, click “Find/Replace,” or Ctrl + H
  10. To undo the last operation, click “Undo,” or click Ctrl + Z
  11. To redo the last operation, click “Redo,” or click Ctrl + Y
  12. To run the currently open query, click “Run.” Note you must save the query before running it

Icon Pack

2016-06-07: The latest Alpha version of Access SQL Editor (available here), has the ability to display custom icons, and to distinguish between query types (make table, delete, etc.).  To that end, I am posting an Icon Pack for download here: Access SQL Editor Icon Pack.

To install it, first make sure you have the latest Alpha version installed.  Next, unzip the Icon Pack to your disk, and copy the icons into %appdata%\Field Effect, LLC\MsaSQLEditor\icons. If any of the subdirectories of that path do not exist, you must create them.

Please note that I will not distribute these icons with Access SQL Editor because they are part of Microsoft Access, and belong to Microsoft.  Do not download or use these icons unless you have Microsoft Access installed.

Hints and Tips

  • Use hotkeys:
    • Ctrl + S – Save
    • Ctrl + F – Find
    • Ctrl + H – Find and Replace
    • Ctrl + Q – Add line comments (double-dashes) to selected text
    • Ctrl + Shift + Q – Remove line comments from selected text
    • Ctrl + Z – Undo
    • Ctrl + Y – Redo
  • Unfortunately, there is no way to prevent edits outside of the Access SQL Editor.  When someone uses the built-in Access editor to edit a query, it will overwrite your formats.  Access SQL Editor allows you to see your last revision, and the current state of the query side-by-side:
    External Edits
  • Use the Query Filter to show only tables that contain a particular name. The Query Filter also supports regular expressions
    Query FilterQuery Filter Regular Expressions

Creating a Pass-Through Query

A pass-through query allows you to connect to a remote database, such as SQL Server, DB2, Oracle, MySQL, PostgreSQL, or Teradata.  In order to use a pass-through query, you must have the correct drivers installed on your machine.  You can usually download these ODBC drivers from the vendor of the database system that you would like to connect to.  In more recent versions of Windows, such as Windows 7, SQL Server ODBC drivers are pre-installed.

To create a pass-through query:

  • Create a new query or open an existing one
  • Right-click on the tab:

  • In the Properties dialog, you can enter your ODBC connection string.  There are two types of connection strings: DSN-based connection strings, and “DSN-less” connection strings.  A DSN (Data Source Name) is a user-wide or system-wide database connection shortcut.  If you click the ellipsis (…), you will see a system ODBC dialog. Choose the “Machine Data Source” tab to pick an existing DSN, or create a new one by choosing the “New…” button.  The system will then walk you through the process of creating a new user-wide or system-wide DSN.
  •  To use a “DSN-less” ODBC connection instead, simply write or paste your connection string into the “ODBC Connect Str” text box.  Most connection strings can be found at connectionstrings.com.  Please note that you must have the characters ODBC; before the connection string text. Sample SQL Server DSN-less connection string:
    ODBC;DRIVER=SQL Server;UID=<User Name>;PWD=<Password>;DATABASE=<Database Name>;SERVER=<Server Name or IP>
    

     

  • Pass-through queries appear in your queries with a globe icon next to them:

Overriding the Default Color Settings

Access SQL Editor doesn’t have the syntax coloring settings exposed yet, but it’s a feature that is in development.  However, there is a workaround to change the colors in the editor.  To do this, you’ll need to edit the ScintillaNET.xml file that’s included in your installation. Please note, this requires administrative access to your machine.

  1. Open Notepad (or your favorite text editor) as an administrator.  This can be done by searching for Notepad in your Start menu or Start screen and right-clicking it.  Choose “Run as administrator”.  Click “Yes” if a window appears, asking if you wish for Notepad to be allowed to “make changes to your computer”.

  2. In Notepad, click File->Open.
  3. Change the file filter to show “All Files (*.*)”:

  4. Browse to the folder where Access SQL Editor is installed (usually C:\Program Files\Field Effect, LLC\Access SQL Editor).
  5. Open ScintillaNET.xml.
  6. Scroll down to the part of the file where you see the <Style>…</Style> tags:

  7. Each style represents one type of element in the SQL syntax.  Now, you may edit the existing colors by changing ForeColor to a known color name, or by using an RGB hex value.  Here is an online tool for choosing colors using hex values.
  8. You may also choose to change the background color for an element.  You can do this by adding the BackColor=”#NNNNNN” attribute to one of the Style elements.
  9. There are some styles that aren’t shown in ScintillaNET.xml by default.  If you want to change them, you will need to add a new <Style> element within the <Styles>…</Styles> block, like this:
    <Style Name="{Style Name}" ForeColor="{Color Name}" BackColor="{Background Color}" />
    Replace the text in braces ( {…} ) with the desired values.

Below is a partial list of styles with their descriptions.

Style Name Description
BRACEBAD Mismatched brace
BRACELIGHT When cursor is placed next to a brace, shows its matching brace
CALLTIP
COLUMN_NAME_2 Column names within square brackets
COMMENT Block comments /* … */
CONTROLCHAR
DATATYPE
DEFAULT Default styling
DEFAULT_PREF_DATATYPE
DOCUMENT_DEFAULT
FUNCTION Aggregate functions, SQL Server functions
GLOBAL_VARIABLE
INDENTGUIDE
LASTPREDEFINED
LINE_COMMENT Single-line comment (– …)
LINENUMBER Line numbers
MAX
OPERATOR Operator symbols (commas, parentheses, +, -, etc.)
STATEMENT
STORED_PROCEDURE SQL Server stored procedures
SYSTABLE SQL Server system tables

Regular Expression Recipes

To convert a pasted row of Excel cells into Access SQL Editor, then convert them to a commented list (Version 1.1.18+)

Find: \t{0,1}((\S| )+)(?=(\t|\r\n))

Replace: --${1}\r\n

Example

Header1{tab}Header2{tab}Header3

Becomes:

--Header1
--Header2
--Header3

 

  • wilo

    very nice. Is there a full integration planned? I want to edit alle Query with the new editor, as there are query behind listfields and comboboxes…

    • Steve

      I’m sorry for missing your comment. I think I may have discussed this with you through e-mail, but I’ll post here, too, for everyone else’s benefit.

      The “Queries” list box doesn’t show the queries behind forms by default. But it accepts search parameters in .NET regular expression form.

      If you type “.*” (without quotes) into the Queries search box, it will show the queries behind forms. Those queries are in this format: ~sq_cForm1~sq_cList0

      Please see the screenshot attached for illustration.

      I would like to integrate the editor more directly into Access, but I am trying to make sure that I do not do anything that would be unsupported by Microsoft, because it will be very difficult to maintain. For instance, I wanted to replace the built-in query windows altogether with Access SQL Editor windows, but I haven’t been able to find a supported way to do that. I did do some experimentation with the Windows API, which “kind of” worked, but it would not be supported.

  • Rick from Music City

    Suggestions:

    1. Color the nested pairs of parentheses. Jet’s requirement to use parentheses with multiple outer joins can make joins very hard to edit and debug, and colored parentheses would be invaluable. In fact, that’s what I was looking for when I found your product. I suggest the do-nothing sets of parentheses that Access query designer puts in the where clause be formatted in gray or removed outright. Save the bright colors for the parentheses that actually affect the query logic.

    2. Add buttons to automatically manage the carriage returns and indents.

    a. After each join, put a carriage return and then indent the next line to align the following table or query horizontally with whatever it is being joined to.

    b. before each AND or OR, put a carriage return and then indent to make the term that comes next align horizontally with whatever it’s being anded or ored with.

    c. Optionally put carriage returns into the select, group by, order by clauses. Replace each comma or comma/space with a carriage return followed by an indent, a comma, and a space. But it would be necessary to distinguish between commas that follow columns and those that are part of function calls. For example

    SELECT OfficeID, IIf(RegID=99, “not allocated”, RegName) AS Region, Headcount

    would become

    SELECT OfficeID
    , IIf(RegID=99, “not allocated”, RegName) AS Region
    , Headcount

    3. Add a “set table alias” feature. When the feature is invoked, it displays a list of source table names and the current alias for each. User edits the alias column. Feature flags invalid entries, e.g. same alias entered for two tables. User clicks ok and the feature updates the SQL accordingly.

    • Steve

      Thanks for the suggestions, Rick! I consider all suggestions very carefully.

      Your suggestion about colored parentheses is compelling to me.

      Regarding formatting, the current Alpha version from the Downloads Page exposes many more options from the Poor Man’s T-SQL Formatter (Ctrl+1) plugin. The built-in SQL formatter is meant to get your queries close to where they need to be, so that you can hand-edit them from there. I probably will not focus too much on query format structure as that is very subjective, and I have a limited amount of time that I am able to work on this project.

      Access SQL Editor does have a plugin architecture that is not yet documented. Plugins can be written in .NET (C#, VB.NET, F#, etc…). This allows for 3rd party or user-written plugins, which might be something that would interest you.

      I’m not sure I understand the Table Alias feature, but my long-term wish list does include Intellisense-type clause completion (tables etc.). I’m wondering if that’s what you mean?

      The main focus of Version 1.1 (next big release) is dockable windows, separate search for Tables/Queries, and better performance for the Tables window.

      • Rick from Music City

        By table aliases I mean where you substitute a shorter name for a table name to make the query more readable. For example with aliasing we can shorten this:
        select ProductTableWithALongName.id, ProductTableWithALongName.name, qryProductCalculatedFieldsQueryWithALongName.unitcost, qryProductCalculatedFieldsQueryWithALongName.demand
        from ProductTableWithALongName inner join
        qryProductCalculatedFieldsQueryWithALongName on prod.id = pc.id
        order by ProductTableWithALongName.id, ProductTableWithALongName.name, qryProductCalculatedFieldsQueryWithALongName.unitcost, qryProductCalculatedFieldsQueryWithALongName.demand

        To this:
        select prod.id, prod.name, pc.unitcost, pc.demand
        from ProductTableWithALongName as prod inner join
        qryProductCalculatedFieldsQueryWithALongName as pc on prod.id = pc.id
        order by prod.id, prod.name, pc.unitcost, pc.demand

        • Steve

          I’m sorry, I wasn’t clear — I know what a table alias is in the context of a SQL query, but I wasn’t clear on what your suggestion was. Did you mean something like the Intellisense you get from SQL Server Management Console? If so, that is part of my long-term plan.

  • Pingback: Access SQL Editor | Alex & Access()

  • Hello, I purchased Access Sql Editor and it is great.
    The only problem is that when I ‘Alt+Tab’ in the editor and come back, I lose the focus in the editor’s window.
    Also, when I run a query and then press Ctrl+W (or Ctrl+F4), the focus is no longer in the editor, so I have to use the mouse.
    Could you fix this?

    • Steve

      Hi Felipe,

      First, thank you for your purchase. I’m really sorry for the inconvenience. I was able to replicate the problem you are having.

      I’ve had a very tricky time modifying anything with focus in this app. There are a few bugs in my log related to focus. I end up fighting with MSACCESS in cases like this, because it wants to determine what gets the focus.

      I’ll try to see what I can do. I wonder if there is a way to do this with AutoIt or something similar.

      • I don’t know what you used to write this add-in.
        AutoIt has some methods to give focus to a window. C# and VBA also does.
        Using Spy++, I could observe that the Editor has a window handle.
        What I would before a query run in Access Sql Editor:
        1 – Store in a static variable the value of current window handle using API GetActiveWindow
        2 – Run the query
        3 – Use the API SetFocus to give the focus back to the editor
        4 – Active the ‘results’ tab.
        Depending on what language you are using, maybe I can provide some insight.

        • Steve

          It’s written as a .NET ActiveX object hosted on a standard Access form. The ActiveX object is written in C#.

          What you’re suggesting would give focus back to the editor right after running a query, which would be confusing for users, because when someone ran a query, it would pop up behind the SQL editor.

          It would be a lot tricker to figure out when the user has closed the Access window, or has hit Ctrl+Alt to switch focus back to Access. Since the query windows belong to Access, it would probably require hooking into the window messages of Access’s MDI children… and that’s kind of a “hackish” solution. I won’t add code like that to the SQL editor, because it’s unsupported & hard to maintain.

          • You are right.
            I don’t know much about Access Forms. Is there an event such as Window_Activate? If so, maybe you could put the SetFocus code in it to set the focus in the Sql’s text editor…

          • Steve

            As far as I know there isn’t a VBA event that occurs when Access is activated. I think you’d have to hook into Access’s window messages and capture the WM_ACTIVATE message.

            I have played with that in the past because of a different bug (when a SQL window is popped out into its own window the SQL Editor can’t see Ctrl+F… It’s directed to the Access application window), without success. Maybe I can look into it again and see if it can be used in a simple way for this purpose.

          • Thanks for the talk! It would be awesome.
            It’s a pity Access object model has such incovenience 🙁

          • For now, I’m displaying the results in the buil-in datagrid, so when I close the window with Ctrl+W, I get the focus back to the query editor 🙂

          • Steve

            I made a little progress on this issue. I was able to get focus to return on alt-tab by capturing WM_ACTIVATE, but I haven’t figured out how to get focus back from the internal query windows yet.

  • I’d love if both F5 and Ctrl+R could run a query.

  • George Hepworth

    I just had to write a blog article bragging on “Access SQL Editor”.
    https://gpgonaccess.blogspot.com/2017/07/a-fine-tool-for-access-developers.html

    Thanks for a great tool.

    George Hepworth
    MS Access MVP 2007-2017

    • Steve

      Hi George,

      Thanks so much for the amazing testimonial… from an Access MVP, no less! I’m really glad that folks are finding it so useful.

      Sincerely,
      Steve Russo
      Field Effect, LLC

  • Aleksandr M.

    Hi!
    First of all I want to thank you for such an awesome and useful addin! I’ve tried it for less than an hour and decided to buy a lisence, because it was the greatest thing I met for access, which really made essential improvements to the poor built-in sql editor. I really appreciate your work in this direction! And I’m also curious about when you’re planning to release the next version and what new features it’s supposed to have? Thanks!
    Alex

    • Steve

      Hi Alex,

      Thank you for the positive feedback! The Alpha version is the latest one available, which should give you a better idea of the improvements that are planned. Some improvements that are already implemented in the alpha at the time of this writing are:

      – Column editing (hold down ALT key and use mouse to begin column editing)
      – Queries and Tables can now be expanded to show their columns
      – Improved plugins interface for writing 3rd party enhancements (e.g. Export Queries plugin, Copy as HTML plugin)
      – Faster loading of tables and queries for large databases
      Optional icons to distinguish different types of queries/tables
      – Signed installer and add-in project
      – Many bug fixes

      I’ve been using the Alpha version of the editor myself daily. The main reason that the Alpha build hasn’t been promoted to the stable build yet is because the Alpha attempts to load table columns asynchronously, which is the source of some bugs. Once that is sorted out, and thoroughly tested, I will probably promote it to Stable.

      The biggest feature I’d like to add would be auto-completion, but I’m not sure that I have the time to implement that. It would be great to have users build and share their own add-ins through the plugin interface, but right now as far as I know, i’m the only one who’s written any add-ins– at least partially because it isn’t well-documented yet.