Using the DataGrid Control with a Class Module

See Also

If the data you want to access exists in a custom format, or in a form not directly supported by an ODBC driver, you can create a class to encapsulate the data. You can then program the class with customized functions to retrieve the data. The class then becomes a data source that can be used by any data consumer, such as the DataGrid control.

In the class module's Initialize event, you first create an ADODB recordset object by declaring a variable as New ADODB.Recordset. After creating the recordset object, append fields to it, one for each field in your data source. Then fill the recordset with the appropriate data.

Note   You can also create a data source using an OLEDB Simple Provider. See "Creating a Component with a Data Provider" for more information about OLEDB Simple Providers.

The class module features a GetDataMember event that occurs whenever a data consumer (such as the DataGrid control) requests data. In the event, the Data argument is set to the recordset object created in the Initialize event.

To use the class module, create a form with a DataGrid control on it. In the form's Load event, place code that sets the control's DataSource property to the class.

Note   The class module won't be available at design-time. For example, with the DataGrid control, all available data sources appear in a drop-down list when the user clicks DataSource on the Properties window. The class module will not appear among them, and can only be set through code.

Create a Data Source Using the Class Module

The example below uses a class module to create a simple data source. The DataGrid control is then bound to the module through the DataSource property.

To create a class for use with the DataGrid

1. Create a new Standard Exe project.
   
   
2. Add a DataGrid control to the form.

   If the DataGrid control is not available in the Toolbox, on the Project menu, click Components. Click Microsoft DataGrid Control, then click OK.

3. On the Project menu, click References. On the References dialog box, click Microsoft ActiveX Data Objects 2.0 Library.
   
   
4. On the Project menu, click Add Class Module to add a class module to the project.
   
   
5. In the Project Explorer window, click the Class icon to select it, and press F4 to display the Properties window.
   
   
6. On the Properties window, change the name of the class to NamesData.
   
   
7. On the Properties window, click DataSourceBehavior and change the property to vbDataSource.
   
   
8. In the Declarations section of the class module, create an ADODB Recordset variable, as shown below:

   Option Explicit
   Private WithEvents rsNames As ADODB.RecordSet
   

   Declaring the variables using the WithEvents keyword allows you to program the RecordSet object events.

9. In the class Initialize event, add the following code:

   Private Sub Class_Initialize()
      ' Add the names of the new datamember to the DataMember collection
      ' This allows other objects to see the available DataMembers
      DataMembers.Add "Names"   
   
      Set rsNames = New ADODB.RecordSet ' Set the object variable.
   
      ' Create a recordset with two fields and open the recordset. The 
      ' first record has an integer data type and the second is a string, 
      ' with a maximum of 256 characters. The CursorType is set to 
      ' OpenStatic--an updatable snapshot of a set of records. The 
      ' LockType is set to LockOptimistic to allow updates to the 
      ' recordset
      With rsNames          
         .Fields.Append "ID", adInteger
         .Fields.Append "Name", adBSTR, 255
         .CursorType = adOpenStatic
         .LockType = adLockOptimistic
         .Open
      End With
   
      Dim i As Integer
      For i = 1 to 10 ' Add ten records.
         rsNames.AddNew 
         rsNames!ID = i
         rsNames!Name = "Name " & i
         rsNames.Update
      Next i
      rsNames.MoveFirst ' Move to the beginning of the recordset.
   End Sub
   

   The code first creates the recordset object, then appends two fields to the recordset. The code then adds ten records to the recordset.

10. In the class GetDataMember event, add the following code:

    Private Sub Class_GetDataMember(ByVal DataMember As String, _
    Data As Object)
       Set Data = rsNames
    End Sub
    

    The code returns the recordset object whenever the event occurs — whenever the class object is bound to a data consumer, such as the DataGrid control.

11. In the Form object's code module, declare an object variable for the class:

    Option Explicit
    Private datNames As NamesData ' Class variable
    

12. In the Form object's Load event, add the code to set the DataGrid control's DataSource to the class object.

    Private Sub Form_Load()
       ' Create a new NamesData Object
       Set datNames = New NamesData
        
       ' Bind the DataGrid to the new DataSource datNames
        Set DataGrid1.DataSource = datNames
    End Sub
    

13. Press F5 to run the project.

Programming the RecordSet Events

You can also program the events of the Recordset object. In the class module, click the Object Box (in the upper left corner), and then click rsNames. In the Procedures/Events box (upper right corner), the drop-down list will display all of the events of the Recordset object.

Add a Property to the Class

The class module can also be modified to respond to events or function calls. The code below shows how you can first add a property to the class. When invoked from another object, the property returns the RecordCount of the class.

Public Property Get RecordCount() As Long
   RecordCount = rsNames.RecordCount
End Sub

Using the DataMember Property

The GetDataMember event also includes the DataMember argument. Using this argument, you can include more than one recordset in the class module, and return the appropriate recordset by using a Select Case statement with the DataMember argument:

Private Sub Class_GetDataMember(ByVal DataMember As String, Data As _
Object)
   Select Case DataMember
   Case "Names"
      Set Data = rsNames
   Case "Dates"
      Set Data = rsDates
   Case Else
      ' Set a default data member.
      Set Data = rsYears
   End Select
End Sub

To specify which DataMember you want, set the DataMember property of the data consumer to the appropriate string, then set the DataSource as usual. For the DataGrid control, this would be:

Private Sub Form_Load()
   ' Create a new NamesData Object
   Set datNames = New NamesData

   ' Specify which DataMember you want, then set DataSource.
   DataGrid1.DataMember = "Names"
   Set DataGrid1.DataSource = datNames

End Sub