ADO lets you access data through data providers. Here’s how to create your own data provider, which can be used from ADO. Peter Vogel demonstrates this technology by creating a provider to pull Form, Query, and Report information out of an Access MDB.

From Access 95, 97, and 2000 you can use ADO to retrieve data through data providers. The available data providers include the Microsoft.Jet OLE.DB.4.0 provider, which accepts SQL commands and returns data pulled from Jet databases. But you can create your own data providers that will accept requests and return Recordsets. And your data providers can extract their data from any data source that you want! The beauty of the OLE DB framework is that no matter what format you have your data stored in, you can create a provider for that data. Once your provider is created, your data becomes accessible from any ADO-compliant tool–which means any tool that can work with COM–from Visual Basic to Access to Delphi.

In this article, I’ll show you how to create a data provider using Visual Basic and how to call it from Access. The example that I use is a simple provider that returns information about the Forms, Queries, and Reports in an MDB file.

Data providers

While data providers can be created in any language, I’ll use Visual Basic for this example. To create a data provider, you begin with either an ActiveX DLL or ActiveX EXE project in Visual Basic (an ActiveX DLL will give you the best performance). You must have two Class modules in the project. One Class module is responsible for retrieving the data. The second class module, which I’ll call the interface module, handles the communication between ADO and your data provider Class. I’ll begin by looking at the data provider Class module, stop to look at the interface module, and then return to finish the Class module.

After creating your Visual Basic project, you must add a reference to the Microsoft OLE DB Simple Provider 1.5 Library to your Project | References list. With that reference added, you’re ready to work on the Class module that will act as your data provider module. To convert the Class module to a data provider, you need to implement the OLE DB Simple Provider interface in it. You do that by adding this line to the top of the class module:

Implements OLEDBSimpleProvider

Now that you’ve added that Implements line, you’ll have to add routines for all of the methods that make up the OLE DB Simple Provider interface. The names and purpose of the methods that make up the interface are summarized in Table 1. How many of these routines you actually put code in is up to you. In order to create a read-only provider, which is all that I’ll do here, you only need to return values for the getColumnCount, getRowCount, and getVariant routines.

Table 1. Methods for an OLE DB Simple Provider.

Before looking at the code that actually retrieves the data, I’ll show you some of the simpler routines to implement in the data provider module. For my provider, I created an internal Recordset that holds three columns of information for each Form, Report, or Query in the target database: the name, the creation date, and the last modified date. As a result, the data provider’s getColumnCount method just has to return the number 3–the number of columns in the Recordset that I create:

Private Function _

  OLEDBSimpleProvider_getColumnCount() As Long

 OLEDBSimpleProvider_getColumnCount = 3

End Function

Two other routines (get EstimatedRows and getRowCount) return the number of rows in my internal Recordset. To supply those values, I just return the RecordCount property of that Recordset in those two routines:

Private Function _

  OLEDBSimpleProvider_getEstimatedRows() As Long

 OLEDBSimpleProvider_getEstimatedRows = _

                             m_RS.RecordCount

End Function



Private Function _

  OLEDBSimpleProvider_getRowCount() As Long

 OLEDBSimpleProvider_getRowCount = _

                             m_RS.RecordCount

End Function

The routine that’s called when the user asks for data is the provider’s getVariant routine. This routine is passed the row number and the column number of the data to be returned. In my provider I use the row number to move to the requested row in my Recordset using the Recordset’s AbsolutePosition property. I use the Fields collection and the column number to return the requested column. Here’s the code (with error handling removed):

Private Function OLEDBSimpleProvider_getVariant _

  (ByVal iRow As Long, ByVal iColumn As Long, _

   ByVal format As std.OSPFORMAT) As Variant

  m_RS.AbsolutePosition = iRow + 1

  OLEDBSimpleProvider_getVariant = m_RS(iColumn - 1)

End Function

The values of the iRow and iColumn parameters must be adjusted to match the way that my Recordset works. A Recordset’s lowest AbsolutePosition is 1, but the getVariant routine passes 0 in the iRow parameter. Similarly, the first field in the Fields collection is number 0, but the iColumn parameter is passed a 1.

With the provider mostly built, I’ll turn my attention to creating the interface class that will manage access to my provider. This interface class is another Class module in the Visual Basic project. After adding the Class module you must set its DataSourceBehavior property to vbDataSource. Setting this property requires you to add a GetDataMember routine to your module. The GetDataMember routine is passed two parameters:

