Example of Opening and Closing Recordset, then Opening a Different Table with the DataEnvironment

The steps and code below will open a Recordset against the Shippers table in the Northwind sample database using the DataEnvironment Connection and Command Objects.

It will then close this Recordset, and open one against the Employees table in the same database.

1. Create a Standard EXE project in Visual Basic. Form1 is created by default.

2. Add a DataEnvironment to the project. Connection1 is created by default.

3. Set Connection1 to use the OLEDB Provider for ODBC Drivers to connect to the Nwind.mdb sample database.

4. Add a command to Connection1 based on the following query:
“SELECT * FROM Shippers”

5. Right-click and drag the command onto Form1 and choose “Data Grid.” (FYI: this is a short-cut, that you should not use on a regular basis; it can cause the bindings to get lost later.)

6. Add a CommandButton to Form1 from the toolbox.

7. Paste the following code in the General Declaration section of your code window.

 

Option Explicit

Private Sub Command1_Click()
‘ Close the recordset to the Shippers table.
With DataEnvironment1
.rsCommand1.Close
‘ Set the Command Source to point to the new table.
.rsCommand1.Source = “Select * from Employees”
‘ Open the new Command recordset.
.rsCommand1.Open
End With
‘ Reset the binding for the DataGrid.
Set DataGrid1.DataSource = DataEnvironment1
‘ Display a recordcount to show new recordset.
MsgBox DataEnvironment1.rsCommand1.RecordCount
End Sub

Private Sub Form_Load()
Command1.Caption = “Close Shippers and Open Employees Table”
End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s