HandsOn Adding a New Record to a Table

1. Open the Acc2003_Chap14.mdb file from the book's downloadable files, or create this file from scratch using the Microsoft Office Access user interface.

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

3. In the module's Code window, enter the Add_Record procedure as shown below.

' 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 rst As ADODB.Recordset Dim strConn As String

Part II

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

Set rst = New ADODB.Recordset With rst

.Open "Select * from Employees", _

strConn, adOpenKeyset, adLockOptimistic

' Add a record and specify some field values .AddNew

!LastName = "Marco" !FirstName = "Paulo" !City = "Boston"

' Retrieve the Employee ID for the current record Debug.Print !EmployeeId.Value

' Move to the first record .MoveFirst

Debug.Print !EmployeeId.Value .Close End With

Set rst = Nothing Set conn = Nothing End Sub

When adding or modifying records, you can set the record's field values in one of the following ways:

rst.Fields("FirstName").value = "Paulo"

or rst!FirstName = "Paulo"

As mentioned earlier, when you use the AddNew method to add a new record and use the Move method, the newly added record is automatically saved without explicitly having to call the Update method. In the example procedure above we used the MoveFirst method to move to the first record; however, you can call any of the other move methods (Move, MoveNext, MovePrevious) to have ADO implicitly call the Update method. After calling the AddNew method, the new record becomes the current record.

0 0

Post a comment