ActiveX Data Objects (ADO)

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.




OLEDB interface.

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.

General Property Page of ADODC.

List of Providers.

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.

Table 19.6: Properties of ADODC.

Connection attributes.
Record Source attributes.


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.