ActiveX Data Object

1
ActiveX Data Object

• ISYS 562

2
ADO

• An ActiveX control
• ActiveX is build upon COM, a contract that
  defines a standard interface by which objects
  communicate.
• ADO works with several different DBMSs.
• Can use ODBC DSN.

3
A Client/Server Access Application
Request
Access Application
Database Server
Response
4
Using ODBC

• Windows 2000/2003
• Control Panel /Administrative Tools/DataSource(ODB
  C)
• Three types of data source names
• System DSN

5
Using ADO

• Must reference ADO objects. In VBE
• Tools/Reference/
• click Microsoft ActiveX Data Object library 2.7

6
ADO Object Model

• There are three main objects on which the ADO
  object models is built
• Connection object make connection to the
  database.
• Command object run SQL against the database.
• Recordset object holds some or all of the
  records in database.

7
ADO Programming Object Model
Connection
Errors
Error
Command
Parameters
Parameter
RecordSet
Fields
Field
8
Connection with a Connection Object

• A connection object represents a unique session
  with a data source.
• Connection string database, OLE DB provider,
  password, if any, security, etc.
• Use the Open method to open a connection.
• Use the Execute method to execute SQL.
• Manage transaction BeginTrans, CommitTrans,
  rollBackTrans.

9
Providers

• MSDASQL
• Microsoft OLE DB Provider for ODBC
• Microsoft.Jet.OLEDB.4.0
• MSDAORA For Oracle
• SQLOLEDB Microsoft SQL Server

10
Connecting String

• Containing information about database, OLE DB
  provider, password, if any, security, etc.
