HandsOn Modifying a Record

1. Switch to the Visual Basic Editor window and insert a new module.

2. In the Code window, enter the Update_Record procedure as shown below.

Sub Update_Record()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strConn As String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

Set rst = New ADODB.Recordset

With rst

.Open "Select * from Employees Where LastName = 'Marco'", _

strConn, adOpenKeyset, adLockOptimistic .Fields("FirstName").Value = "Paul" .Fields("City").Value = "Denver" .Fields("Country").Value = "USA" .Update .Close End With

Set rst = Nothing Set conn = Nothing End Sub

You can also modify several fields in a specific record by calling the Update method and passing it two arrays. The first array should specify the field names, the second one should list the new values to be entered. For example, the following statement updates the data in the FirstName, City, and Country fields with the corresponding values:

rst.Update Array("FirstName", "City", "Country"), Array("Paul", "Denver", "USA")

You can use the same technique with the AddNew method.

0 0

Post a comment