Managing and debugging SQL queries in MS Access

Question

MS Access has limited capabilities to manage raw SQL queries: the editor is quite bad, no syntax highlighting, it reformats your raw SQL into a long string and you can't insert comments.

Debugging complex SQL queries is a pain as well: either you have to split it into many smaller queries that become difficult to manage when your schema changes or you end-up with a giant query that is a nightmare to debug and update.

How do you manage your complex SQL queries in MS Access and how do you debug them?

Edit
At the moment, I'm mostly just using Notepad++ for some syntax colouring and SQL Pretty Printer for reformatting sensibly the raw SQL from Access.
Using an external repository is useful but keeping there's always the risk of getting the two versions out of sync and you still have to remove comments before trying the query in Access...

Answer 1

For debugging, I edit them in a separate text editor that lets me format them sensibly. When I find I need to make changes, I edit the version in the text editor, and paste it back to Access, never editing the version in Access.

Still a major PITA.

Answer 2

If you're doing really complex queries in MS Access, I would consider keeping a repository of those queries somewhere outside of the Access database itself... for instance, in a .sql file that you can then edit in an editor like Intype that will provide syntax highlighting. It'll require you to update queries in both places, but you may end up finding it handy to have an "official" spot for it that is formatted and highlighted correctly.

Or, if at all possible, switch to SQL Server 2005 Express Edition, which is also free and will provide you the features you desire through the SQL Management Studio (also free).

Answer 3

I wrote my own editor http://johnmacintyre.ca/SQLBE.asp

please forgive the cheesy website ... someday I'll update it.

BTW-You do have to register it ... one of the stupid 'features' I'm planning to remove.

Answer 4

Similar to recursive, I use an external editor to write my queries. I use Notepad++ with the Light Explorer extension for maintaining several scripts at a time, and Notepad2 for one-off scripts. (I'm kind of partial to Scintilla-based editors.)

Another option is to use the free SQL Server Management Studio Express, which comes with SQL Server Express. (EDIT: Sorry, EdgarVerona, I didn't notice you mentioned this already!) I normally use it to write SQL queries instead of using Access, because I typically use ODBC to link to a SQL Server back end anyway. Beware that the differences in the syntax of T-SQL, used by SQL Server, and Jet SQL, used by Access MDB's, are sometimes substantial.

Answer 5

Debugging is more of a challenge. If a single column is off, that's usually pretty easy to fix. But I'm assuming you have more complex debugging tasks that you need to perform.

When flummoxed, I typically start debugging with the FROM clause. I trace back to all the tables and sub-queries that comprise the larger query, and make sure that the joins are properly defined.

Then I check my WHERE clause. I run lots of simple queries on the tables, and on the sub-queries that I've already checked or that I already trust, and make sure that when I run the larger query, I'm getting what I expect with the WHERE conditions in place. I double-check the JOIN conditions at the same time.

I double-check my column definitions to make sure I'm retrieving what I really want to see, especially if the formulas involved are complicated. If you have something complicated like a coordinated subquery in a column definition

Then I check to see if I'm grouping data properly, making sure that "DISTINCT"'s and "UNION"'s without UNION ALL don't remove necessary duplicates.

I don't think I've ever encountered a SQL query that couldn't be broken down this way. I'm not always as methodical as this, but it's a good way to start breaking down a real stumper.

---

One thing I could recommend when you write your queries is this: Never use SELECT * in production code. Selecting all columns this way is a maintenance nightmare, and it leads to big problems when your underlying schemas change. You should always write out each and every column if you're writing SQL code that you'll be maintaining in the future. I saved myself a lot of time and worry just by getting rid of "SELECT *"'s in my projects.

The downside to this is that those extra columns won't appear automatically in queries that refer to "SELECT *" queries. But you should be aware of how your queries are related to each other, anyway, and if you need the extra columns, you can go back and add them.

---

There is some hassle involved in maintaining a code repository, but if you have versioning software, the hassle is more than worth it. I've heard of ways of versioning SQL code written in Access databases, but unfortunately, I've never used them.

Answer 6

Are you talking here about what MS-Access calls 'queries' and SQL call 'views' or about the 'MS-Access pass-through' queries which are SQL queries? Someone could get easily lost! My solution is the following

1. free SQL Server Management Studio Express, where I will elaborate and test my queries
2. a query table on the client side, with one field for the query name (id_Query) and another one (queryText, memo type) for the query itself.

I then have a small function getSQLQuery in my VBA code to be used when I need to execute a query (either returning a recordset or not):

Dim myQuery as string, _
    rsADO as ADODB.recorset

rsADO = new ADODB.recordset
myQuery = getSQLQuery(myId_Query)

'if my query retunrs a recordset'
set rsADO = myADOConnection.Execute myQuery
'or, if no recordset is to be returned'
myADOConnection.Execute myQuery

For views, it is even possible to keep them on the server side and to refer to them from the client side

set rsADO = myADOConnection.execute "dbo.myViewName"

Answer 7

I guess I don't write complex SQL, because I don't have a problem with the Access SQL editor most of the time. This is because, for the most part, I use the QBE to write the SQL and only dip into the SQL view to do the things the QBE doesn't support (such as non-equi joins, some forms of subqueries, UNION, etc.). This is not to say that I don't have any SQL that is very hard to work with, but that's mostly because it's HIDEOUSLY BADLY WRITTEN, and that's my fault, not Access's fault. I have one horrid, appalling saved QueryDef in an app that's been in production since 1997 that has SQL that's 11,934 characters. And, yes, it's awful to troubleshoot. And nearly any edit I make to it breaks something. But that's because IT'S BAD SQL.

Why anyone would want to write their SQL by hand as a general rule, I can't say. For anything but the most trivial SQL, it seems to me like more trouble than it's worth.

This kind of thing seems to me like another case of people resisting the default Access way of doing things. Almost always, this comes about with users experienced in other programming environments who are too impatient to try things the way Access does them by default. The end result is usually unhappy for everybody.

--
David W. Fenton
David Fenton Associates

Answer 8

I have a few tips that are specific to SQL in VBA.

Always use a string variable:

NO:   DoCmd.RunSQL ("SELECT ...")
YES:  strSQL = "SELECT ..."
      DoCmd.RunSQL (strSQL)

This lets you isolate code better. When debugging, before the query is run insert

Debug.Print strSQL
Stop

Your SQL appears in the Immediate window, from which you can paste it into a QBE window ("query by example," the normal query-building interface outside of VBA) in SQL view. Debugging is now possible. A big plus is that your code-handled variables are ready-to-go in the string.

For a long query in VBA, break up your code as you would a long Message Box diatribe:

strSQL = "SELECT wazzle FROM bamsploot"  [underscore]
      & vbCrLf & "WHERE 1 > 2"

(NOTE: I can't place an underscore (_) without messing up my post. Thus the [placeholder].)

This makes your code more readable from the IDE, and also makes the Debug.Print rendition much more manageable. (Tiny other benefit: no space needed at end of each line, the CR builds that in.) You might think this also opens up adding comments, but unfortunately no.

As said elsewhere here, trips to a word processor are a time-saver (though cumbersome). Frequently while the code is in break mode, I go

VBA > QBE (described above) > Notepad (for global replace)
Notepad > QBE (for checking, troubleshooting) > VBA

A really sticky investigation involves several cycles as I move up in complexity; but this is rare.

The word processor also lets me clean up code generated by Access -- superfluous table references and the appalling redundancy of parentheses in the WHERE clause.

It's amusing how this web site's handling of SQL is ahead of the VBA editor in some ways. I learned SQL through VBA and seeing any smart formatting is still a shock.