ActiveX Data Objects (ADO)

*  What is OLEDB?

*  ActiveX Data Object model

*  Connection, Command and RecordSet objects

*  How to use ADODC to access data?

*  What are the properties of ADODC?

*  What are the events of ADODC?

ADO is another data access method. ADO data model has a collection of objects using which you can access and manipulate any database that is based on OLEDB interface (more about it later). ADO model is simple. It has fewest objects in its object model.  ADO is the data model for databases that are accessed using OLEDB interface, where as RDO is the data model for databases that are accessed using ODBC interface.

What is OLEDB (OLE Database)?

OLEDB is an entirely new method to connect to databases.  OLEDB is replacing ODBC. OLEDB provides flexibility and ease of use than ODBC. OLEDB was designed to allow access to data of various formats. That means the data need not be in the form of a relation database and accessed using SQL. 

OLEDB needs a provider, which is a program that can sit between the application and the database (something like an ODBC driver). So when you want to access a database using OLEDB interface, you have to make sure that you have OLEDB provider for that database. Microsoft has provided the following OLEDB providers.

¨       Microsoft OLE DB provider for ODBC drivers

¨       Microsoft Jet 3.51 OLEDB provider

¨       Microsoft OLE DB provider for Oracle

¨       Microsoft OLE DB provider for SQL Server

¨       Microsoft OLE DB provider for Directory Services

Note: You can access any ODBC data source through OLEDB using OLE DB provider for ODBC drivers, if no OLEDB provider is available.

ActiveX Data Object Model

This is a collection of objects that is used to access the database using OLEDB as the underlying interface. ADOs are simple compared with DAO and RDO.

ActiveX Data Objects allow you to access only data and not the schema (table definitions, column definitions etc).  As the result it has very few objects in it object model.

The following are the objects in ActiveX data object model.

Object	What it does?
Connection	Allows you to connect to the database.
Command	Is used to execute commands that are supported by the database.
RecordSet	Contains the collection of records retrieved by executing a query.

Table 19.1: Important objects in ADO data model.

There are some more objects in the object model of ADO. But the three objects mentioned above are the most important objects.

Let us discuss about each of these objects.

Connection Object

Connection object allows the application to connect to the database.  It represents an open connection to the data source.

To establish a connection with a particular database, you have to set the ConnectionString property to the required string, which specifies the OLEDB provider to be used and the data source to be accessed.

To connect to Products.Mdb, enter:

Dim con As ADODB.Connection

 Set con = New ADODB.Connection

 

 con.ConnectionString = "PROVIDER=Microsoft.jet.OLEDB.3.51; _

                   DATA SOURCE=D:\srikanth\products.mdb"

 con.Open   ‘ open the connection

 ‘ Or you can also supply connectionstring at the time of opening the

 ' connection

 con.Open "PROVIDER=Microsoft.jet.OLEDB.3.51; _

              DATA SOURCE= D:\srikanth\products.mdb"

The following are the important collections, methods, and properties of the Connection object

Type	Name	Meaning
Collections	Errors	Contains error objects related to a single failure.
	Properties	Contains all the properties specific to collection.
Method	BeginTrans, CommitTrans, Rollbacktrans	Are used to start a new transaction and either to commit or rollback the transaction.
	Open	Open a new connection to the data source.
	Execute	Execute the given command. If command is a query, it returns a recordset object.
	Openschema	Returns information about the data source.
Property	Connectionstring	Contains information that is used to connect to data source.
	Provider	Indicates the name of the provider.
	Mode	Indicates the available permissions for modifying data in a connection.
	State	Indicates whether connection object is opened or closed.
	Cursorlocation	Indicates the cursor location of the cursor engine. For more details, see “Cursor Location property”.
	DefaultDatabase	Indicates the default database for the connection object.

Table 19.2: Members of Connection object.

CursorLocation Property

Determines the location where cursor is managed.  The following are the valid settings.

