Skip to main content

Bill Mosca's Microsoft Access Database Tools, Code Samples and more for the serious developer.
Home
Access Basics
How To's And Articles
Utilities and Add-Ins
Code Samples
Recommended Books
Reviews
Apex SQL Editor
Total Visual Sourcebook
Blog, blah, blah
Misc Downloads & Links
Contact Us
About Us
ApexSQL Edit 2008.08
While this site is an Access developer's site, SQL Server plays a big role in many Access applications. I routinely use an SQLS back end with an Access front end in order to take advantage of the more robust and secure environment that SQLS has.
That said, the more I use SQLS databases, the more I find myself in the Enterprise Manager(2000) or the SQL Server Management Studio (2005).
When writing queries, triggers or stored procedures I find the tools offered through those clients to be rather vanilla. They're certainly not like the wonderfully helpful Access tools so I started shopping around for a third-party tool that would make those tasks easier.
Well, I found one that I fell in love with. The good folks at ApexSQL.com have a product called ApexSQL Edit (http://www.apexsql.com/sql_tools_edit.asp). This is one fantastic tool!
At first, it looks a little like the Query Analyzer (Fig.1). You'll see the tables, views and other objects listed in the Schema Browser panel. But there is something the QA just doesn't have to offer. There is a list of columns! Expand a column and you'll see all the tables that have that column. Not only that, but if you have a column with the same name, but a different size or data type it will be listed separately. That's a great way to see if you muffed your design. Why is AcctCode a varchar(10) in one table and a varchar(50) in another?
You can even get a full list of triggers. Cool!
Figure 1
Want to get the DDL for a stored procedure? Select it and look on the DDL tab in the Object Browser (Fig.2). This works for all objects. But that's not all the OB has.
Figure 2
Open the Object browser and you'll find it's is chock-full of things. This browser gives you instant information about the object. Select a table in the Object Browser and you'll see tabs like Data, Columns, DDL, Permissions and much more.
Now comes the best part...
Type SELECT * FROM in the query window. Then press the space bar. The Intellisense list opens with a list of all the tables and views. Select the one you want. Want to get a list of columns to replace the asterisk? Press Ctrl+Shift+E to refactor and expand the wildcard.
By the way, the Refactor can be downloaded for free to use as an add-in for SQL Server Management Studio.
There is also a fully customizable formatter. You can set it up to format any given SQL syntax to be just like you want your query structure. Indents, line breaks, upper case this, lower case that. It reminds me of the VBA editor, MZTools and SmartIndent1 all rolled into one.
Figure 3
Mouse over the option and the Example window shows you how it will look.
There are tons of features beyond the above. You can run a query with the Test Mode turned on and your transaction will automatically be rolled back.
Add line numbers, bookmarks, turn on the outline feature so you can collapse or expand blocks within the query window. It also helps spot those missing ENDs. There is an AutoIndent just like the VBA editor, too. Highlight opening and closing parentheses.
Switch to design view and you've got an Access Design window look and feel.
Write your own code snippets or use the ones that come with the product. Edit them or use them as they are. You can call them up with a hot key combination. The snippets can contain any of the system constants like a list of tables. There is one that I use all the time. It is a SELECT * FROM {tables}. I set the hot keys to sfw. I type that and press Tab. The query is typed and the list of tables pops open.
Figure 4
There are also tons of templates (Fig.5)for writing just about anything. Plus you can create your own templates. I double-clicked the "Simple Database Mail configuration" template and got a prompt for the Profile name and account.
And let's not forget the incorporation of source control.
Frankly, I can't think of anything ApexSQL Edit doesn't have. I could go on, but I still might leave out some features. This is definitely one you MUST try for yourself.
Disclosure:
While I wrote this review with complete honesty and was not influenced by ApexSQL in any way, I did earn VIP points as a participator in their forums. Because of my involvement there and this review, I was gifted with a couple of their gear.

1If you are not familiar with MZTools and SmartIndent check out the links on my home page. These are truly indispensable add-ins for hardcore VBA coders.