Rich said:
I am trying to change a field name in a query using ado or adox. It seems this is not possible if the query is already in the querydef collection. I have not seen a way to do it in ADOX either. Is this right?
Hi Rich,
If you are trying to modify a stored query in ADO, the following
compares the DAO and ADO code for modifying a stored query:
http://msdn.microsoft.com/library/techart/daotoadoupdate_topic6.htm
in case this link no longer works, here be what I think you wanted:
***quote***
Modifying a Stored Query
The following listings demonstrate how to modify an existing query.
DAO
Sub DAOModifyQuery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Get the query
Set qry = db.QueryDefs("Employees by Region")
' 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
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").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
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. You may think that the following ADO code examples are equivalent:
Set cmd = cat.Procedures("Employees by Region").Command
cmd.CommandText = "PARAMETERS [prmRegion] TEXT(255);" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
Set cat.Procedures("Employees by Region").Command = cmd
and:
cat.Procedures("Employees by Region").CommandText = _
"PARAMETERS [prmRegion] TEXT;" & _
"SELECT * FROM Employees WHERE Region = [prmRegion] " & _
"ORDER BY City"
However, they are not. Both will compile, but the second piece of code will not actually update the query in the database. In the second example ADOX will create a tear-off command object and hand it back to Visual Basic for Applications. Visual Basic for Applications will then ask ADOX to update the CommandText property, which it does. Finally, Visual Basic for Applications moves to execute the next line of code and the Command object is lost. ADOX is never asked to update the Procedure with the changes to the modified Command object.
****unquote****
Please respond back if I have misunderstood.
Good luck,
Gary Walter