• DataMember, which will contain the command passed to the provider from the calling program. My provider requires commands to consist of the type of object being requested and the path to the database (for instance, “Form;C:\My Documents\MyDB.MDB”).
• Data, which is used to return the data provider you created to the calling program.

In the GetDataMember routine you must load the Class module that retrieves the data and perform any initialization work. Since I called my data provider Class Module clsMDBInfo, the GetDataMember’s code to create the provider Class module looks like this:

Private Sub Class_GetDataMember _

  (DataMember As String,Data As Object)

  Set Data = New clsMDBInfo

End Sub

Since the GetDataMember of the interface class receives the command passed to the provider, I must pass that command on to the data provider class so that it can build its internal Recordset. I first parse out the command to get the kind of object to retrieve and the database name. Once I have them, I pass them to separate properties on the data provider and call a routine to build my internal Recordset (again, error handling has been removed):

Private Sub Class_GetDataMember _

  (DataMember As String,Data As Object)

Dim intPos As Integer



  Set Data = New clsMDBInfo

  intPos = InStr(DataMember, ";")

  Data.Command = Left$(DataMember, intPos - 1)

  Data.DB = Mid$(DataMember, intPos + 1)

  Data.CreateRecordset

End Sub

I’ll now return to my data provider class to show how I actually retrieve the data. I need to define the two properties and the method that I used in the GetDataMember routine of the interface class. The Command and DB properties are easy: I just add Public variables at the top of the data provider Class module:

Public Command As String

  Public DB As String

My CreateRecordset method is only slightly more complicated. First, I have to create a Recordset and add some columns to it. To create an ADO Recordset from scratch I need a reference to the ADO Recordset library in my Project | References dialog. After using the New keyword to create the Recordset, this code then adds three fields to the Recordset and opens the Recordset:

Dim m_RS As ADOR.Recordset



Public Sub CreateRecordset()



Set m_RS = New ADOR.Recordset

m_RS.Fields.Append "Name", adVarChar, 25

m_RS.Fields.Append "DateCreated", adVarChar, 20

m_RS.Fields.Append "DateModified", adVarChar, 20

m_RS.Open

The OLE DB simple provider will use the first row in the Recordset that I return to get the names of the fields in my Recordset. I need to add a row to hold those names:

m_RS.AddNew

m_RS(0) = "Name"

m_RS(1) = "DateCreated"

m_RS(2) = "LastUpdated"

Now, I have to open a Jet database to get a list of the Forms, Reports, and Queries stored in it. In order to get at that information, I’ll fall back on good old DAO, which means that I have to add a reference to DAO to my rapidly growing References list. You must be careful when you have DAO and ADO referenced in the same project, since they share the same name for their Recordset objects. Whenever referring to a Recordset, you should always prefix it with DAO or ADODB so that VBA knows which Recordset you mean.

Once I have the reference to DAO made, I can open the requested database and find the appropriate collection (that is, Forms, Reports, or Queries).

Dim dbs As DAO.Database

Dim col As Object



Set dbs = OpenDatabase(DB)

Acc.OpenCurrentDB DB



Select Case Command

  Case "Form"

    Set col = dbs.Containers("Forms").Documents

  Case "Report"

    Set col = dbs.Containers("Reports").Documents

  Case "Query"

    Set col = dbs.QueryDefs

End Select

Finally, I can use a For Each…Next loop to move through each item in the collection. For each item in the collection, I add a row to my Recordset and copy the properties that I want to report to the appropriate column in the Recordset. Here’s that code that populates my Recordset with the data from Access:

Dim itm As Object

For Each itm in col

  m_RS.AddNew

  m_RS(0) = itm.Name

  m_RS(1) = itm.DateCreated

  m_RS(2) = itm.LastUpdated

Next



End Sub

Registering your provider

The next steps require you to step out of Visual Basic. To have your class treated as an ADO provider, you need to generate a GUID and create a bunch of registry entries that identify your provider to ADO.

To generate a GUID, you can use the UUIDGen.EXE that’s distributed with Visual Studio. You can find it in C:/Program Files/Microsoft Visual Studio/common/tools or download it from the Microsoft Web site. UUIDGen is a DOS command-line utility, so to run it you can either open a DOS window or execute it from the Start menu’s Run option. I pass the utility two switches:

• /i This switch produces a short text file containing a new GUID.
• /o This switch should be followed by the file name where the text file should be placed.

This command line, for instance, generates the text file in c:\myguid.txt:

uuidgen.exe /i /oc:\myguid.txt

The resulting file will look something like this:

