Transcript ActiveX Data Objects (ADO) is Microsoft`s latest database object

ADO
ActiveX Data Objects (ADO) is Microsoft’s latest database object
model.
The goal of ADO is to allow VB developers to use a standard set
of objects to refer to any OLE DB source.
ADO Objects
Connection
Command
Recordset
Errors
Parameters
Fields
Error
Parameter
Field
Connection
Allows control over the connection to the data source
Recordset
Contains the records that are the result of a query
Command
Executes database commands and queries
Error
Contains information about Errors from ADO
Field
Represents a field in a data set
Parameter
Works with Command to set parameters for stored
procedures or parameter queries
Property
Allows you to access ADO object Properties
Establish Reference to ADO
Before using ADO, you must establish a reference to the ADO library.
Project, References, Microsoft ActiveX Data Objects 2.5 Library
Basic Steps to executing a query using ADO
1.
Create a Connection Object.
2.
Specify the connection string to connect to the database of interest.
3.
Open the connection
4.
Create a Recordset object. The Recordset object contains the results of
the query after execution.
5.
Specify the SQL text. When you open a recordset, you can either use a
table, a stored procedure, or string containing a SQL statement.
6.
Open the recordset.
7.
Opening the recordset executes the query and returns the records to the
recordset object. The records are accessible through the recordset object
now.
Connection Object
A Connection object represents a unique session with a data source. In the
case of a client/server database system, it may be equivalent to an actual
network connection to the server.
Important Properties and Methods
•
•
•
•
•
•
ConnectionString
ConnectionTimeout
Mode
CursorLocation
Provider
Open and Close
Connection Object
'Create ADODB Connection
Dim connAVB As ADODB.Connection
Set connAVB = New ADODB.Connection
connAVB.ConnectionString = "Provider = Microsoft.Jet.OLEDB.3.51;" &
"Data Source=" & App.Path & "\AVB.mdb;Mode=readwrite“
connAVB.Open
Connection Object
'Create ADODB Connection
Dim connAVB As ADODB.Connection
Set connAVB = New ADODB.Connection
connAVB.ConnectionString = "Provider = Microsoft.Jet.OLEDB.3.51;" &
"Data Source=" & App.Path & "\AVB.mdb;Mode=readwrite“
connAVB.Open
‘code
connAVB.Close
Set connAVB = Nothing
Recordset Object
Represents the entire set of records from a base table or the results of an
executed command.
You use Recordset objects to manipulate data from a provider.
Important Properties and Methods
• CursorLocation
• CursorType
• LockType
• Mode
• Open, Close
• MoveFirst, MoveLast, MoveNext, and MovePrevious
• BOF, EOF
• Update, AddNew, Delete
• GetString, GetRows
CursorLocation
Cursor refers to system resources needed to manage a set of data.
Specifies the location of the cursor service.
Constant Value Description
adUseClient -Uses client-side cursors supplied by a local cursor. Use for
desktop applications.
adUseServer - Default. Uses data-provider or driver-supplied cursors. These
cursors are sometimes very flexible and allow for additional sensitivity to
changes others make to the data source. However, some features of the
Microsoft Cursor Service for OLE DB (such as disassociated Recordset
objects) cannot be simulated with server-side cursors and these features
will be unavailable with this setting.
Cursor Types
Dynamic cursor — allows you to view additions, changes, and deletions by
other users; allows all types of movement through the Recordset
Keyset cursor — behaves like a dynamic cursor, except that it prevents you
from seeing records that other users add, and prevents access to records
that other users delete. Data changes by other users will still be visible.
Static cursor — provides a static copy of a set of records for you to use to find
data or generate reports; always allows bookmarks and therefore allows
all types of movement through the Recordset. Additions, changes, or
deletions by other users will not be visible. This is the only type of cursor
allowed when you open a client-side Recordset object.
Forward-only cursor — allows you to only scroll forward through the
Recordset. Additions, changes, or deletions by other users will not be
visible. This improves performance in situations where you need to make
only a single pass through a Recordset.
Lock Types
LockType is important when you have multiple users accessing the same
data.
Lock Type
Constant
Result
N/A
adLockUnSpecified
Read Only
adLockReadOnly
No edits
Optimistic
adLockOptimistic
Locked as each update submitted.
BatchOptimistic adLockBatchOptimistic
Locked when all updates
submitted
Pessimistic
Locked from Recordset creation
adLockPessimistic
Opening a RecordSet
Public Sub MoveX()
' connection and recordset variables
Dim rstAuthors As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQLAuthors As String
' Open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=sqloledb;Data Source=a-iresmi2000;Initial Catalog=pubs;User
Id=sa;Password=;"
Cnxn.Open strCnxn
' Open recordset from Authors table
Set rstAuthors = New ADODB.Recordset
rstAuthors.CursorLocation = adUseClient
Opening a RecordSet
strSQLAuthors = "SELECT au_id, au_fname, au_lname, city, state FROM Authors
ORDER BY au_lname"
rstAuthors.Open strSQLAuthors, strCnxn, adOpenStatic, adLockOptimistic,
adCmdText
rstAuthors.MoveFirst
numRecords = rstAuthors.RecordCount
city = rstAuthors!city
rstAuthors.Close
Cnxn.Close
Set rstAuthors = Nothing
Set Cnxn = Nothing
End Sub
Opening a RecordSet
Source = Table
rsObject.Open tablename, Connection Object, CursorType, LockType,
adCmdTable
Source = Stored Procedure
rsObject.Open stored procedure name, Connection Object,
CursorType, LockType, adCmdStoredProc
Source = string
rsObject.Open SQLstring, Connection Object, CursorType,
LockType, adCmdText