View parameterized query's SQL after setting parameters

Question

The problem: I am looking for a way to view a parameterized query's SQL after the parameters have been set in VBA.

What I have tried:

Public Function test()

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef

    Set db = CurrentDb
    Set qd = db.QueryDefs![1Para]

    qd.Parameters("ID").Value = 5

    Debug.Print qd.SQL

    Call qd.Close

End Function

Actual output:

SELECT * FROM table1 WHERE table1.ID = [ID]

Desired output:

SELECT * FROM table1 WHERE table1.ID = 5

I have looked for this answer online for a while but turned up nothing. That may indicate that this isn't possible but I thought I would ask here just in case there is something I missed.

If you revise the query's SQL to SELECT * FROM table1 WHERE table1.ID = [pID], you could then do Debug.Print Replace(qd.SQL, "[pID]", qd.Parameters("pID").Value) I don't know any other way to get what you want. — HansUp
– HansUp, Commented Jun 30, 2015 at 15:20
That is a very neat solution, though anything beyond a simple single parameter query and I don't think it would work. I feel like what I had wanted isn't really easily, wishful thinking I guess. — Newd
– Newd, Commented Jun 30, 2015 at 15:24
Basically the problem you're up against here is that supplying parameter values does not update the query SQL to include those values. You might look into ODBC logging or ShowPlan, but I'm not sure either of those would show you the SQL with parameter values inserted into the SQL. — HansUp
– HansUp, Commented Jun 30, 2015 at 15:24
@HansUp I figured that was the case, I just thought something so simple would have been implemented in the QueryDefs object. It isn't an answer that I really NEED, more so something that just piqued my interested from another question. Thanks anyway! — Newd
– Newd, Commented Jun 30, 2015 at 15:26

Newd · Accepted Answer · 2015-07-09 16:21:46Z

1

The answer seems to be not really.

answered Jul 9, 2015 at 16:21

Newd

2,1832 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

View parameterized query's SQL after setting parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related