Using ADO Programmatically

1
Using ADO Programmatically

• The Connection Object and the Command 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

• ConnectionString
• ConnectionTimeout
• CommandTimeout
• Mode
• Provider

5
Connection Object Methods

• Open
• Close
• BeginTrans
• CommitTrans
• RollbackTrans

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
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

8
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

9
Private Sub Form_Load() ' Declare the
Connection object. Dim conMagnet As New
ADODB.Connection ' 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.Connection
String "Data SourceA\Magnet.mdb
conMagnet.Open End Sub
10
The Command Object

• Properties
• ActiveConnection
• CommandType
• adCmdText, adCmdTable, adCmdStoredProcedure
• CommandText
• CommandTimeout
• 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