

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 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

Screenshots



Installation Installation

Register for a trial or purchase a license for Access SQL Editor 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 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 Double-click the .MSI file to begin installation

Follow the instructions on the installation screens

Open up a Microsoft Access database

Open Access’s Add-Ins menu, and select Access SQL Editor. In Access 2010, the Add-Ins Menu is on the Database Tools tab

Click the button that says “Please click here to install your license”

Choose the MsaSQLEditor-license.xml file that you downloaded earlier, and click “Open”

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

Go to Add-Ins/Access SQL Editor to use it





Usage Usage

To open Access SQL Editor, use the Add-Ins menu in Microsoft Access.

Once it is open, it will show you the tables in the Access (Jet) database that you are currently using

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”

You may now edit your query. When complete, click “Save” or press Ctrl + S

To add comments to several lines of SQL at once, highlight all of the lines you wish to comment, and press Ctrl + Q

To remove them, highlight the text and press Ctrl + Shift + Q To create a new query, click “New”

To find text, click “Find,” or Ctrl + F To find and replace text within your query, click “Find/Replace,” or Ctrl + H To undo the last operation, click “Undo,” or click Ctrl + Z To redo the last operation, click “Redo,” or click Ctrl + Y 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}

before the connection string text. Sample SQL Server DSN-less connection string: 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.

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”. In Notepad, click File->Open. Change the file filter to show “All Files (*.*)”: Browse to the folder where Access SQL Editor is installed (usually C:\Program Files\Field Effect, LLC\Access SQL Editor). Open ScintillaNET.xml. Scroll down to the part of the file where you see the <Style>…</Style> tags: 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. 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. 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

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

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.

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