ADO equivalent to QueryDef sql property

[Brian]

HI

In DAO I can retrieve the actual SQL string that makes up
a query by doing the following:

Dim qrs as QueryDef
Dim strSQL as string
Set qrs = CurrentDb.QueryDefs("qryReportsGeneric")
strSQL = qrs.SQL

The value of strSQL is then "SELECT * FROM..."

However, I seem to be unable to do this using ADO. I've
tried many different ways using both command and
recordset objects and cannot get this string. Is there
an ADO equivalent to the QueryDef SQL property?

Any help would be greatly appreciated.

Brian

 

[Alastair MacFarlane]

Brian,

ADO uses a parameters collection whereas in DAO you use
QueryDef. In ADO append a parameter to the collection
specifying the datatype and then the value and run the
command. The resulting recordset object will be yours to
do with as you wish.

There are many articles in the knowledgebase on this
subject.

I hope this helps.

Alastair MacFarlane.

 

[Terry Kreft]

Create a reference to msadox.dll, then use something like

' ***********************
' Code Start
Function QuerySQLADO(QueryName As String) As String
Dim loCon As ADODB.Connection
Dim loCom As ADODB.Command
Dim loExt As ADOX.Catalog
Dim loProc As ADOX.Procedure
Dim loView As ADOX.View

Set loCon = Access.CurrentProject.Connection

Set loExt = New ADOX.Catalog
With loExt
Set .ActiveConnection = loCon
On Error Resume Next
Set loProc = .Procedures(QueryName)
If Err <> 0 Then
Set loView = .Views(QueryName)
Set loCom = loView.Command
Else
Set loCom = loProc.Command
End If
End With
QuerySQLADO = loCom.CommandText
End Function
' Code Start
' ***********************

Terry