Constant	Description
AdUseNone	No cursor services are used. (This constant is obsolete and appears solely for the sake of backward compatibility.)
AdUseClient	Uses client-side cursors supplied by a local cursor library. Local cursor engines will often allow many features that driver-supplied cursors may not, so using this setting may provide an advantage with respect to features that will be enabled. For backward compatibility, the synonym adUseClientBatch is also supported.
AdUseServer	Default. Uses data-provider– or driver-supplied cursors. These cursors are sometimes very flexible and allow for additional sensitivity to changes other user make to the data source.

Table 19.3: Setting for CursorLocation property.

Command Object

A Command object is a definition of a specific command that you intend to execute against a data source. The following are important collections, methods and properties.

Type	Name	Meaning
Collection	Parameters	Contains all the parameters of the command object.
	Properties	Contains properties that are specific to command object.
Method	CreateParameter	Creates a new parameter with the specified properties. For more information, see “Parameter Object”.
	Execute	Executes the given command.
	Cancel	Cancels the execution of an asynchronous command.
Property	ActiveConnection	Specifies to which connect the command object should belong.
	CommandText	Contains the command that you want to execute.
	Commandtype	Indicates the type of command. Depending upon this the CommandText is interpreted.
	Prepared	If set to true, the compiled version of the command is saved before first execution of the command. This improves performance from second execution onwards.

Table 19.4: Members of Command object.

The following example uses a command object to execute an Update command:

Private Sub Command1_Click()

    Dim con As New ADODB.Connection

    Dim com As New ADODB.Command

    Dim p1 As ADODB.Parameter

    Dim p2 As ADODB.Parameter

   Dim ref As Long

   con.Open "PROVIDER=Microsoft.jet.OLEDB.3.51;DATA SOURCE=D:\vb60\biblio.mdb"

   Set com.ActiveConnection = con

   com.CommandType = adCmdText

   com.CommandText = "Update Authors set [year born]=? where au_id=?"

   Set p1 = com.CreateParameter("yb", adInteger, adParamInput, , txtyb.Text)

   Set p2 = com.CreateParameter("auid", adInteger, adParamInput, , txtauid.Text)

   com.Parameters.Append p1

   com.Parameters.Append p2

   'Execute the command

  

   com.Execute ref

  

   If ref = 0 Then

     MsgBox "Updation is not successful"

   Else

     MsgBox "Updation is successful"

   End If

End Sub

Listing 19.2: Sample code using Update command.

Recordset object

A Recordset object represents a set of records taken from the given base table or retrieved from the database using the given query. Though Recordset contains a collection of records, only one record can be accessed at a time, which is called as the current record. However, you can move next and move previous to access all the records in the Recordset.

The following are the important collections, methods, and properties of Recordset object.

Type	Name	Meaning
Collection	Fields	Contains the details of all the fields in the Recordset.
	Properties	Contains the properties that are specific to Recordset object.
Methods	Addnew	Adds a new blank record at the end of recordset.
	CancelBatch	Cancels a pending batch update.
	CancelUpdate	Cancels a pending update. But it should be called before Update method is invoked.
	Clone	Creates a duplicate recordset from an existing recordset.
	GetRows	Returns the data from the given number of rows and given fields starting at the specified record (otherwise current record). The return value is to be copied into a double dimension array.
	NextRecordset	Returns the next recordset when used with compound command statement, where you can give multiple select commands and execute them one after another.
	Requery	Updates the data in the Recordset by executing the query on which Recordset is based.
	Resync	Refreshes either current record or the specified record with Filter property or all records with the underlying records.
	Supports	Returns true if the specified functionality is supported by Recordset. For the list of features, please see on-line documentation.
	Close	Closes recordset and releases memory associated with recordset.
	Delete	Deletes current record in the recordset.
	Move, MoveFirst, MoveNext, MovePrevious and MoveLast	Used to move record pointer within the recordset.
	Update	Makes the changes made to recordset permanent.
	UpdateBatch	Writes all pending batch updates to disk.
