ADO QueryDef Equivalent?

[Bruce Castle]

I am attempting to retrieve the SQL string from each of my Queries in
Access 2000. It was fairly straightforward in Access 97 using the
properties of Querydef. In ADO (in Access 2000) Querydef does not seem
to be recognized. The Access 2000 help system has not been a great help
in my quest for this information.

I will continue my quest. However, if anyone can shed any light on this
matter it would be greatly appreciated.

Thanks,

Bruce

[Steve Thompson]

There is no direct equivalent to opening the Querydef property of the
query.

However you can get the SQL by opening an ADO recordset and using the
Source property.

The ADO v2.1 help is documented in 'Ado210.Chm', if it's not on your
hard disk you may be able to find it on Microsoft's site.

Steve

[Bruce Castle]

Steve,

Thanks for the information.

I found this procedure on the Microsoft Web site under
http://www.microsoft.com/data/ado/adotechinfo/dao2ado_5.htm
which is a multipage paper regarding the differences between DAO and ADO. It
shows how to modify the SQL therefore it should enable me to retrieve it.

(This was on page 5)

Sub ADOModifyQuery()

Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command

'Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\nwind.mdb;"

'Get the query
Set cmd = cat.Procedures("Employees by Region").Command

'Update the SQL
cmd.CommandText = "Parameters [prmRegion] Text(255);" & _
"Select * from Employees where Region = [prmRegion] ORDER BY City"

'Save the updated query
Set cat.Procedures("Employees by Region").Command = cmd
Set cat = Nothing
End Sub

Thanks again for such a speedy response,

Bruce