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.

Updates

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:

Events

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.

 

Posted in Other Topics | Leave a comment

An ADO Tutorial: What is ADO?

In this article, Russell Sinclair provides a primer on what you need to know about ADO: what it is, why you should care, ADO’s relation to OLE DB, and a tour of the major objects in ADO.

In order to explain ADO, it’s first necessary to explain OLE DB. OLE DB is a new standard that Microsoft has created for accessing persistent data stores. Basically, OLE DB is a replacement for ODBC, the Open Database Connectivity standard. What OLE DB does that ODBC didn’t is provide a standard interface to all data stores, regardless of the manufacturer or format. These data stores include database management systems and any other tool for storing or persisting data. To be used by OLE DB, a data store need only be a storage system that saves information in a common state. You can even think of the file system on Windows as a type of data store, and, in fact, there’s already an OLE DB driver for Directory Services. With the right OLE DB driver, you can access any data source you need.

OLE DB, however, doesn’t have an interface that can be easily accessed by programming languages such as Visual Basic or Java. OLE DB is meant to be used by C programmers. ActiveX Data Objects (ADO) is an object-oriented ActiveX interface that allows non-C programmers to use OLE DB. ADO is designed to be very similar to DAO, the Microsoft Jet Database Engine’s native interface. ADO gives developers the ability to access any data store without needing to know many specifics about how the data store actually handles the data.

What all of this means is that ADO is the new programming interface that you should start using in order to access any data store. However, keep in mind that in Access 95 and 97, there are no controls that make use of ADO that you can place on any of your forms. All of the objects in ADO exist entirely in code. Currently, Microsoft has created OLE DB drivers for Access, Oracle, Microsoft SQL Server, Microsoft Index Server, and Active Directory Service. Other drivers can be purchased through third parties.

OLE DB and ADO are themselves part of Microsoft’s Universal Data Access (UDA), a data management strategy designed to standardize methods of accessing information across the enterprise.

The ADO object model

Figure 1 shows a diagram of the basic ADO object model. As you can see, ADO is comprised of only three major objects: the Connection object, the Command object, and the Recordset object. Each of the objects has two collections. All three objects have a Properties collection, whose members can change depending on the OLE DB provider that’s used.

 
Figure 1

The Connection object
The Connection object is the main object in the ADO model. It’s used to create a connection to a database. If you’re used to DAO, you can think of a Connection object as a hybrid between the DAO Workspace and Database objects. Through the Connection object, you specify information about how ADO should connect to the data store. This includes what OLE DB driver to use, the name and location of the database, login credentials, and whether to use server-side or client-side cursors.

Most actions that take place against a database are housed in the Connection object. All Recordset and Command objects have a Connection object that can be accessed through their ActiveConnection property. The Errors collection of a Connection object holds all of the error messages generated by a data source when an error occurs. Errors is a collection rather than a single property, since multiple errors can be generated by a single action. For example, you might issue a SQL statement that tried to modify two fields with invalid values. In this case, the Errors collection would have two entries.

You can loop through the Errors collection as you do with many other collections: by using a For…Next loop, as in this example:

The Command object

The Command object allows you to run queries or text commands against the active Connection object. This is especially useful for queries or actions that modify data. Since many queries have input parameters, the Command object has a Parameters collection. Through this collection, you can set or retrieve values for parameters for a query (provided the data store supports parameters). If you run SQL commands or queries that return data, you can assign the data that the Command object returns to a Recordset object where you can view it at a later time.

If the data provider supports it, Command objects are compiled only the first time they’re executed. This means that the first time you issue your command, you’ll have to wait for the data provider to parse, compile, and optimize your command before executing it. On any subsequent executions of the Command in the same program, the Command will just be executed. For complex queries, this can be a real time-saver.

The Recordset object

The Recordset object is the object that holds the data returned by either the Command or the Connection object. The ADO Recordset object is similar to the DAO Recordset object. The two Recordsets share many of the same properties, and if any of you are experienced in DAO programming, then you’ll barely notice the differences. Essentially, the ADO Recordset allows you to view and modify the data retrieved from a data store. With the Recordset object, you can filter data, search for records matching your own criteria, or copy data between Recordsets or code variables.