Properties	AbsolutePage	Contains the absolute page number of the page in which current record is.
	AbsolutePosition	Returns the ordinal position of the current record in the recordset.
	ActiveConnection	Contains the connection object to which recordset belongs.
	PageCount	Returns how many pages of records are there in recordset.
	PageSize	Specifies how many records constitute one page. Default is 10.
	RecordCount	Contains the number of records the recordset object currently has.
	MaxRecords	Indicates the maximum number of records to be sent to recordset from query.
	Status	Indicates the status of the current record with respect to batch updates or other bulk operations.
	State	Returns the current state of the recordset object – whether opened or closed and so on.
	Cachesize	Indicates the number of records from the Recordset object that are cached locally.
	CursorLocation	Indicates the location where cursor is managed.
	CursorType	Determines the type of cursor to be used to manage records of the recordset.
	LockType	Specifies the type of lock that is to be applied on records during editing.
	EditMode	Returns the editing status of the current record. See EditMode below.
	MarshalOptions	Specifies whether all records are marshaled or only modified records are marshaled.
	Filter	Allows you to specify which records are to be filtered based on either the given condition  or array of bookmarks or the specified options, such as specifying only effected records are to be displayed.
	BOF, EOF	Return true when the record pointer reaches either beginning of the file or end of the recordset.
	Source	Contains the source from where records were taken into Recordset.
	Bookmark	Returns the bookmark of the current record or changes the current record to the one whose bookmark is set to this.

Table 19.5:  Collections, Methods and Properties of Recordset object.

EditMode property

Returns the editing status of the current record in the recordset. The mode may be any of the following.

Option	Meaning
AdEditNone	Indicates no editing operation is in progress.
AdEditInProgress	Indicates that the current record is being edited. That means it has been modified but not yet saved.
AdEditAdd	Indicates that AddNew method has been invoked and new record is not yet saved.

Let us write a snippet that displays the details of all authors using Recordset object.

Private Sub Command2_Click()

   Dim con As New adodb.Connection

   Dim rs As adodb.Recordset

     

   con.Open "PROVIDER=Microsoft.jet.OLEDB.3.51;DATA SOURCE=D:\vb60\biblio.mdb"

    

   'Execute query and get records into recordset

  

   Set rs = con.Execute("select * from authors where au_id < 10")

  

   Do Until rs.EOF

      Print rs("au_id"), rs("author")

      rs.MoveNext

   Loop

   rs.Close

   Set rs = Nothing

   con.Close

   Set con = Nothing

End Sub

Listing 19.3: Using RecordSet object to display details of Authors.

That is all about three major objects – connection, command and Recordset – of ActiveX Data Objects. We will see more usage of ADOs later in this chapter.

Using ActiveX Data Control

We have used data control, which is a control in standard set of controls. Now we will use an ActiveX control called as ActiveX Data Control, which uses ADOs to access a database for which we have an OLEDB provider.

Here are the steps to display the details of publishers taken from biblio.mdb.

1.       Start a new project using File->New Project and select Standard Exe as the type of the project.

2.       Load Microsoft ADO Data Control 6.0 (OLEDB) into project using Project->Components option and check the above mentioned ActiveX control.

3.       Place ADODC (ActiveX data objects data control) on the form.

4.       Select ADODC properties options from popup menu of the ADODC control.

5.       This brings up property pages of ADODC.

6.       Click on Build button in Use Connection String  radio button.

7.       ADODC displays Data Link Properties windows.

8.       Make sure Provider tab is selected and select Microsoft jet 3.51 OLEDB  Provider

9.       Then click on Connection tab

10.   Click on three dots and select the name of data file that you want to access.

11.   At this stage you can test your connection to the database by clicking on Test Connection button.

12.   Advanced tab mainly deals with access permissions, and All tabs displays the entire information.

13.   Click on Ok in Data Link Properties window to close it and come back to property pages of  activex data control.

14.   At this stage you should see connect string created by Visual Basic placed in Use connection String option button.

15.   Now select RecordSource tab and adCmdTable from the type of the command dropdown list box.

16.   Click on down arrow for the next combo box and  from the list of valid tables select Authors tables

17.   Click on Ok to close property pages.

18.   Place three textboxes on the form and corresponding labels. Change the properties of these controls as follows.

