Using ADO Programmatically

1
Using ADO Programmatically

• The Connection Object , Command Object, and
  Recordset Object

2
Using ADO Programmatically

• To perform ADO operations without the aid of the
  ADO Data control
• Establish a connection to a data source
• Define a command
• Execute the command
• Commands may or may not return records. If they
  do, create a recordset.
• Manipulate the records in the recordset as
  desired.

3
ADO Object Hierarchy

• A Connection object represents an open connection
  to a data source.

Connection
Errors
Command
Recordset
4
Connection Object Properties and Methods

• Properties
• ConnectionString
• ConnectionTimeout
• CommandTimeout
• CursorLocation
• Mode
• Provider
• Methods
• Open
• Close

5
Mode Property Values
 
6
Building the Connection Object Programmatically

• Create an instance of an ADO Connection object
• Set the necessary arguments for the
  ConnectionString property of the Connection
  object.
• Call the Open method of the Connection object.

7
Creating an Instance of the ADO Object

• You must create an instance of a particular ADO
  object before you can use it.
• Example
• Dim conMagnet As New ADODB.Connection

8
ConnectionString Property

• The ConnectionString property is a string with
  which you specify a data source.
• It defines connection information in the form of
  argument value pairs separated by semicolons
  (). ADO supports the following arguments
• Provider
• Data Source
• User ID and Password
• File Name
• Remote Provider and Remote Server

9
Private Sub Form_Load() ' Declare the
Connection object. Dim conMagnet As New
ADODB.Connection ' Define the properties
for the Connection object conMagnet.Mode
adModeShareDenyNone conMagnet.CursorLocation
adUseClient conMagnet.Provider
"Microsoft.Jet.OLEDB.4.0" conMagnet.Connection
String "Data SourceA\Magnet.mdb ' Call
the Open Method of the Connection object
conMagnet.Open End Sub
10
The Command Object

• Properties
• ActiveConnection
• CommandType
• adCmdText, adCmdTable, adCmdStoredProcedure
• CommandText
• Methods
• Execute
• Cancel

11
Private Sub Form_Load() ' Declare the Connection
and Command objects. Dim conMagnet As New
ADODB.Connection Dim cmdMagnet As New
ADODB.Command ' Define the properties for the
Connection object ' then open the connection.
conMagnet.Mode adModeShareDenyNone
conMagnet.CursorLocation adUseClient
conMagnet.Provider "Microsoft.Jet.OLEDB.4.0"
conMagnet.ConnectionString "Data
SourceA\Magnet.mdb" conMagnet.Open ' Define
the propeties for the Command object. Set
cmdMagnet.ActiveConnection conMagnet
cmdMagnet.CommandType adCmdTable
cmdMagnet.CommandText "tblOrderMaster End Sub
12
' Module level variable to store a reference to
the form's recordset. Private rstCurrent As New
ADODB.Recordset Private Sub Form_Load() Dim
conMagnet As New ADODB.Connection Dim
cmdMagnet As New ADODB.Command
conMagnet.Mode adModeShareDenyNone
conMagnet.CursorLocation adUseClient
conMagnet.Provider "Microsoft.Jet.OLEDB.4.0"
conMagnet.ConnectionString "Data
SourceA\Magnet.mdb" conMagnet.Open Set
cmdMagnet.ActiveConnection conMagnet
cmdMagnet.CommandType adCmdTable
cmdMagnet.CommandText "tblOrderMaster
rstCurrent.Open cmdMagnet End Sub
13
Private Sub LoadCurrentRecord()
txtOrderID.Text rstCurrent.Fields("fldOrderID")
txtCustomerID.Text rstCurrent.Fields("fldCus
tomerID") txtOrderDate.Text
rstCurrent.Fields("fldOrderDate")
txtOrderCost.Text rstCurrent.Fields("fldOrderCos
t") txtOrderSales.Text rstCurrent.Fields("fl
dOrderSales") lblCurrentRecord.Caption
rstCurrent.AbsolutePosition
lblRecordCount.Caption rstCurrent.RecordCount En
d Sub
14
Looping Through Records in a Recordset
Do Until rstCurrent.EOF cboMemID.AddItem
rstCurrent("Member ID")
rstCurrent.MoveNext Loop