Click to purchase for only $9.99 USD, or learn about a FREE trial

Documentation

Note: Please report bugs or feature requests to our Product Support page.

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
• Column edit-mode (Alt+drag)
• Automatic SQL formatting plugin
• Tabbed interface with dockable windows
• Hotkeys
  • Save (Ctrl-S)
  • Add line comments (Ctrl-Q)
  • Remove line comments (Ctrl + Shift + Q)
  • Undo (Ctrl-Z)
  • Redo (Ctrl-Y)
  • Run (F5 or Ctrl-R)
  • Delete entire line (Ctrl+L or Ctrl+Shift+L)

System Requirements

• Windows 7, 8, 8.1, or 10
• 32 or 64-bit Microsoft Access 2007, 2010, 2013, or 2016
• Microsoft .NET 4.6+ Runtime (free download from Microsoft)

Screenshots

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
   
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
   
5. Follow the instructions on the installation screens
   

   

   

   

   

   

   

6. Open up a Microsoft Access database
   
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
   
8. Click the button that says “Please click here to install your license”
   
9. Choose the MsaSQLEditor-license.xml file that you downloaded earlier, and click “Open”
   
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
    
11. Go to Add-Ins/Access SQL Editor to use it
    

Usage

1. To open Access SQL Editor, use the Add-Ins menu in Microsoft Access.
   
2. Once it is open, it will show you the tables in the Access (Jet) database that you are currently using
   
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”
   
4. You may now edit your query. When complete, click “Save” or press Ctrl + S
   
5. To add comments to several lines of SQL at once, highlight all of the lines you wish to comment, and press Ctrl + Q
   
6. To remove them, highlight the text and press Ctrl + Shift + Q
7. To create a new query, click “New”
   
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

Access SQL Editor has the ability to display custom icons, and to distinguish between query types (make table, delete, etc.).

This icon pack now comes bundled as a plugin when you download the editor, and it is automatically installed the first time you run it.  When you close and re-open the editor, it will display the new icons.

Icons are unzipped into %appdata%\Field Effect, LLC\MsaSQLEditor\icons.

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
• Ctrl + R or F5 – Run a query

Hints and Tips

• 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:
  
• Use the Query Filter to show only tables that contain a particular name. The Query Filter also supports 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
Non-Administrator Installation
I have received several requests for per-user installation of Access SQL Editor.  Since I had a bit of free time, I experimented with this.  Please be aware of the following (known) limitations:

The installer will only install into C:\Apps\Access SQL Editor.  The path is hard-coded, because this is a very simple self-extracting install, and the registry must find the files in the correct location.
This is a completely experimental side project, and is not officially supported.

Non-Admin Builds



Build Link
SHA256


Access SQL Editor v1.1.27
497f750e3902679b68a78924af3bcec655ff115de53ed3a95efbaabded1447b1