Control	Property	Value
Text1	Datasource	Adodc1
	Datafield	Au_id
	Name	txtauid
Text2	Datasource	Adodc1
	Datafield	Author
	Name	txtauthor
Text3	Datasource	Adodc1
	Datafield	Year born
	Name	Txtyb

19.   Change remaining properties as required.

20.   Run the project to get the details of authors into three textboxes.

Note: This application is same as the application we developed in “Accessing Data using Data Control” section of chapter 15. But instead of using data control, we have used an ADODC to access the data. And there the underlying objects are Data access object and here the objects are ActiveX Data Objects.

ActiveX Data Control does the same job as the standard Data Control but in a different way. The fundamental difference is; standard data control is based on ADO and ADODC is based on ADO & OLEDB.  And there are different set of events and properties for ADODC.

Properties of ADODC

The following are properties that are specific to ADODC.

Property	Meaning
BOFAction	Specifies the action to be taken when record pointer reaches beginning of the underlying Recordset.
CacheSize	Specifies the number of records to be kept in the memory.
Caption	The message to be displayed on the control.
Commandtimeout	Specifies the amount of time to wait for the command to return. Time is specified in seconds. Default is 30.
Commandtype	Specifies the type of command. Valid options are, adcmdUnKnown, adCmdText, adCmdTable, and adCmdStoredProc.
ConnectionString	Contains the information required to connect to the database.
ConnectionTimeout	The number of seconds to wait before aborting connection. This is specified in seconds. Default is 15.
CursorLocation	Sepecifies whether client-side or server-side cursors are used.  Default is adUseClient – client side cursor.
CursorType	Specifies which type of cursor is to be used. Default is adOpenStatic.
EOFAction	Indicates the action to be taken when record pointer reaches end of the recordset.
LockType	Specifies the type of lock to be used. The default is adLockOptimistic.
Maxrecords	Specifies the number of records retrieved at the time of opening the underlying recordset.
Mode	Specifies in which mode the recordset is to be opened. The mode of recordset determines what operations other users can perform on the Recordset.
Orientation	Whether to display recordset vertically or horizontally.
Password	Password of the user currently connected.
RecordSource	The name of the table or stored procedure or SELECT command that supplies data.
Username	Name of the user currently connected.

The following is an example where the ADODC’s data source is changed and underlying recordset is recreated.

With Adodc1

    .CommandType = adCmdText

    .LockType = adLockReadOnly

    .CursorType = adOpenStatic

    .RecordSource = "select * from authors where au_id < " &  txtau_id.Text

    .Mode = adModeReadWrite

    .Refresh  ' recreate recordset

End With

Note: Refresh method of ADODC is used to reopen the database and recreate the underlying Recordset. The other method available for ADODC is UpdateControl, which refreshes the data of bound control with the data of current record.

Events of ADODC

The events of ADODC can be divided into two main groups. One group contains events that occur before the operation is performed (WillMove) and another group contains events, which occur after the operation is completed (MoveComplete).

Will events which will occur before the action takes place and gives you an opportunity to cancel the operation that is about to take place using cancel parameter. See the example later.

The following are the events that are specific to ADODC.

Event	When it occurs?
Fetchprogress	Periodically during a lengthy fetch operation.
FetchComplete	After all records of the recordset are fetched.
WillMove	Before record pointer moves from one record to another record.
Movecomplete	After record pointer has moved from one row to another.
EndOfRecordset	When record pointer has moved past the last record.
WillchangeRecordset	Before a change is made to recordset.
RecordsetChangeComplete	After change is made to recordset.
WillchangeRecord	Before the changes of the current record are sent to database.
RecordChangeComplete	After the record in the data source is modified with the data in the recordset.
WillChangeField	Before the current fields in the recordset is updated.
FieldChangeComplete	After the current field is updated.

Table 19.7: Events of ADODC.

To cancel user moving to last record:

Private Sub Adodc1_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

‘ if user is moving to last record

 If adReason = adRsnMoveLast Then

       adStatus = adStatusCancel    ‘ cancel the operation

 End If

End Sub

Listing 19.3:Code to prevent moving to last record.