VBA/Excel/Access/Word/Access/Recordset Add Update Delete
Содержание
- 1 Adding a New Record to a Recordset
- 2 Adding a New Record to a Table
- 3 Add new a row to recordset
- 4 Call delete method in Recordset
- 5 Call update method from Recordset
- 6 Check the Recordset affected
- 7 Delete row for a certain criteria
- 8 Deleting a Record
- 9 Modifying a Record
- 10 Use AddNew and specify the field information
- 11 Use AddNew method from Recordset and two arrays to add a new row
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>