HandsOn Opening a Recordset Based on an SQL Statement

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 CreateRst_WithSQL procedure shown below.

Part II

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

Sub CreateRst_WithSQL()

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 = conn.Execute("Select * from Employees")

Debug.Print rst("LastName") & ", " & rst("FirstName")

rst.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

Opening a Recordset Based on Criteria

Instead of retrieving all the records from a specific table or query, you can use the SQL WHERE clause to get the records that meet certain conditions. The procedure below calls the recordset's Open method to create a forward-only and read-only recordset populated with employees who do not have a manager.

0 0

Post a comment