468,770 Members | 2,424 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

How to Debug SQL String

32,131 Expert Mod 16PB

One of the most popular (frequently occurring rather than best liked) problems we get here is with SQL strings being created/manipulated in VBA code. This is also true in other coding environments, but I'll work here with the VBA situation.


The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code, which in turn is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.


A technique I often suggest to coders struggling with this (Coders of any level - This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it. The benefit of the Immediate Pane option over the MsgBox() is that it can then be copied and pasted - into a post in Bytes for instance.

Another alternative, assuming you're relatively comfortable with Debugging in VBA, is simply to trace through (You can set up a breakpoint to do this) to the line that will execute the SQL string, then you can look at the string value directly (in various ways in Debug mode - including printing into the Immediate Pane).

A third thing to try, because sometimes the behaviour and error messages seen when running via VBA are different from those seen running a full QueryDef (Saved Query in an Access database), is to take the SQL printed and put it into a QueryDef object and seeing what happens when you try to run it. To do this it is only necessary to create a QueryDef and, when it is opened for design, switch to SQL view and paste your copied SQL string in place of what's already there. Run it from there to see what happens. You may, or may not, get more information that way than you get from trying to execute it from VBA.

It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety and in its true form, rather than as the code is about to create it.

This is also a much better way to ask SQL questions on a forum. No-one is going to appreciate being asked a SQL question about your posted VBA code.
Jul 23 '11 #1
2 11558
8,800 Expert 8TB
As a compliment to your Informative Post, I am including a Utility that will assist in the conversion of SQL Strings to their VBA Code counterparts. I realize that it will probably be of no Value to you, but it may be to others.
Jul 23 '11 #2
Thanks for the info on debugging SQL in VBA.
Mar 5 '14 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

97 posts views Thread by s | last post: by
4 posts views Thread by emma middlebrook | last post: by
12 posts views Thread by Micah | last post: by
10 posts views Thread by mr_sorcerer | last post: by
reply views Thread by BA | last post: by
3 posts views Thread by =?Utf-8?B?ZWxhZGxh?= | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.