The Fields collection of the Recordset object contains Field objects corresponding to, you guessed it, fields in the Recordset. Fields have their own properties that define the type of data contained in the field, the size of the field, the value contained within the field, and other useful information.

Events in ADO

ADO allows you to execute certain commands asynchronously. This means that you can execute a command against an object without passing control to that command. For example, if you use the Command object’s Execute method to issue a SQL statement, your program will normally stop code execution at the point that you call the method. Your program won’t continue executing until the command is finished processing. This can be impractical if the query or SQL statement that you’re running takes a long time to complete. If you specify that the function should be executed asynchronously, the lines of code following the method call will be executed without waiting for the SQL command to finish processing.

The result of this asynchronous processing is that you need some kind of notification when the method has actually completed executing. This notification is supplied through an event. If you used the Execute method, the ExecuteComplete event would fire. Any code you put in this event will be executed once the SQL command has finished processing and the data it retrieved is available for you to use.

In order to have ADO events available to you, you must declare the object variable that you use to issue the command in the declarations section of a class module (remember that forms and reports are class modules), and use the WithEvents keyword, as in this example:

(For a more in-depth discussion of the WithEvents keyword, see Shamil Salakhetdinov’s article, “Using Dynamic External Event Procedures,” in the January 1999 issue of Smart Access). Once you have this code entered into a class module, the variable you’ve used for the object is treated like a control that you’ve placed on a form. When you add code to the class module, you’ll find the object listed in the object list and the events for the object listed in the procedure list. Only the Recordset and Connection objects support events.

Most of the events in the Recordset and Connection objects are paired. In any method that supports events, the events are called the WillMethod and MethodComplete events. If you have any experience programming in VBA, you’ve probably used the Before and After events of various objects (the BeforeUpdate and AfterUpdate events of the Access Form object are an example). You can think of the events in ADO in exactly the same way. One event is raised as a method begins, and the other is raised when the action completes execution. For the Command object’s Execute method, there’s a WillExecute method and an ExecuteComplete event.

You should be aware of one thing when using events in ADO. ADO allows you to cancel an asynchronous action while it’s processing. However, if you cancel an event before it’s finished, it doesn’t necessarily mean that the Complete event won’t occur. This is different from VBA, where canceling an event when it starts will prevent the completion event from begin raised. Most ADO events pass a parameter called adStatus to the event procedure. The adStatus variable lets you do the following:

  • check to see whether the method completed successfully
  • cancel the event by modifying the variable
  • tell ADO to stop generating events in the current context

How to start using ADO

