HandsOn Opening a Recordset

1. Create a new Microsoft Office Access database or open the Acc2003_ Chap13.mdb file from the book's downloadable files. If you are creating a database from scratch, you should import the Employees table from the Northwind database.

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

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

4. In the module's Code window, type the OpenRst procedure shown below.

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

Sub OpenRst()

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset With rst

.Source = "Select * from Employees" .ActiveConnection = CurrentProject.Connection .Open

Debug.Print rst.Fields.Count .Close End With

Set rst = Nothing End Sub

In the code example above, the first two lines declare a Recordset object and create a new instance of it. Next, the Source property specifies the data you want to retrieve. (The source can be a table, query, stored procedure, view, saved file, or Command object.)

The SQL SELECT statement tells VBA to select all the data from the Employees table. Next, the ActiveConnection property specifies how to connect to the data. Because the Employees table is located in the currently open database, you can use CurrentProject.Connection. Finally, the Open method retrieves the specified records into the recordset. Before we close the recordset

Part II

using the recordset's Close method, we retrieve the number of fields in the open recordset by examining the recordset's Fields collection, and write the result to the Immediate window.

Opening a Recordset Based on a Table or Query

A recordset can be based on a table, view, SQL statement, or a command that returns rows. It can be opened via a Connection or Command object's Execute method or a recordset's Open method (see the example procedures below). ■ Connection.Execute method

Sub ConnectAndExec()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb" Set rst = conn.Execute("Select * from Employees") Debug.Print rst.Source rst.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

^^ Note: Once you open the recordset, you can perform the required operation on its data. In this example, we use the recordset's Source property to write to the Immediate window the SQL command on which the recordset is based.

■ Command.Execute method

Sub CommandAndExecO

Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection With conn

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

End With

Set cmd = New ADODB.Command With cmd

.ActiveConnection = conn .CommandText = "Select * from Customers" End With

Set rst = cmd.Execute

Creating and Manipulating Databases with ADO

MsgBox rst.Fields(1).Value rst.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

^^ Note: Once you open the recordset, you can perform the required operation on its data. In this example, we display a message with the name of the first customer.

■ Recordset.Open method

Sub RecSetOpen()

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

Set rst = New ADODB.Recordset With rst

.Open "Select * From Customers", _

strConnection, adOpenForwardOnly .Save CurrentProject.Path & "\MyRst.dat" .Close End With

Set rst = Nothing End Sub

^^ Note: Once you open the recordset, you can perform the required operation on its data. In this example, we save the entire recordset to a disk file named MyRst.data. In Chapter 16 you learn how to work with records that have been saved in a file.

The procedure in Hands-On 13-2 illustrates how to open a recordset based on a table or query.

0 0

Post a comment