ActiveX Data Objects (ADO)
Download
Report
Transcript ActiveX Data Objects (ADO)
ActiveX Data Objects (ADO)
Performs same functions as DAO with
following improvements:
Simpler command set
Faster execution (sometimes)
Uses less memory
Consumes less disk space
Supports any database to which Access can
connect
ActiveX Data Objects (ADO)
Connection object
Establishes a link to a database
ADO objects should be prefixed with ADODB
Set statement is optional when assigning values
to ADO objects, if you use the New keyword
Example:
Dim cnn As New ADODB.Connection
Cnn.Open CurrentProject.Connection
OR
Set cnn = CurrentProject.Connection
ActiveX Data Objects (ADO)
Opening a recordset
Arguments
Table or query
Database connection
SQL statements may be embedded
Usually the current one stored in the Connection
object variable
Cursor types
Lock types
ActiveX Data Objects (ADO)
Cursor types
adOpenForwardOnly
Read only recordset with ability to scroll
through the records in a forward direction only
adOpenKeyset
Requests a dynaset that can be updated
adOpenDynamic
Updateable, but not available for Jet
databases
adOpenStatic
Read only where updates are not visible
ActiveX Data Objects (ADO)
Lock types
adLockReadOnly
No changes allowed
adLockPessimistic
Lock the record immediately upon entering
adLockOptimistic
Attempt to lock the record when the record is
saved
adLockBatchOptimistic
Used for batch updating
ActiveX Data Objects (ADO)
Update method
Changes saved using Update, moving to another
record or closing the recordset
In DAO, only Update method saves the changes
Find method
Replaces FindFirst, FindLast, etc. from DAO
ActiveX Data Objects (ADO)
Edit method
ADO automatically opens in an edit mode
DAO requires Edit method
ADO or DAO?
ADO is a generic interface to multiple
database providers
DAO is best performer for Jet (Access)
databases
Useful for building client/server and web
applications
ADO has to retrieve info about the database
before it can communicate with it
ADO has security shortcomings for Jet
databases