HandsOn Modifying a Select Query

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Modify_Query procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

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 & "\Northwind.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

' get the existing SQL statement for this query oldStrSQL = cmd.CommandText

MsgBox oldStrSQL, vbInformation, _ "Current SQL Statement"

' now update the query's SQL statement cmd.CommandText = newStrSQL MsgBox newStrSQL, vbInformation, _ "New SQL Statement"

' save the modified query

Set cat.Views(strQryName).Command = cmd

Set cmd = Nothing Set cat = Nothing End Sub

Creating and Manipulating Databases with ADO

0 0

Post a comment