How to retrieve SQL from query without DAO

[krymat]

I do know how to do this with DAO, and I don't believe I can do this with ADO. Is there another way?

 

[scriverb]

I don't believe so except manually and I don't think that is what you were thinking of. Why don't you explain your problem and then maybe someone can come up with a solution that you aren't think of. Bob Scriver

 

[krymat]

No particular reason. Just curious of other methods.

 

[danvlas]

You can inspect the .Command property of a Procedure or View object...

Check

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

Good luck


Daniel Vlas
Systems Consultant

 

[krymat]

This doesn't work with stored queries

 

[scriverb]

DAO vs ADO modification of .sql property of a stored query.
DAO Example
Sub DAOModifyQuery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb&quot
' Get the query
Set qry = db.QueryDefs("Employees by Region&quot
' Update the SQL and save the updated query
qry.SQL = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
db.Close
End Sub
ADO Example
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=.\NorthWind.mdb;"
' Get the query
Set cmd = cat.Procedures("Employees by Region&quot.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&quot.Command = cmd
Set cat = Nothing
End Sub

The above code was found in the link supplied by danvlas above. Look under Defining and Retrieving database's Schema - - Creating and Modifying Queries

That link is loaded with code comparing DAO vs ADO with full explainations. Sorry, about my first response as I failed to read your comment about ADO.

Let me know if you need more help. Bob Scriver

 

[krymat]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adocreateq.asp

"Although it is possible to create and modify a stored query in an Access database by using Microsoft ActiveX® Data Objects Extensions for Data Definition Language and Security (ADOX), if you do so your query won't be visible in the Access Database window or in any other part of the Access user interface—for example, you can't set the RecordSource property of a form to a query created with ADOX, nor can you import a query created with ADOX into another database. However, you can still run stored queries created by using ADOX from ADO code."

What I meant by stored query in my previous post was a query viewable and editable by end-user in the Access user interface.

 

[danvlas]

I thought you were trying to read the sql, not create or modify it.

As an alternative:
Use the MSysQueries table as Michael Redd suggested in one recent post.

Detect the query ID from MSysObjects table on the basis of the query name and type (5)

Open an ADO recordset based on MSysQueries filtered by ObjectID.

Play a little with the Attributes column: source table(s) are marked as 5, fields are marked with 6, joins with 7, conditons with 8, it's up to you to find 'group by', 'having' and anything else.

I have not yet used it, but it's something that I keep in mind...just in case.

You can rebuild the SQL of the query in this way...

And you use an ADO connection after all

Good luck

Daniel Vlas
Systems Consultant