VBA/Excel/Access/Word/Access/SQL Update

Deleting a Stored Query

 
Sub Delete_Query()
   Dim cat As New ADOX.Catalog
   Dim strPath As String
   On Error GoTo ErrorHandler
   strPath = CurrentProject.Path & "\mydb.mdb"
   cat.ActiveConnection = "Provider=Microsoft.Jet.OleDb.4.0;" & _
      "Data Source= " & strPath
   cat.Views.Delete "London Employees"
ExitHere:
   Set cat = Nothing
   Exit Sub
ErrorHandler:
   If Err.Number = 3265 Then
      MsgBox "Query does not exist."
   Else
      MsgBox Err.Number & ": " & Err.Description
   End If
   Resume ExitHere
End Sub

Executing an Update Query

 
Sub Execute_UpdateQuery()
   Dim conn As ADODB.Connection
   Dim NumOfRec As Integer
   Dim strPath As String
   strPath = CurrentProject.Path & "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
   conn.Execute "UPDATE Products SET UnitPrice = UnitPrice + 1" & _
      " WHERE CategoryId = 8", NumOfRec, adExecuteNoRecords
   Debug.Print NumOfRec & " records were updated."
   conn.Close
   Set conn = Nothing
End Sub

Executing an Update Query using the Command Object

 
Sub Execute_UpdateQuery2()
   Dim cmd As ADODB.rumand
   Dim NumOfRec As Integer
   Dim strPath As String
   strPath = CurrentProject.Path & "\mydb.mdb"
   Set cmd = New ADODB.rumand
   With cmd
      .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
      .rumandText = "Update Products Set UnitPrice = UnitPrice *1.1"
      .Execute NumOfRec, adExecuteNoRecords
   End With
   MsgBox NumOfRec
   Set cmd = Nothing
End Sub

Get the updated record count

 
Sub Execute_UpdateQuery()
   Dim conn As ADODB.Connection
   Dim NumOfRec As Integer
   Dim strPath As String
   strPath = CurrentProject.Path & "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
   conn.Execute "UPDATE Products SET UnitPrice = UnitPrice + 1" & _
      " WHERE CategoryId = 8", NumOfRec, adExecuteNoRecords
   Debug.Print NumOfRec & " records were updated."
   conn.Close
   Set conn = Nothing
End Sub

Listing All Saved Queries in a Database

 
Sub List_SavedQueries()
   Dim cat As New ADOX.Catalog
   Dim v As ADOX.View
   Dim strPath As String
   strPath = CurrentProject.Path & "\mydb.mdb"
   cat.ActiveConnection = "Provider=Microsoft.Jet.OleDb.4.0;" & _
      "Data Source= " & strPath
   For Each v In cat.Views
      Debug.Print v.Name
   Next
   Set cat = Nothing
End Sub