VBA/Excel/Access/Word/Access/Recordset Add Update Delete

Adding a New Record to a Recordset

   <source lang="vb">

Sub cmdAddADO()

   Dim rst As ADODB.Recordset
       Set rst = New ADODB.Recordset
       With rst
           .ActiveConnection = CurrentProject.Connection
           .CursorType = adOpenKeyset
           .LockType = adLockOptimistic
           .Open "Select * from Employees Where EmployeeID = 0"
           .AddNew
               !FirstName = "newF"
               !LastName = "newL"
               !Region = "new"
           .Update
       End With

End Sub

</source>
   
  

Adding a New Record to a Table

   <source lang="vb">

" Use the References dialog box to set up a reference to the Microsoft ActiveX Data Objects Library Sub Add_Record()

  Dim conn As ADODB.Connection
  Dim myRecordset As ADODB.Recordset
  Dim strConn As String
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
  Set myRecordset = New ADODB.Recordset
  With myRecordset
     .Open "Select * from Employees", _
        strConn, adOpenKeyset, adLockOptimistic
     .AddNew
     !LastName = "Marco"
     !FirstName = "Paulo"
     !City = "Boston"
     .MoveFirst
     .Close
  End With
  Set myRecordset = Nothing
  Set conn = Nothing

End Sub

</source>
   
  

Add new a row to recordset

   <source lang="vb">

Sub exaRecordsetAddNew()

   Dim db As Database
   Dim rs As Recordset
   
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Books")
   Debug.Print "Current title: " & rs!Title
   With rs
      .AddNew             " Add new record
      !ISBN = "0-000"     " Set fields
      !Title = "New Book"
      !PubID = 1
      !Price = 100
      .Update             " Save changes.
      .Bookmark = rs.LastModified   " Go to new record
      Debug.Print "Current title: " & rs!Title
   End With
   
   rs.Close

End Sub

</source>
   
  

Call delete method in Recordset

   <source lang="vb">

Public Sub RemoveCompany()

 Dim rst As ADODB.Recordset
 Dim strSQL As String
 Set rs = New ADODB.Recordset
 rs.Open "SELECT * FROM tblCompany WHERE CompanyID=14", CurrentProject.Connection, adOpenStatic, adLockOptimistic
 With rs
   If .RecordCount > 0 Then
     .Delete
   End If
 End With
 rs.Close
 Set rs = Nothing

End Sub

</source>
   
  

Call update method from Recordset

   <source lang="vb">

Public Sub ADOUpdate()

 Dim rs As ADODB.Recordset
 Dim strSQL As String
 
 Set rs = New ADODB.Recordset
 strSQL = "SELECT CompanyName, Address, City FROM tblCompany WHERE (CompanyName = "Liams Diner")"
 
 rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
 
 With rs
   !CompanyName = "Diner"
   .Update
 End With
 rs.Close

End Sub

</source>
   
  

Check the Recordset affected

   <source lang="vb">

Sub exaCreateAction2()

   Dim ws As Workspace
   Dim db As Database
   Dim qdf As QueryDef
   Dim strSQL As String
   Set ws = DBEngine(0)
   Set db = CurrentDb
   strSQL = "UPDATE BOOKS SET Price = Price*1.1 WHERE Price > 20"
   Set qdf = db.CreateQueryDef("PriceInc", strSQL)
   ws.BeginTrans
   qdf.Execute
   If qdf.RecordsAffected > 15 Then
       Debug.Print qdf.RecordsAffected
       ws.Rollback
   Else
       Debug.Print qdf.RecordsAffected
       ws.rumitTrans
   End If
   

End Sub

</source>
   
  

Delete row for a certain criteria

   <source lang="vb">

Sub exaRecordsetDelete()

   Dim db As Database
   Dim rs As Recordset
   Dim DeleteCt As Integer
   
   Set db = CurrentDb
   Set rs = db.OpenRecordset("Books Copy")
   
   DeleteCt = 0
   
   rs.MoveFirst
   Do While Not rs.EOF
      If rs!Price > 20 Then
            rs.Delete
            DeleteCt = DeleteCt + 1
      End If
      rs.MoveNext
   Loop
   
   rs.Close
   
   Debug.Print Format$(DeleteCt) & " records deleted."

End Sub

</source>
   
  

Deleting a Record

   <source lang="vb">

Sub Delete_Record()

  Dim conn As ADODB.Connection
  Dim myRecordset As ADODB.Recordset
  Dim strConn As String
  Dim strCriteria As String
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
  Set conn = New ADODB.Connection
  Set myRecordset = New ADODB.Recordset
  With myRecordset
     .Open "Select * from Employees Where LastName ="Marco"", _
         strConn, adOpenKeyset, adLockOptimistic
     .Delete
     .Close
  End With
  Set myRecordset = Nothing
  Set conn = Nothing

End Sub

</source>
   
  

Modifying a Record

   <source lang="vb">

Sub Update_Record()

  Dim conn As ADODB.Connection
  Dim myRecordset As ADODB.Recordset
  Dim strConn As String
  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
  Set myRecordset = New ADODB.Recordset
  With myRecordset
     .Open "Select * from Employees Where LastName = "Marco"", _
        strConn, adOpenKeyset, adLockOptimistic
     .Fields("FirstName").Value = "A"
     .Fields("City").Value = "D"
     .Fields("Country").Value = "USA"
     .Update
     .Close
  End With
  Set myRecordset = Nothing
  Set conn = Nothing

End Sub

</source>
   
  

Use AddNew and specify the field information

   <source lang="vb">

Public Sub addCustomer()

 Dim conn As ADODB.Connection
 Dim rs As ADODB.Recordset
 Set conn = CurrentProject.Connection
 Set rs = New ADODB.Recordset
 rs.Open "tblCompany", conn, adOpenDynamic, adLockOptimistic, adCmdTable
 With rs
   .AddNew
   .Fields("CompanyName") = "Diner"
   .Fields("Address") = "Road"
   .Fields("City") = "New York"
   .Update
 End With

End Sub

</source>
   
  

Use AddNew method from Recordset and two arrays to add a new row

   <source lang="vb">

Public Sub addCustomerArray()

 Dim conn As ADODB.Connection
 Dim rs As ADODB.Recordset
 Set conn = CurrentProject.Connection
 Set rs = New ADODB.Recordset
 rs.Open "tblCompany", conn, adOpenDynamic, adLockOptimistic, adCmdTable
   
 varfields = Array("CompanyName", "Address", "City")
 varValues = Array("A", "Road", "B")
 rs.AddNew varfields, varValues
 rs.Update

End Sub

</source>