By Renaud Bompuis

2009-01-07 02:16:51 8 Comments

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?

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


@John 2019-04-24 07:36:05

Well to my knowledge there are 2 options:

  • Notepad++ with Poor man's t-sql formatter plugin ..i know there is already a mention for SQL Pretty Printer but i haven't used my workflow is ..i create the query in Access..i copy paste it to Notepad++ ...i format it..i work on it ...back to Access..only pads in some cases spaces in this case : [Forms]![AForm].[Ctrl] and they become [Forms] ! [AForm].[Ctrl] but i am used to and it doesn't bother me..
  • SoftTree SQL Assistant ( bring just about everything you wanted on a SQL editor...i have worked a bit in the past(trial) but its price tag is a bit stiff

@Smandoli 2009-07-08 17:51:28

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

Put your SQL code with a string variable. I used to do this:

DoCmd.RunSQL "SELECT ..."

That is hard to manage. Do this instead:

strSQL = "SELECT ..."
DoCmd.RunSQL strSQL

Often you can't fix a query unless you see just what's being run. To do that, dump your SQL to the Immediate Window just before execution:

strSQL = "SELECT ..."
Debug.Print strSQL
DoCmd.RunSQL strSQL

Paste the result into Access' standard query builder (you must use SQL view). Now you can test the final version, including code-handled variables.

When you are preparing a long query as a string, break up your code:

strSQL = "SELECT wazzle FROM bamsploot" _
      & vbCrLf & "WHERE plumsnooker = 0"

I first learned to use vbCrLf when I wanted to prettify long messages to the user. Later I found it makes SQL more readable while coding, and it improves the output from Debug.Print. (Tiny other benefit: no space needed at end of each line. The new line syntax builds that in.)

(NOTE: You might think this will let you add add comments to the right of the SQL lines. Prepare for disappointment.)

As said elsewhere here, trips to a text editor are a time-saver. Some text editors provide better syntax highlighting than the official VBA editor. (Heck, StackOverflow does better.) It's also efficient for deleting Access cruft like superfluous table references and piles of parentheses in the WHERE clause.

Work flow for serious trouble shooting:

VBA Debug.Print >       (capture query during code operation)
  query builder   >     (testing lab to find issues)
     Notepad++      >   (text editor for clean-up and review)
  query builder   >     (checking, troubleshooting) 

Of course, trouble shooting is usually a matter of reducing the complexity of a query until you're able to isolate the problem (or at least make it disappear!). Then you can build it back up to the masterpiece you wanted. Because it can take several cycles to solve a sticky problem, you are likely to use this work flow repeatedly.

@transistor1 2014-07-23 16:58:36

I wrote Access SQL Editor-- an Add-In for Microsoft Access-- because I write quite a lot of pass-through queries, and more complex SQL within Access. This add-in has the advantage of being able to store formatted SQL (with comments!) within your Access application itself. When queries are copied to a new Access application, formatting is retained. When the built-in editor clobbers your formatting, the tool will show your original query and notify you of the difference.

It currently does not debug; if there was enough interest, I would pursue this-- but for the time being the feature set is intentionally kept small.

It is not free for the time being, but purchasing a license is very cheap. If you can't afford it, you can contact me. There is a free 14-day trial here.

Once it's installed, you can access it through your Add-Ins menu (In Access 2010 it's Database Tools->Add Ins).

@Renaud Bompuis 2014-07-24 15:03:43

Your plug in quite what I had in mind when I asked the question. It doesn't help directly for debugging, but it helps making SQL queries maintainable. (Full disclosure: @transistor1 offered me a license after I tried the plugin).

@emihir0 2016-08-15 14:11:40

Thank you very much - this is extremely helpful!

@Steve 2013-02-16 23:13:41

Expanding on this suggestion from Smandoli:

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

If you want to keep the SQL code in an external file, for editing with your favorite text editor (with syntax coloring and all that), you could do something like this pseudo-code:

// On initialization:
global strSQL
f = open("strSQL.sql")
strSQL = read_all(f)

// To to the select:

This may be a bit clunky -- maybe a lot clunky -- but it avoids the consistency issues of edit-copy-paste.

Obviously this doesn't directly address debugging SQL, but managing code in a readable way is a part of the problem.

@David-W-Fenton 2009-01-09 16:55:52

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.

@Smandoli 2009-07-08 20:56:27

David, I'm curious. I infer you don't run SQL through VBA, that is, run it apart from a query object. I don't compose SQL "by hand," but there are lots of occasions to take my successful pilot query and "codify" it into a module. The most obvious benefits are saving on "object clutter" in the DB Window and having everything on one "page" where I can see it. I think above all it lets me "mass customize" or configure for parameters. Interested in your thoughts on that.

@David-W-Fenton 2009-07-08 21:39:05

I run the vast majority of my SQL through VBA. That doesn't mean I write it by hand. Why would you make the assumption that because I recommend using the QBE to help write the SQL that I would only ever use the SQL in that context? Use the tool for what it's good for, and that's producing well-formatted, working SQL with the least work.

@Smandoli 2009-07-09 13:47:48

My inference was reasonable. Thanks for satisfying my curiosity. I think the original question is a good one. I'm sure you occasionally have to debug queries and your post doesn't say how you do that.

@David-W-Fenton 2009-07-09 23:28:14

I don't think the inference was reasonable at all. It's based on your supplying a lot of assumptions. In re: debugging SQL, I'm not sure I understand the question. Occasionally, with SQL that has complex expressions in it, I will debug them in Notepad, but that's not really SQL debugging so much as expression debuggin (e.g. ugly nested IIf() statements). In most cases, that's a clue to me that I ought to move that kind of logic out of the SQL entirely and into a user-defined function. I just recently did that with the bad SQL I alluded to and now it's only 7223 characters long! Yippee!

@Smandoli 2009-07-12 03:35:08

I think we have some running confusion on whether we're addressing syntax (or runtime) errors or logical errors. Obviously a great way to avoid the former is to let Access build the query whenever you can, which seems to have been your point. I benefit from your posts on this forum and I stand by my inference.

@David-W-Fenton 2009-07-12 20:08:06

Maybe we're no longer talking about the same inference? You assumed that I don't use SQL in code. This was an unwarranted assumption under any circumstances because nothing in my posts in this forum or any of the dozens of other Access forums where I have been a participant for nearly 15 years should lead you to that assumption.

@onedaywhen 2009-07-13 08:37:37

Using SQL in VBA code is nothing to be proud of, IMO. For me, SQL belongs in the 'back end' (BE) i.e. stored procs only. Sure, this goes against the grain of bound controls on Access Forms but, as you like to point out yourself, nothing in the question implies anything other than an Access database engine BE, in which case your 'move SQL logic to VBA user-defined functions' will not work if the 'front end' is anything other than Access.

@David-W-Fenton 2009-07-13 21:41:21

If your back end is Jet/ACE, then your advice to have the SQL in the back end is nonsense. As to UDFs to simply SQL, it works perfectly well with any back end if you're accessing it via ODBC. Perhaps you're not getting the context -- I'm talking about SQL in Access that uses Access/Jet functions, and simplying that by converting it to a VBA UDF. An example would be here:

@Philippe Grondier 2009-01-07 10:38:55

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"

@eksortso 2009-01-07 06:26:37

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.

@eksortso 2009-01-07 05:21:14

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.

@EdgarVerona 2009-01-07 02:22:53

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

@recursive 2009-01-07 02:20:24

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.

@dkretz 2009-01-07 02:44:56

yup - cut and paste ti vim.

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

27 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2392520 View
  • 1630 Score
  • 27 Answer
  • Tags:   sql duplicates

35 Answered Questions

[SOLVED] How do I debug Node.js applications?

42 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

33 Answered Questions

[SOLVED] How to query MongoDB with "like"?

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

37 Answered Questions

Sponsored Content