How To DAO: Assign a Recordset to a Data Control in VB

This article was previously published under Q147882
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
Some improvements have been made to the data control in Microsoft VisualBasic version 4.0 and higher for Windows. One of these improvements is thecapability to assign back the results of a Recordset object to the datacontrol itself. This was not possible in Microsoft Visual Basic version 3.0for Windows.

This article demonstrates how to assign a Table-type, Dynaset-type, orSnapshot-type Recordset object back to a data control's Recordset property.
MORE INFORMATION
The OpenRecordset method has three possible types of records that it canproduce. Below is a portion of the Visual Basic Online Help that describesthe Openrecordset method that mentions these three types:
dbOpenTable to open a table-type Recordset object.
dbOpenDynaset to open a dynaset-type Recordset object.
dbOpenSnapshot to open a snapshot-type Recordset object.

Also, the following was taken directly from Chapter 4 of the "Guide to DataAccess Objects" book contained in the "Professional Features" Visual Basicmanual:
When you open a database using the data control, a Recordset object is automatically created based on the control's RecordSource property. You can address this Recordset with the following syntax:

Data1.Recordset

All of the Recordset properties and methods discussed in this chapter canbe used with the data control's Recordset. In addition, you can assign thedata control's Recordset object to any Recordset variable you create. Forexample:
   Dim MyRecordset As Recordset   Set MyRecordset = Data1.Recordset				

Conversely, you can assign a Recordset object that you createprogrammatically or with a data control to another data control, using thefollowing syntax:
   Set Data1.Recordset = MyRecordset				

When you assign a new Recordset object to the data control's Recordsetproperty, you must use the data control's Refresh method to rebuild theRecordset and allow the data control to properly display it. In addition,you must make sure that the DataField property of all bound controlsattached to the data control reflect valid fields in the new Recordset.

Step-by-Step Example

  1. Start Visual Basic or on the File menu, click New Project (ALT, F, N) if Visual Basic is already running. Form1 is created by default.
  2. Add three command buttons (Command1, Command2, and Command3), add two text boxes (Text1, and Text2) and one data control to Form1.
  3. Set the following properties:
       Default     Property          Value of Property   -------------------------------------------------   Command1    Caption          Assign Table-type   Command2    Caption          Assign Dynaset-type   Command3    Caption          Assign Snapshot-type   Data1       DatabaseName     BIBLIO.MDB   Data1       RecordSource     Titles   Text1       DataSource       Data1   Text1       Datafield        Title   Text2       DataSource       Data1   Text2       Datafield        Year Published						
  4. Add the following code to the Command1_Click event:
       Private Sub Command1_Click()     Dim ws As Workspace     Dim db As Database     Dim tbl As Recordset     Set ws = DBEngine.Workspaces(0)     Set db = ws.OpenDatabase("Biblio.mdb")     Set tbl = db.OpenRecordset("titles", dbOpenTable)     Set Data1.Recordset = tbl   End Sub						
  5. Add the following code to the Command2_Click event:
       Private Sub Command2_Click()     Dim ws As Workspace     Dim db As Database     Dim rs As Recordset     Set ws = DBEngine.Workspaces(0)     Set db = ws.OpenDatabase("Biblio.mdb")     Dim StrSQL As String     StrSQL = "Select * from titles where title like 'a*' "     Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)     Set Data1.Recordset = rs   End Sub						
  6. Add the following code to the Command3_Click event:
       Private Sub Command3_Click()     Dim ws As Workspace     Dim db As Database     Dim sn As Recordset     Set ws = DBEngine.Workspaces(0)     Set db = ws.OpenDatabase("Biblio.mdb")     Dim StrSQL As String     StrSQL = "Select * from titles where title like 'the*' "     Set sn = db.OpenRecordset(StrSQL, dbOpenSnapshot)     Set Data1.Recordset = sn   End Sub						
  7. On the Run menu, click Start (ALT, R, S), or press the F5 key to run the program. Click the Command1 button to view the records as a table-type Recordset, click the Command2 button to view the records as a Dynaset-type Recordset, or click the Command3 button to view the records as a Snapshot-type Recordset.
kbVBp400 kbVBp600 kbVBp500 kbdse kbDSupport kbVBp kbRDO
Properties

Article ID: 147882 - Last Review: 12/04/2015 14:19:07 - Revision: 2.1

Microsoft Visual Basic 5.0 Learning Edition, Microsoft Visual Basic 6.0 Learning Edition, Microsoft Visual Basic 5.0 Professional Edition, Microsoft Visual Basic 6.0 Professional Edition, Microsoft Visual Basic 5.0 Enterprise Edition, Microsoft Visual Basic 6.0 Enterprise Edition, Microsoft Visual Basic 4.0 Professional Edition, Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

  • kbnosurvey kbarchive kbhowto KB147882
Feedback