Change SQL Behind Query On The Fly Without DAO 1

[Jables]

I am using a query embedded on a form as a SourceObject and it's a Crosstab query that's based on another query. That's why the SQL of the base query itself needs to change instead of just using a filter on the subform.

I need to alter the WHERE clause of the base query when a button is clicked on the master form. I can do it using the SQL property of the QueryDef object, but I don't want to use DAO.

 

[Rolliee]

I haven't time to code this again. Send me you email for a similar example.

rollie@bwsys.net

 

[Jables]

Here is more detail about what I need to do.

Say I have a query, qry1, on a form. It is on the form as an embedded source object and not as a subform based on qry1. When a button is clicked on the master form, I want to change the SQL behind qry1. I know how to do this using DAO, but is there a way to do it using ADO?

 

[MichaelRed]

If, by "embedded' you mean it is a string as the property of a control, you can just load the property into a string, manipulate it as necessary, replace hte property and refresh the form.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Jables]

Well, the name of the query is a property of the control. To my knowledge, there is no property that contains the SQL of the query.

SourceObject = "Query.Query_Name"

That is the string property of the control that I am referring to.

Is there a way to access the query's SQL property through the MS Access object model?

 

[MichaelRed]

If, by "embedded' you mean it is a string ...

When you set the property o the NAME of the query you are (from MY perspective) not "Embedding" it, but just referencing/instantiating the "stored" query.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Jables]

Perhaps it is incorrect to refer to it as embedded. The way I get the query onto the form is just to drag it from the database window and drop it onto the form.

But the bottom line is that I am wondering if there is a way to click a button on the form and alter the SQL behind the query itself without using the QueryDef object within the DAO 3.6 library.

Is there a way to do it via the MS Access object library?

 

[PaulF]

This is an example from a paper titled

Migrating from DAO to ADO
Using ADO with the Microsoft Jet Provider

Alyssa Henry
Microsoft Corporation

July 1999
and found on the Microsoft website

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

In the ADO code setting the Procedure object's Command property to the modified Command object saves the changes. If this last step were not included, the changes would not have been persisted to the database. This difference results from the fact that ADO Command objects are designed as temporary queries, while DAO QueryDef objects are designed as saved queries. You need to be aware of this when working with Commands, Procedures, and Views.


Hope this helps

PaulF

 

[Trevahaha]

Do you know why this would take so much longer? When I change the code to use ADO it seems to be taking 3 times longer to execute. I know it's a huge query I'm dealing with, but why would the changing of the SQL through DAO be faster than ADO?