running a query from a text box

[Tony]

Hi Group,

I have sql stored in a memo field called 'searchmethod'.
Can I run this sql from a command button or double-click
to show the results of the saved sql?

Thanks

Tony

 

[Ken Snell]

To run a stored query by clicking a command button,
DoCmd.OpenQuery "queryname"

Assuming that your SQL is not for an action query but for a select query,
you'd need to create a new querydef using that SQL statement, then open it
via the above code line, then delete it when you're all done with it. If you
want more info on this, post back.

 

[Tony]

Hi Ken,

Thank you for you reply.
The field does indeed store select queries, which I need
to regularly view without having to copy and paste SQL
into the query grid and then running from there.
Could I take you up on the offer of providing more info
on 'creating a new querydef'.

Cheers

Tony

 

[Ken Snell]

Here's some starter code to read an SQL

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set dbs = CurrentDb()
strSQL = DLookup("FieldNameWithSQL", "TableName", "[SomeFieldName] = " &
SomeVariableValue)
Set qdf = dbs.CreateQueryDef("tempQueryName", strSQL)
DoCmd.OpenQuery qdf.Name
Set qdf = Nothing
dbs.Close
Set dbs = Nothing


' do things here


' code to delete the temporary query
' (should be done in separate code procedure,
' so you may need to replace qdf.Name with
' actual query name)
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("tempQueryName")
dbs.QueryDefs.Delete qdf.Name
Set qdf = Nothing
dbs.Close
Set dbs = Nothing