Opening ADO Recordset Objects

This sample project demonstrates three methods of opening recordsets using ADO 2.1,

• by referring to the ActiveConnection,
• without using a Connection object,
• and using the Execute method of the Connection object.

Making the ADO Opening Recordsets Project

1. Create a DSN referring to the Northwind database. For information on creating DSNs, see these two MSDN Help topics:
   • Creating a File DSN
   • Creating a Machine DSN
2. Open a new project in Visual Basic 6.0, and add a reference to the Microsoft ActiveX Data Objects 2.1 Library .
3. Add three command buttons and three labels to the form. Set properties on these controls as listed in the following table.
   
   

   Object	Property	Value
   1st command button	Caption	Execute Method
   2nd command button	Caption	ActiveConnection
   3rd command button	Caption	Without Connection object
   1st label	Caption	ProductName field
   2nd label	Caption	ProductName field
   3rd label	Caption	ProductName field

   
   
4. Copy the following code into the form.

Option Explicit
Private Sub Command1_Click()
    'Open a recordset using the Execute method of the Connection object.
    Dim cnNWind As Connection
    Dim rsNWind As Recordset

    Set cnNWind = New Connection

    'Open method uses Northwind DSN.
    cnNWind.Open "northwind"

    'Execute method of Recordset object uses Products table of NWind database.
    Set rsNWind = cnNWind.Execute("products", , adCmdTable)

    'You can use ADO recordsets with bound controls.
    Set Label1.DataSource = rsNWind
    Label1.DataField = "ProductName"

    'Cleanup
    rsNWind.Close
    cnNWind.Close
    Set cnNWind = Nothing
    Set rsNWind = Nothing
End Sub

Private Sub Command2_Click()
    'Open a recordset by referring to the ActiveConnection.
    Dim cnNWind As Connection
    Dim rsNWind As Recordset

    Set cnNWind = New Connection
    Set rsNWind = New Recordset

    'Open method of Connection object uses Northwind DSN.
    cnNWind.Open "northwind"

    'Open method of Recordset object uses cnNWind, the ActiveConnection.
    rsNWind.Open "products", cnNWind, adOpenStatic, adLockOptimistic, adCmdTable
    Label2.Caption = rsNWind!ProductName

    'Cleanup
    rsNWind.Close
    cnNWind.Close
    Set cnNWind = Nothing
    Set rsNWind = Nothing
End Sub

Private Sub Command3_Click()
    'Open a recordset without using a Connection object.
    Dim rsNWind As Recordset

    Set rsNWind = New Recordset

    'Open method of Recordset object uses Products table and the Northwind DSN.
    rsNWind.Open "products", "northwind", adOpenStatic, adLockOptimistic, adCmdTable
    Label3.Caption = rsNWind!ProductName

    'Cleanup
    rsNWind.Close
    Set rsNWind = Nothing
End Sub

5. Save the project, then run it.

Last changed 9/20/00