|
Sub Modify_Query()
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strPath As String
Dim newStrSQL As String
Dim oldStrSQL As String
Dim strQryName As String
strPath = CurrentProject.Path & "\mydb.mdb"
newStrSQL = "SELECT Employees.* FROM Employees" & _
" WHERE Employees.City='London'" & _
" ORDER BY BirthDate;"
strQryName = "London Employees"
Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
Set cmd = New ADODB.Command
Set cmd = cat.Views(strQryName).Command
oldStrSQL = cmd.CommandText
Debug.Print oldStrSQL
cmd.CommandText = newStrSQL
Debug.Print newStrSQL
Set cat.Views(strQryName).Command = cmd
Set cmd = Nothing
Set cat = Nothing
End Sub
|