reading sql from query properties?

[Matthew Snook]

I have tried unsuccessfully to find a way to programmatically read the sql statement of permanent queries. Is this possible?

I have even tried setting a variable as an object, then looping through the objects in the querydefs collection to search for a certain query: this I can do, but the sql statement is not available as one of the properties that can be returned.

Any suggestions?


Matt

 

[bjackson]

have you tried to open the query in code the
use its sql
Dim dbs As DAO.Database
Dim Rst As DAO.Recordset
dim MyQueryName as String
Dim StrSql as string

Set dbs = CurrentDb()
Dim QDF As DAO.QueryDef
Dim PRM As Parameter


MyQueryName="thenameof the queryyourreportisbasedon"


Set QDF = dbs.QueryDefs(MyQueryName)
For Each PRM In QDF.Parameters
PRM.Value = Eval(PRM.name)
Next PRM
Set Rst = QDF.OpenRecordset(dbOpenDynaset)
With Rst

StrSql=QDF.Sql
End With
Rst.Close
Set Rst = Nothing
Set dbs = Nothing

 

[Tim K.]

You can check this link out as well.

 

[Matthew Snook]

Followup

Thanks for the help! It definitely put me on the right track, after a few changes... Here's what worked:

Given:
1) a button named 'btn_Read_Query';
2) a MSForms.ListBox named 'list_query', which contains a list
of queries from the database;

Private Sub btn_Read_Query_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Dim MyQueryName As String
'set the variable to equal the list box selection
MyQueryName = list_query.Value
Dim QDF As DAO.QueryDef

'This if-section handles the special case
'that occurs when the list box default "Not Found" is selected
'instead of one of the query names pulled from the database.
If MyQueryName = "Not Found" Then
MsgBox "It says 'Not Found'--What am I supposed to do?"
Else
'pull all query defs for this particular query
Set QDF = dbs.QueryDefs(MyQueryName)
'Write the SQL to my text box
Me.txt_SQL = QDF.SQL
End If

'Reset initial conditions
Set dbs = Nothing

End Sub



Here's what didn't work:

Dim dbs As DAO.Database
Set dbs = CurrentDb()
Dim Rst As DAO.Recordset
Dim MyQueryName As String
MyQueryName = list_query.Value
Dim StrSql As String
Dim QDF As DAO.QueryDef
Dim PRM As Parameter


Private Sub Read_SQL()
If MyQueryName = "Not Found" Then
MsgBox "It says 'Not Found'--What am I supposed to do?"
Else
Set QDF = dbs.QueryDefs(MyQueryName)
For Each PRM In QDF.Parameters
'this next line provided various errors depending upon the query
'some queries passed just fine, others with special criteria
'or calculated expressions failed with numerous different errors
PRM.Value = Eval(PRM.Name)
Next PRM
'this line also returned various errors depending upon the
'particular query, causing me to use the simpler method above
QDF.SQL = Eval(QDF.SQL)
Me.SQL = QDF.SQL
End If
Set Rst = QDF.OpenRecordset(dbOpenDynaset)
With Rst

Me.SQL = QDF.SQL
End With
Rst.Close
Set Rst = Nothing
Set dbs = Nothing
end sub

Thanks again! This is a great forum.

Matt