[

uuid(bcde9140-5068-11d3-a8c8-00107a901a5f),

version(1.0)

]

interface INTERFACENAME

{



}

The only part that you need from the file is the GUID (in this case, bcde9140-5068-11d3-a8c8-00107a901a5f).

For the registry entries, I’ve borrowed the following template (Listing 1) from Robert Macdonald, whose article “Do-It-Yourself Recordsets” in the December 1998 edition of Pinnacle’s Visual Basic Developer inspired me to write my provider. I keep the template text in a file on my hard disk and update it in Notepad with the entries appropriate for my provider. After saving the file to my hard disk with a file extension of .REG, I double-click on the file in Windows Explorer to cause RegEdit to read the file’s entries and update my Windows Registry. I then store the file someplace safe to distribute with the setup routine for the component.

To modify the template for your provider, you’ll need to make the following changes:

• Replace **GUID** with the GUID that you generated with UUIDGEN.
• Replace **Full Name** with any descriptive text (just make sure that you use the same text in both places where the full name is required).
• Replace **Component** with the name of your Visual Basic project from the Project | Properties menu choice.
• Replace **Connection** with the progid of your interface class. The progid will be the project name and the class name, separated by a period.

Listing 1. Rob Macdonald’s original template.

REGEDIT4



[HKEY_CLASSES_ROOT\**Component**]

@="**FullName**"



[HKEY_CLASSES_ROOT\**Component**\CLSID]

@="{**GUID**}"



[HKEY_CLASSES_ROOT\CLSID\{**GUID**}]

@="**Component**"



[HKEY_CLASSES_ROOT\CLSID\{**GUID**}\InprocServer32]

@="c:\\Program Files\\Common Files\\System\\OLE DB\\MSDAOSP.DLL"

"ThreadingModel"="Both"



[HKEY_CLASSES_ROOT\CLSID\{**GUID**}\ProgID]

@="**Component**.1"



[HKEY_CLASSES_ROOT\CLSID\{**GUID**}\VersionIndependentProgID]

@="**Component**"



[HKEY_CLASSES_ROOT\CLSID\{**GUID**}\OLE DB Provider]

@="**FullName**"



[HKEY_CLASSES_ROOT\CLSID\{**GUID**}\OSP Data Object]

@="**Connection**"

Listing 2  is an example of what a typical set of entries would look like. In this case, my Visual Basic project was called MDBProvider and the interface class was called clsMDBI. I gave the provider the arbitrary name “MDBInfo”. I used a different GUID from the preceding UUIDGen example:

Listing 2. A typical set of entries.

REGEDIT4



[HKEY_CLASSES_ROOT\MDBProvider]

@="MDBInfo"



[HKEY_CLASSES_ROOT\MDBProvider\CLSID]

@="{dce844e0-4be1-11d2-8e4c-00104bdc2942}"



[HKEY_CLASSES_ROOT\CLSID\{dce844e0-4be1-11d2-8e4c-00104bdc2942}]

@="MDBProvider"



[HKEY_CLASSES_ROOT\CLSID\{dce844e0-4be1-11d2-8e4c-00104bdc2942}\InprocServer32]

@="c:\\Program Files\\Common Files\\System\\OLE DB\\MSDAOSP.DLL"

"ThreadingModel"="Both"



[HKEY_CLASSES_ROOT\CLSID\{dce844e0-4be1-11d2-8e4c-00104bdc2942}\ProgID]

@="MDBProvider.1"



[HKEY_CLASSES_ROOT\CLSID\{dce844e0-4be1-11d2-8e4c-00104bdc2942}\VersionIndependentProgID]

@="MDBProvider"



[HKEY_CLASSES_ROOT\CLSID\{dce844e0-4be1-11d2-8e4c-00104bdc2942}\OLE DB Provider]

@="MDBInfo"



[HKEY_CLASSES_ROOT\CLSID\{dce844e0-4be1-11d2-8e4c-00104bdc2942}\OSP Data Object]

@="MDBProvider.clsMDBI"

To use this ADO provider, the developer sets the provider attribute in a connection string to the name you gave in your REG file. This code, for instance, creates a Recordset of Form information using my provider and displays all of the form names:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset



rs.Open "Form;C:\My Document\MyDB.MDB", _

   "Provider=MDBInfo;"

While Not rs.EOF

  Msgbox rs!Name

  rs.MoveNext

Wend

I’ve included a version of the MDB Information provider in this month’s Subscriber Download file. Also included is the registry file to register it. Using that code as a model, you’re now ready to create your own ADO data providers.