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

Deleting a Stored Query

   <source lang="vb">

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

</source>
   
  

Executing an Update Query

   <source lang="vb">

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

</source>
   
  

Executing an Update Query using the Command Object

   <source lang="vb">

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

</source>
   
  

Get the updated record count

   <source lang="vb">

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

</source>
   
  

Listing All Saved Queries in a Database

   <source lang="vb">

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

</source>