Hab da was gefunden aber noch nicht ausprobiert:
Modifying a Query
To modify a query, retrieve the Command object for the query from either the Views or Procedures collection, update the CommandText property setting for the query, then save the query back to the appropriate collection. The following code example shows how to modify an existing query.
Sub ModifyQuery(strDBPath As String, _
strQryName As String, _
strSQL As String)
Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command
Set catDB = New ADOX.Catalog
’ Open the Catalog object.
catDB.ActiveConnection = „Provider=Microsoft.Jet.OLEDB.4.0;“ & _
„Data Source=“ & strDBPath
Set cmd = New ADODB.Command
’ Get the query from the Procedures collection.
Set cmd = catDB.Procedures(strQryName).Command
’ Update the query’s SQL statement.
cmd.CommandText = strSQL
'Save the updated query.
Set catDB.Procedures(strQryName).Command = cmd
Set catDB = Nothing
End Sub
For example, to use this procedure to update the parameter query created by the previous example so that it now sorts by the City field, you’d use a line of code like this:
ModifyQuery „c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb“, _
„Employees by Region“, „PARAMETERS prmRegion Text;“ _
& „SELECT * FROM Employees WHERE Region = prmRegion ORDER BY City;“
The ModifyQuery procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the Samples\CH14 subfolder on the companion CD-ROM to the Microsoft Office 2000/Visual Basic Programmer’s Guide (Microsoft Press, 1999).
Note that the process of updating a saved query when you are using ADO and ADOX is somewhat different from the process when you are using DAO. In DAO, QueryDef objects are designed as saved queries, so opening and changing an existing QueryDef object’s sqltext argument automatically saves the changes to the saved query. In ADO, a Command object is inherently a temporary object. You need to be aware of this when you are working with Command objects and the Procedures and Views collections. For example, you may think that the following ADO code fragments are equivalent
Set cmd = catDB.Procedures(„Employees by Region“).Command
cmd.CommandText = „PARAMETERS prmRegion Text;“ & _
„SELECT * FROM Employees WHERE Region = prmRegion ORDER BY City“
Set catDB.Procedures(„Employees by Region“).Command = cmd
and:
catDB.Procedures(„Employees by Region“).CommandText = _
„PARAMETERS prmRegion Text;“ & _
„SELECT * FROM Employees WHERE Region = prmRegion ORDER BY City“
However, they aren’t. Both will compile, but the second piece of code won’t actually update the query in the database. In the second example, ADOX will create a temporary Command object and update its CommandText property, but the Command object will not be saved to the Procedures collection.
Hoffe es hilft
Mfg Andy
