Advanced ADO Data Providers

If you have some special data source that you want to extract data from, you can create an ADO data provider. Peter moves beyond simple read-only providers to show you how to create a provider that performs updates, accepts parameter strings, and fires events.

In last month’s issue I showed you how to create an ADO data provider that you could use in the same way you use the Jet or SQL Server providers that come with ADO (“Creating an ADO Data Provider” in the February 2000 issue). However, the provider that I demonstrated in that article was a basic provider. This article will show you how to add the rest of the features that a provider needs: the ability to perform updates, accept parameters, and fire events back to the clients that use them.


The first thing that you’ll probably want to add to your provider is the ability to perform updates. To do this, you must indicate to ADO that the records that you return are updateable by using the getRWStatus method:

This method will be passed a row and column number, and you must return one of the four enumerated values given in Table 1. If the row parameter is —1, you should return a value that indicates the updateability of the specified column for all the rows that you are returning; if the column parameter is —1, the updateability of all columns for the specified row should be returned; if both values are —1, then you should return the updateability for all of the records that you’re managing.

For my sample provider, which returns data about an Access database, I returned the easiest answer: OSPRW_READONLY for all combinations of the iRow and iColumn parameters. This indicates that that none of the columns and none of the rows are updateable

If you do return one of the other values, then you must write the routine to update your data. ADO will call your module’s setVariant routine when an update is made, passing the row and column number of the field to be updated, along with the field’s new value.You must add the code that allows your provider to update the data source from which you draw your information. For my data provider, the only data source is my internal Recordset, so an updated version of the provider would have a setVariant routine that looks like this:

The code in this example uses the iRow value passed to the routine to set the AbsolutePosition property of the Recordset. Since the first iRow value passed is a 0 and AbsolutePosition begins at 1, I have to add 1 to iRow. The requested row now becomes the current row in the Recordset. The iColumn value is used to select the field to be updated and the var parameter is used to update the field. Here again, the lowest value passed to the routine (1) has to be adjusted to match the lowest value in the Recordset’s Fields collection (0). You would want to provide error handling routines to handle a request for a row or column that didn’t exist or receiving an inappropriate value.

Table 1. Values to be returned by the getRWStatus method.

Update Status Return Values Description
OSPRW_DEFAULT Updates allowed
OSPRW_MIXED Update status not known, or a combination of updateable and non-updateable (say, when the column or row parameter is set to —1 and some rows/columns can be updated and others can’t)
OSPRW_READONLY Updates not allowed
OSPRW_READWRITE Updates allowed

Parameter strings

My code for processing the commands passed to my bare-bones provider was very simple and just accepted the name of the database concatenated with the name of the object to retrieve (“Form” or “Report”). A more flexible system would allow users to pass a set of parameters that my provider could then process, like this:

The command string passed to my provider now matches the format used by ODBC and ADO connection strings: parameter name, an equals sign, a parameter value, and a semicolon, with all four items repeated indefinitely. This string will be passed to the GetDataMember of the class that controls my provider where I can parse it and use it to set properties or methods of my provider class.

To process a parameter string, I use Visual Basic 6’s Split function. This function returns an array of all of the values between a delimiter. Passed a string and a semicolon, for instance, the split function will find all of the substrings that exist between the semicolons and create an array of them. Passed the parameter string I used above, Split will return an array with “ObjectType=Form” in its first position and “Database=C:\MyDB.MDB” in the second position. I can then use the Split function on each position in the array to find the data before and after the equals sign. Using Split on “ObjectType=Form” will create an array with “ObjectType” in the first position and “Form” in the second. A simple Select statement will let me pass the right data to the right property:


ADO Recordsets can fire events. A client can declare a Recordset using the WithEvents keyword and then write code to respond to events fired by the Recordset. As an example, this code will be executed prior to any updates in the Recordset:

In your code, you can use the OLEDBSimpleProviderListener object to fire events back to the clients using your provider. As each client connects to your provider, the addOLEDBSimpleProviderListener event will fire in your provider and pass you a reference to an OLEDBSimpleProviderListener object. As each client disconnects, the removeOLEDBSimpleProviderListener event will fire. The OLE Listener object is your connection to the clients using your provider.

In the addOLEDBSimpleProviderListener event, you can set a variable that will reference the Listener object passed in. With that reference to the Listener object, you can use its methods to notify your clients of the actions that your provider is taking or is about to take. Each method of the Listener object causes a corresponding event to fire in the client using the provider. This code, for instance, fires events before and after any update to my internal Recordset:

When the provider calls the aboutToChangeCell method, the client receives the WillChangeField event; calling the cellChanged method fires the FieldChangeComplete event in the client using the provider. There doesn’t seem to be any way to respond to the client setting the adStatus variable that’s passed to the client in the event that results from calling the Listener’s methods.

If your provider can be used by multiple clients, you’ll need to keep references to all of the Listener objects that are passed to you. A collection is an excellent choice for holding those references. This version of the addListener method adds each Listener object to a collection:

As the code in your provider executes and you want to fire events back to your clients, you’ll need to loop through the collection, firing the appropriate methods:

The removeListener event fires as each client disconnects from your provider. In the remove event, you’ll have to find the Listener object in the collection that’s associated with the disconnecting client. The remove event is passed a reference to the Listener for the disconnecting client, so it’s just a matter of finding the matching object in the collection and then removing it:

And that’s all there is to it. This article provides you with the finishing touches that every ADO provider should have. Armed with this information, you can create data providers that you can use from Access 97 or 2000, and that have all the capabilities of the providers that come from Microsoft.

Sidebar: Consumers, Providers, and Service Providers

OLE DB, on which ADO rests, provides a set of objects that allow you to work with data. OLE DB divides these objects into three groups: consumers, providers, and service providers. Consumers are objects or applications that use data provided by the other two kinds of objects. A provider is an object that can extract data from a data source and return it in the standard OLE DB format. A service provider can’t extract data, but, like a consumer, it can accept data from a provider. A service provider, unlike a straight consumer, also manipulates the data and can pass it on to a consumer or another service provider. Since a service provider can work with any ADO provider, service providers can be very flexible. Microsoft’s search engine, Index Server, has a data provider that you can use to extract data from the search engine’s catalog (for example “Find me all of the documents containing the words ‘Smart Access’”). Since the Index Server data provider returns data in the standard ADO format, you can use the Data Shaping server to manipulate that data. All of these features are available from any development tool that can work with objects, including Access 97 and Access 2000.


About Peter Vogel

After 10 years of editing Smart Access, Peter continues to develop with Access (though he also does a lot of .NET stuff). Peter is also still editing other people's article--he even self-published a book on writing user manuals ("rtfm*") with a blog here.
This entry was posted in Other Topics. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.