Query Maker (v2000)

Topic

New Reply

I have a SQL statement that is generated in VBA as a user completes fields on a form. A report then opens and the resulting SQL code is loaded as the report’s Record Source. It runs quite nicely. I have no trouble updating a form or report using a SQL statement from VBA. However, I recently came across another need…how can I update a query with the resulting SQL statement. Once the SQL statement is created and the form is closed, I will still need to recall the SQL until the next time the form is used. As a result, I thought updating a saved query could do this for me but I don’t know how to update a query in code. If there’s a different approach, I’m all ears.
Thanks!
Dashiell

Reply | Quote

Replies

A querydef object has a SQL property, which you can set in code to your new SQL string, if that’s what you are asking.

Reply | Quote

If I want to update qrySample with the following VBA:
MySQL = “SELECT tblClients.ClientName FROM tblClients WHERE (((tblClients.ClientName) Like “John*”));”
How would I do it?
Thanks Charlotte!

Reply | Quote

Try this:
Dim Qdf as DAO.QueryDef
MySQL = “SELECT tblClients.ClientName FROM tblClients WHERE (((tblClients.ClientName) Like “John*”));”
Set Qdf = CurrentDB.QueryDefs(“qrySample”)
Qdf.SQL = MySQL
Set Qdf = Nothing

Reply | Quote

That worked great. Thanks Pat!!

Reply | Quote

That worked great. Thanks Pat!!

Reply | Quote

Try this:
Dim Qdf as DAO.QueryDef
MySQL = “SELECT tblClients.ClientName FROM tblClients WHERE (((tblClients.ClientName) Like “John*”));”
Set Qdf = CurrentDB.QueryDefs(“qrySample”)
Qdf.SQL = MySQL
Set Qdf = Nothing

Reply | Quote

If I want to update qrySample with the following VBA:
MySQL = “SELECT tblClients.ClientName FROM tblClients WHERE (((tblClients.ClientName) Like “John*”));”
How would I do it?
Thanks Charlotte!

Reply | Quote

A querydef object has a SQL property, which you can set in code to your new SQL string, if that’s what you are asking.

Reply | Quote