Before you can develop applications with ADO, you must download the Microsoft Data Access Components (MDAC) SDK from Microsoft (currently available at http://www.microsoft.com/data). This self-installing file includes the necessary files you’ll need to start developing applications that use ADO. If you’ll be distributing your application to other users, then you also need to ensure that you pick up the redistribution files from the same area. These files are used to install DCOM and ADO libraries on any client machines for your application.

Microsoft hasn’t yet made the installation process as simple as I’d like. Before you can use ADO on a client machine, DCOM95 or DCOM98 must be installed. If you’ve installed Internet Explorer 4.0 or later, then you have the appropriate version. Without IE4, you have at least a two-stage setup for any program you want to distribute.

At the time of this writing, Microsoft had two versions of the MDAC kit: 2.0 and 2.1. Microsoft has published information on its Web site that recommends that all users who don’t have Jet 4.0 (Office 2000 or SQL Server 7.0) installed use version 2.0. There appear to be a number of ODBC problems with MDAC 2.1 that will be addressed when Service Pack 1 for version 2.1 is released. I’d recommend that you stick with version 2.0, just to be safe, until this service pack is released.

Once you’ve installed the SDK, you must add a reference to the one of the Microsoft ADO libraries to your Access database. To add a reference to the library, open any code module, choose References from the Tools menu, and place a checkmark beside the listing for the ADO library that you’ll be using.

Picking the appropriate ADO library can be a little confusing, since there are currently three separate libraries. The Microsoft ActiveX Data Objects 2.0 library is the one you’ll most likely want to use. It includes a fully featured and up-to-date version of all of the objects that ADO has to offer. The Microsoft ActiveX Data Objects Recordset 2.0 library is a much lighter library that takes up less memory in your applications but only contains the Recordset object, along with its properties and collections. This can be very useful if you aren’t planning on managing your connections or executing commands that have parameters. The Microsoft ActiveX Data Objects (Multi-Dimensional) 1.0 library allows you to work with multi-dimensional recordsets, a concept that I’ll return to in an upcoming article. For now, it’s best to stick with the main ADO 2.1 library.

What’s next?

Over the next few months, I’ll be taking you step-by-step through all of ADO’s objects. I’ll do my best to ensure that you can become an expert with ADO. I’ll go in-depth and tell you about each object’s properties, methods, and events. I’ll also provide you with real-world examples of how you can use ADO in your applications. Next month, for instance, I’ll take a look at the base object in ADO: the Connection object.

Other Pages That Might Interest You

Comparing DAO and ADO Recordsets

An ADO Command Factory for Stored Procedures

Using the ADO Command Object

Using the ADO Objects Effectively

Advanced ADO Data Providers

What Every Access Developer Needs to Know About Word

 

Posted in Old Material | Leave a comment

Active Data Objects

OLE DB is interesting, but you can’t get to it from Access. To make OLE DB accessible to VBA programmers, Microsoft has provided ADO. Michael provides an introduction to this new technology.

Active Data Objects (ADO) are the gateway to OLE DB for Access programmers (or anyone using VBA or any scripting language). ADO is an easy-to-program wrapper around OLE DB. Although simpler than Data Access Objects (DAO), ADO provides an object model that allows you to do almost everything DAO does. This article focuses on those essential features of ADO that return a Recordset or produce an effect on data by manipulating underlying tables directly.

The ADO model

ADO is a rich and extremely flexible object model consisting of three primary objects listed below (see Figure 1 for the complete model). Although I’ll focus on the Recordset related methods, these three primary objects use dozens of methods and properties. The ADO help file installed when Active Server Pages (http://microsoft.com/iis/) or the OLE DB SDK (http://microsoft.com/oledb/) is installed is an excellent reference.

  • The Connection object — The primary method of this object, the Open method, defines the location of data by using a connection string (more about that later). Transactions are also controlled by the Connection object. The Execute method of this object can be used to set up the server environment or to call a stored procedure. The Execute method returns, by default, a highly specialized cursor that is read/forward-only (sometimes called a “fire-hose” cursor). The SQL statement that creates this cursor can’t use output parameters or require command preparation. This fire-hose cursor was chosen as the default type because it doesn’t require cursor type and lock type properties.
  • The Command object — This object’s main purpose is to communicate command text (such as SQL strings or directory names) to data providers. When supported by the data provider, this object also identifies stored procedures and querydefs along with their command parameters (both input and output). This object might or might not return a cursor.
  • The Recordset object — This is the richest of the ADO objects and implements dozens of methods and properties. This is where ADO does most of the work.

ADO objects are not strictly hierarchical, which is a departure from tradition. You can create a Connection object, associate it with a Command, fetch a Recordset with that Command object, and then disconnect the Command object from the Connection. With that done, you can use a different Connection object to create another Recordset with the same Command object.


Figure 1

Collecting with ADO

The next group of entities in the world of ADO is the group of collections tied to one or another of the primary objects as shown here:

Object Collection
Connection Errors
Command Parameters
Recordset Fields

Each of these collections will be familiar to Access developers, but each has its own new twist.

The Errors Collection, for example, has a distinctive ability to collect more than one Error object for each ADO-specific error created by the data provider. It’s the responsibility of the data provider to supply the Error objects when things go awry. If the data provider doesn’t produce multiple Error objects, then the routine COM error handlers take over. Don’t expect ADO to put anything in the Error object. In the OLE DB model, that’s up to the data provider.

The Parameters collection is also unconventional. With DAO, parameters were created and fixed at design time. In ADO, Parameter objects can be created at runtime. With the Parameters collection, I deal with each Parameter object explicitly before I invoke the Open method of the Recordset object. If I know the attributes of my querydef’s parameters, I duplicate those qualities with the arguments used in the CreateParameter method. If I don’t know the attributes of the parameters, I can invoke the Refresh method and let the data source generate parameter information. I can use the Parameters collection with SELECT, INSERT, UPDATE, and DELETE SQL statements to minimize the number of trips to the data source. By using a single SQL statement and the Parameters collection, I can have everything done with just one trip to the data source.

The Fields Collection is different in ADO than DAO because ADO must accommodate a wider variety of field types than those typically encountered in Access. Don’t worry, the old Field methods AppendChunk and GetChunk are still around to handle binary large objects. ADO has also made getting access to original field values (the value of a field before it was edited) easier by providing the new OriginalValue property of a Field object. This OriginalValue property is also added in DAO 3.5. With ADO (and DAO 3.5), you can also easily get the value of fields after updates (for ADO it’s the UnderlyingValue property, for DAO 3.5, the VisibleValue property). This UnderlyingValue property is handy if you want to ensure that a field has been updated or if you need to resolve conflicts after batch updates.

That’s an overview of the most important features of ADO. Now let’s take ADO for a spin.

ADO basics

In this section you’ll see examples of required syntax for each of the primary objects of ADO: the Connection, Command, and Recordset objects. I’ll include alternative syntax examples in code snippet comments.

The Connection object connects you to your data provider. In the same way that you need to know the phone number of someone you want to call, the Connection object needs to know the “phone number” of the data source. In ADO, this number is called the Connection object’s ConnectionString property.

If you use the ODBC Provider in ADO, there are two ways to look up this number: through a provided Data Source Name (DSN) or through a detailed string of argument=value pairs separated by semicolons. In either case, the resulting ConnectionString property tells ADO the name of the correct OLE DB provider (and, if necessary, an ODBC driver) needed to access the data source, the location and name of the database, security information when accessing a secure database, and special customization properties exposed by the data provider.

A DSN can come in several forms. A System DSN is created in the Control Panel of the ODBC32 Administrator. When you go this route, the connection string information is stored in the Windows Registry in a key accessible to all users who log in to this computer. A User DSN, on the other hand, is stored with individual user profiles and is not visible to all users. A File DSN, also created in the ODBC32 Administrator, records the same connection information as the other two but stores the information in the file system. If a System DSN is used to define the ConnectionString property, the DSN must exist in the Registry of the machine hosting ADO; a common mistake is to define it on the client machine.

In the case of ADO, there’s an implicit argument in the ConnectionString property called the Provider argument that identifies which data provider to invoke. For ADO, the Provider argument has a default value of “MSDASQL.” Other OLE DB data providers might use other Provider arguments. After intercepting the Provider argument, OLE DB passes the connection string information on to the next layer in data access technology. When going after Access data, that will be the ODBC Provider. The ODBC Provider Users Guide (included with the OLE DB SDK) lists all the keywords recognized by the ODBC Provider. Arguments not recognized by the ODBC Provider are ignored and used by the next layer in the system, the ODBC driver. Individual drivers recognize their own collection of keywords, so you should refer to the ODBC help files for each data source for details.

Getting connected

In the following code, I copied the connection string information from a file DSN:

Notice the second argument, dbq. ODBC drivers might call this argument by other names (for example, SQL Server uses “database,” though FoxPro sticks with “dbq”). If you’re using ODBC, you’ll never get the connection string wrong if you use the ODBC32 Administrator to create a file DSN and then copy the resulting arguments to your connection string.

This snippet creates that “fire-hose” cursor I mentioned earlier. The cursor is inside the objRstConn object variable. I use the name objRstConn to remind myself that I’m dealing with an object that is a Recordset generated by a Connection object. I do this because a Recordset object can also be created from the Command object. Although using the Connection object is convenient, I don’t have full access to the entire Recordset object when I use it.

In other routines in this article, I use the objRst object variable instead. Here, my choice of variable names designates a Recordset object. The name in this case indicates that I’ve created the Recordset object from the Command object. This gives me full access to the Recordset object so that I have complete control in how that Recordset object is defined.

If you forget to invoke the Open method of the Connection object, it’s like talking on the phone before you dial. ADO will complain with the message “Invalid operation on closed object.” All ADO programs must have something like the first two commands in this routine.

Here’s the code to create a Recordset from the Command object in ADO. Not all data providers can use commands, so the Command object is optional. When it’s used, the Command object assumes a Connection object has been created, so this code should be regarded as a continuation of the previous routine:

If you forget to use Set in the second statement in this routine, you’ll create a copy of the connection string in a second connection object (I prefix object variables with “obj” just to remind me to use Set).

Notice some fine points here. The Command object requires you to set two properties before you can invoke the Execute method. The Command object must know where to look for the command, so the ActiveConnection property must be set to a Connection object or to a connect string provided through a System DSN, a File DSN, or directly. The Command object must also know what the command is through the CommandText property. This is set either to the name of a statement in the database or to the actual text of the command to be sent to the database. If you forget either of those properties, you’ll receive one of the following ADO errors:

Missing Property Resulting error
ActiveConnection Invalid operation on object with a closed connection reference.
CommandText No command has been set for the Command object.

Keen-eyed readers will have noticed that the only argument I passed to the Execute method of the Command object was the third one. The adCmdText constant tells the data provider not to bother looking for database objects and that the CommandText is just that — text. If you look closely at the first routine, you’ll see that I passed the constant adCmdTable to the Execute method of the Connection object. This sets the CommandType type property in the Command object to “table.” Either way, the data provider doesn’t have to waste time figuring out how to implement the command. You can also call a querydef or stored procedure by setting the CommandType property of the Command object to adCmdStoredProc.

Remember that the Command object includes the Parameters collection. Parameter objects deserve coverage beyond the scope of this article, for they’re a powerful and convenient resource for busy developers.

Recordsets

The final object that actually yields a Recordset is the Recordset object. Like the Command object, the Recordset object needs a Connection object for the Recordset’s ActiveConnection property. It also needs a Source property that is the equivalent of the CommandText property in the Command object. Here are the error messages that will crop up if you omit either of these parameters:

Missing Open Argument or Recordset Property Resulting Error
ActiveConnection Invalid operation on object with a closed connection reference.
Source No command has been set for the Command object.

I haven’t included all the combinations of syntax you can use with the Open method of the Recordset object. I have, however, demonstrated the two basic styles: the “shortcut” technique of using Open method arguments (for the fire-hose cursor), and the “explicit” strategy of setting Recordset object properties (for the keyset cursor).

Here’s the code to create a fire-hose cursor and a keyset cursor:

This routine uses two new properties, CursorType and LockType. In Access, it was easy to ignore these properties and let DAO make the decisions for you. In ADO, you’re in complete control, and if you don’t specify a CursorType or LockType, you get the default values that produce the fire-hose cursor.

Also, note that the Open method in the Recordset object has five arguments against the three used by the Connection object. Because I wanted to specify that the Source property was a querydef, the only way I could do that was through the fifth argument in the Open method. There’s no separate property equivalent to the CommandType property of the Command object. Using the querydef gives you all the performance optimizations available at the data provider.

What’s next

This article has covered a few essential features of ADO. However, this new technology has many more capabilities that developers should seek to learn. Since this is the wrapper for the next step in Microsoft’s data access strategy, it’s very important for you to become as familiar with ADO as you are with DAO.

More:

An ADO Tutorial: What is ADO?

 

Posted in Old Material | Leave a comment

Accessing ADO

ActiveX Data Objects is the successor to Data Access Objects, and with Access 2000, ADO finally goes mainstream. Here’s a quick look at some neat things that Access 2000 and ADO can do together.

What really brought home to me that ADO was the future of data access came when I loaded up Access 2000. After I started Access 2000, I added a Basic module and went to the References list to see what was listed. Jet 4.0, the new version of DAO, wasn’t even listed. The current version of Jet (3.5) was listed, but it wasn’t checked off. What was both listed and invoked was ADO. For at least the first beta of Access, if you start writing code to get at your database, you’re going to start using ADO. Continue reading

Posted in Old Material | Leave a comment

Access 2006 The Community Speaks

Garry Robinson, Contributing Editor

In the August 2004 issue Access 2006—Have Your Say, Peter and I threw the challenge to the readers of Smart Access to come up with ideas for the next version of Access. At the start of a new year, it seems appropriate to look ahead to what the Access community wants to see in our product. I’m glad to report that the response for this was great—so great that we actually received more than 200 separate ideas about what you folks are really interested in. After sifting through all the suggestions and bundling them into appropriate categories, I have to say there’s a definite message for the Access development team at Microsoft: Get back to the basics—improve the Access we love and stop padding the edges to keep the marketing team happy. Put another way: The job’s not done yet—don’t give up on us. Continue reading

Posted in Old Material | Leave a comment