Go to Top

Code to help you debug SQL code – POPQuery

I use SQL in my code a lot, I’m not a fan of creating queries and then referencing them in my code  since users may delete or change them.

Often I build SQL strings and then I need to debug them in the query Access grid, in the past I would get the value of my SQL string in the immediate window and paste the SQL in a new query window. I got tired of doing it all the time so I created a small function that will do it for me:

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

Public Function PopQuerySQL(strSQL As String, strQueryName As String)
Dim qdf As DAO.QueryDef
On Error GoTo PopQuerySQL_Error
DoCmd.Close acQuery, strQueryName
DoCmd.DeleteObject acQuery, strQueryName
Set qdf = CurrentDb.CreateQueryDef(strQueryName, strSQL)
Set qdf = Nothing
DoCmd.OpenQuery strQueryName, acViewNormal
On Error GoTo 0
Exit Function
PopQuerySQL_Error:
If Err.Number = 7874 Then
Resume Next ‘Query does not exist
End If
MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in procedure PopQuerySQL of Module mdlAPI”
End Function
I now use the code with my SQL variable in the immediate window:
PopQuery strSQL, “qryTemp”
It saves me a lot of time and I hope it does the same for you!
Juan

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

2 Responses to "Code to help you debug SQL code – POPQuery"

  • Nicanor
    May 25, 2013 - 1:39 am Reply

    In a VBA module

    Example:

    Ok = PopQuerySQL( “Select Top 1 From tbCustomers”, “e_QryTemp”

    or

    Ok = PopQuerySQL( “Select sum(Qty) as Tot From tbSalesDetail”, “e_QryTemp”

  • Diego
    April 6, 2013 - 12:05 pm Reply

    Hey Juan,

    I tried to use your function but I still don’t get where should I put the line PopQuery strSQL, “qryTemp”.

    Can you explain a little bit?

    Thanks,

    Diego

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]