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.
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
If the DataGrid control is not available in the Toolbox, on the Project menu, click Components. Click Microsoft DataGrid Control, then click OK.
Option Explicit
Private WithEvents rsNames As ADODB.RecordSet
Declaring the variables using the WithEvents keyword allows you to program the RecordSet object events.
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.
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.
Option Explicit
Private datNames As NamesData ' Class variable
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
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.
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
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