• For Jet database
• Dim cn As ADODB.Connection
• Set cn New ADODB.Connection
• cn.Open ("providermicrosoft.jet.oledb.4.0data
  sourcec\salesDB.mdb")

11
ODBC DSN Connection

• When using the ODBC provider, the connection
  string can be a DSN. For example
• Dim Cn as ADODB.Connection
• Set Cn New ADODB.Connection
• cn.Open ("providermsdasqldsnmySalesDB")

12
Connection Object Methods

• OPEN
• cn.Open ("providermicrosoft.jet.oledb.4.0data
  sourcec\salesDB.mdb")
• Close
• Execute
• Return a recordset after executing a SQL Select
  statement.
• Usually is used to execute SQL Insert and Delete
  that dont return set of records.
• Demo

13
Command Objects Properties Methods

• Command object properties
• ActiveConnection Current connection
• CommandType adCmdText, adCmdTable,
  adCmdStoredProc, etc.
• CommandText Table name if command type is
  adCmdTable, SQL statement if command type is
  adCmdText.
• Command object methods
• Execute

14
Creating ADO Recordset Objects

• Connection objects Execute method
• Command objects Execute method
• Recordsets Open method

15
Types of Recordset
16
Creating Recordset Using ConnectionForwardOnly
Option Explicit Dim cn As ADODB.Connection Dim rs
As ADODB.Recordset Private Sub Form_Load() Set cn
New ADODB.Connection cn.Open ("providermicrosof
t.jet.oledb.4.0data sourcec\salesDB.mdb") Set
rs cn.Execute("select from customer",
adCmdText)
17
Creating Recordset Using Command ForwardOnly
Option Explicit Dim cn As ADODB.Connection Dim rs
As ADODB.Recordset Dim cm As ADODB.Command Private
Sub Form_Load() Set cn New ADODB.Connection cn.
Open ("providermicrosoft.jet.oledb.4.0data
sourcec\salesDB.MDB") Set cm New
ADODB.Command cm.CommandType adCmdText cm.Comman
dText "select from customer Set
cm.ActiveConnection cn Set rs
cm.Execute Text1 rs.Fields(0) Text2
rs.Fields(1) End Sub
18
Creating Recordset Using Recordsets Open Method
(Can open any type of recordset)
Dim cn As ADODB.Connection Set cn New
ADODB.Connection cn.Open ("providermsdasqldsnmy
SalesDB") Dim rs As ADODB.Recordset Set rs New
ADODB.Recordset rs.Open "customer", cn,
adOpenKeyset Table or SQL Dim rc As Integer Do
While Not rs.EOF rc rc 1 rs.MoveNext Loop MsgB
ox (rc)
19
Recordset Properties

• BOF, EOF
• BookMark
• CursorLocation
• CursorType
• LockType
• MarshalOption
• MaxRecords
• RecordCount
• Sort

20
Recordset Methods

• AddNew, Update, Delete
• Find Search a recordset.
• MoveFirst, MoveLast, MoveNext, MovePrevious
• Requery Re-Execute the query.
• GetRows Fetch records to an array.

21
Recordset Events

• FetchProgress, FetchComplete
• WillChangeField, FieldChangeComplete
• WillMove, MoveComplete
• WillChangeRecord, RecordChangeComplete
• WillChangeRecordset, RecordsetChangeComplete
• EndOfRecordSet

22
Using SQL with ADO

• SQL Select commands
• Connection objects Execute method
• Command objects Execute method
• RecordSet objects Open
• SQL Update commands Insert, Delete, Update
• Connection objects Execute method
• Cn.Execute Update EmpFile Set Salary
  Salary1.05
• Command objects Execute method
• sqlSTr " Update EmpFile Set Salary
  Salary1.05
• cm.CommandType adCmdText
• cm.CommandText sqlSTr
• cm.ActiveConnection cn
• cm.Execute

23
Private Sub Form_Load() Dim cn As
ADODB.Connection Set cn New ADODB.Connection cn.
Open ("providermicrosoft.jet.oledb.4.0data
sourcec\salesDB.mdb") 'cn.Open
("providermsdasqldsnmySalesDB") Dim rs As
ADODB.Recordset Set rs New ADODB.Recordset rs.Op
en "select cid from customer", cn,
adOpenKeyset Do While Not rs.EOF List0.AddItem
(rs.Fields("cid")) rs.MoveNext Loop End
Sub Private Sub List0_Click() Dim cn As
ADODB.Connection Set cn New ADODB.Connection cn.
Open ("providermicrosoft.jet.oledb.4.0data
sourcec\salesDB.mdb") 'cn.Open
("providermsdasqldsnmySalesDB") Dim rs As
ADODB.Recordset Set rs New ADODB.Recordset rs.Op
en "select cname from customer where cid '"
List0 "'", cn, adOpenKeyset MsgBox
(rs.Fields(0)) End Sub
24

• Dim rating As String
• If Frame13 1 Then
• rating "A"
• ElseIf Frame13 2 Then
• rating "B"
• Else
• rating "C"
• End If
• db.Execute ("update customer set rating'"
  rating "' where cid'" List0 "'")
• Text9 rating

25
Recordsets AddNew

• A form with unbound textboxes is initialized for
  input.
• After entering data, a Save/Add button is clicked
  and the event handler uses a pair of AddNew and
  Update methods to insert record.
• Note We can also use SQL Insert

26
Unbound Control Input Example
Private Sub Command8_Click() Dim cn As
ADODB.Connection Set cn New ADODB.Connection cn.
Open ("providermicrosoft.jet.oledb.4.0data
sourcec\salesDB.mdb") 'cn.Open
("providermsdasqldsnmySalesDB") Dim rs As
ADODB.Recordset Set rs New ADODB.Recordset rs.Op
en "customer", cn, adOpenDynamic,
adLockOptimistic With rs .AddNew
.Fields("cid") Text0 .Fields("cname")
Text2 .Fields("city") Text4
.Fields("rating") Text6 .Update End
With End Sub
27
Find Method

• Syntax adoRecordsetName.Find criteria
• Only accept simple comparison criteria.
• Example
• recordset.Find CID txtCID
• Finding employees hired between 2/15/2000 and
  5/15/2000
• Adodc1.recordset.Find Hdate gt 2/15/2000
• Then use IF statement in program to filter out
  those hired after 5/15/2000
• Note Search starts from the current position.
  To search from the beginning, must do MoveFirst
• If no match is found, the EOF of the recordset
  will be set to True.
• Note The search starts from the current position
  of the recordset. To start from the beginning,
  use the MoveFirst method.

28
Find Code Example
Dim searchID As String searchID InputBox("enter
searchID") rs.MoveFirst rs.Find "cid'"
searchID "'" If Not rs.EOF Then Text0
rs.Fields("cid") Text2 rs.Fields(1) Else
MsgBox ("not found") End If
Demo
29
Using the BookMark

• Bookmark is a variant type property that keeps
  the current recordsets position. This position
  can be saved in a variant variable and later go
  to that position.

30
BookMark Example
To save bookmark Dim BkMark As
Variant Private Sub Command5_Click() BkMark
rs.Bookmark End Sub To go to the saved bookmark
location Private Sub Command6_Click() rs.Book
mark BkMark Text0 rs.Fields("cid") Text2
rs.Fields("cname") End Sub