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.
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
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 (figure19.3)
9.
Then click on Connection
tab (figure 19.4)
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 (shown in figure 19.5)
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.
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.
|
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.
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.
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.
No comments:
Post a Comment