ActiveX Data Objects (ADO)
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.
Figure 19.1: 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 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; _
con.Open ‘ open the connection
‘ Or you can also supply connectionstring at the time of opening the
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
|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.
Determines the location where cursor is managed. The following are the valid settings.
|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.
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.
|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)
‘Execute the command
If ref = 0 Then
MsgBox “Updation is not successful”
MsgBox “Updation is successful”
Listing 19.2: Sample code using Update command.
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.
|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.|
|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.
Returns the editing status of the current record in the recordset. The mode may be any of the following.
|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”)
Set rs = Nothing
Set con = Nothing
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.
- Start a new project using File->New Project and select Standard Exe as the type of the project.
- Load Microsoft ADO Data Control 6.0 (OLEDB) into project using Project->Components option and check the above mentioned ActiveX control.
- Place ADODC (ActiveX data objects data control) on the form.
- Select ADODC properties options from popup menu of the ADODC control.
- This brings up property pages of ADODC.
- Click on Build button in Use Connection String radio button.
- ADODC displays Data Link Properties
- Make sure Provider tab is selected and select Microsoft jet 3.51 OLEDB Provider (figure19.3)
- Then click on Connection tab (figure 19.4)
- Click on three dots and select the name of data file that you want to access.
- At this stage you can test your connection to the database by clicking on Test Connection
- Advanced tab mainly deals with access permissions, and All tabs displays the entire information.
- Click on Ok in Data Link Properties window to close it and come back to property pages of activex data control.
- At this stage you should see connect string created by Visual Basic placed in Use connection String option button.
- Now select RecordSource tab and adCmdTable from the type of the command dropdown list box.
- Click on down arrow for the next combo box and from the list of valid tables select Authors tables (shown in figure 19.5)
- Click on Ok to close property pages.
- Place three textboxes on the form and corresponding labels. Change the properties of these controls as follows.
- Change remaining properties as required.
- 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.
Figure 19.2: General Property Page of ADODC.
Figure 19.3: List of Providers.
Properties of ADODC
The following are properties that are specific to ADODC.
|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.
Figure 19.4: Connection attributes.
Figure 19.5: Record Source attributes.
The following is an example where the ADODC’s data source is changed and underlying recordset is recreated.
.CommandType = adCmdText
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.RecordSource = “select * from authors where au_id < ” & txtau_id.Text
.Mode = adModeReadWrite
.Refresh ‘ recreate recordset
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
Listing 19.3:Code to prevent moving to last record.