HandsOn Opening a Recordset Based on Criteria

1. In the Database window, press Alt+F11 to switch to the Visual Basic Editor window.

2. In the Visual Basic Editor window, choose Insert | Module.

3. In the module's Code window, type the OpenRst_WithCriteria procedure shown below.

4. Choose Run | Run Sub/UserForm to execute the procedure.

Sub OpenRst_WithCriteria() 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 conn = New ADODB.Connection conn.Open strConn

Set rst = New ADODB.Recordset rst.Open "SELECT * FROM Employees WHERE ReportsTo is Null", _

Creating and Manipulating Databases with ADO

conn, adOpenForwardOnly, adLockReadOnly

Do While Not rst.EOF

Debug.Print rst.Fields(1).Value rst.MoveNext Loop rst.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

Opening a Recordset Directly

If you are planning to open just one recordset from a specific data source, you can take a shortcut and open it directly without first opening a Connection object. This method requires you to specify the source and connection information prior to calling the Recordset object's Open method, as shown in Hands-On 13-5 below.

0 0

Post a comment