using ado programmatically

Using ADO Programmatically

The Connection Object and the Command Object

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.

ADO Object Hierarchy

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

Connection

Command

Recordset

Errors

Connection Object Properties

• ConnectionString

• ConnectionTimeout

• CommandTimeout

• Mode

• Provider

Connection Object Methods

• Open

• Close

• BeginTrans

• CommitTrans

• RollbackTrans

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.

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

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

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.ConnectionString = "Data Source=A:\Magnet.mdb” conMagnet.Open

End Sub

The Command Object

• Properties– ActiveConnection– CommandType

• adCmdText, adCmdTable, adCmdStoredProcedure

– CommandText– CommandTimeout

• Methods– Execute– Cancel


' Declare the Connection and Command objects.


Dim cmdMagnet As New ADODB.Command

' Define the properties for the Connection object

' then open the connection.




conMagnet.ConnectionString = "Data Source=A:\Magnet.mdb"

conMagnet.Open

' Define the propeties for the Command object.

Set cmdMagnet.ActiveConnection = conMagnet

cmdMagnet.CommandType = adCmdTable

cmdMagnet.CommandText = "tblOrderMaster“

End Sub

' Module level variable to store a reference to the form's recordset.

Private rstCurrent As New ADODB.Recordset



Dim cmdMagnet As New ADODB.Command




conMagnet.ConnectionString = "Data Source=A:\Magnet.mdb"

conMagnet.Open

Set cmdMagnet.ActiveConnection = conMagnet

cmdMagnet.CommandType = adCmdTable

cmdMagnet.CommandText = "tblOrderMaster“

rstCurrent.Open cmdMagnet

End Sub

using ado programmatically

Documents