Microsoft Corporation
March 19, 1998
The Microsoft® ActiveX® Data Objects (ADO) object model defines a collection of programmable objects that can be used in any language that supports both COM and Automation. This includes Microsoft Visual Basic®, Microsoft Visual C++®, Microsoft Visual J++™, Microsoft Visual FoxPro®, Microsoft Visual Basic, Scripting Edition (VBScript), and many others. The ADO object model is designed to expose the most commonly used features of OLE DB in an easy-to-use manner.
The ADO object model contains seven objects: Connection, Recordset, Command, Parameter, Field, Error, and Property.
By using the Connection object, you can establish connection sessions with data sources. It provides a mechanism for initializing and establishing the connection, executing queries, and using transactions. The Open method is used to establish a connection. The State property is used to determine the current state of the connection.
Sub ConnectionExample()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
' Open the connection, specifying data source name,
' userid, and password.
cn.Open "Pubs", "sa", ""
' If the connection is open, say so.
If cn.State = adStateOpen Then
Debug.Print "Connection is open."
End If
' Close the connection.
cn.Close
End Sub
The Recordset object provides methods for manipulating data. You use the Recordset object to add, update, delete, and scroll through records in the recordset. Updates on the Recordset object can be in an immediate or batch mode. When a Recordset object is created, a cursor is automatically opened.
Sub RecordsetExample()
Dim rs As New ADODB.Recordset
' Open the recordset, specifying a SQL statement
' and a connection string.
rs.Open "Select * from titles", "DSN=pubs;UID=sa"
' Loop through the recordset and print the first field.
Do While Not rs.EOF
Debug.Print rs(0)
rs.MoveNext
Loop
' Close the recordset.
rs.Close
End Sub
By using the Command object, you can issue commands to the database. These commands can be, but are not limited to, query strings, prepared query strings, and associated parameters with query strings. The Command object can either open a new connection or use an existing connection to perform queries, depending on what is specified in the ActiveConnection property. Executing query strings can generate a recordset, multiple recordsets, or no recordset at all.
Sub CommandExample()
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
' Set the command's connection using a connection string.
cmd.ActiveConnection = "DSN=pubs;UID=sa"
' Set the command's text, and specify that it
' is an SQL statement.
cmd.CommandText = "Select * from titles"
cmd.CommandType = adCmdText
' Create a recordset by executing the command.
Set rs = cmd.Execute()
' Loop through the recordset and print the first field.
Do While Not rs.EOF
Debug.Print rs(0)
rs.MoveNext
Loop
' Close the recordset.
rs.Close
End Sub
The Parameters collection provides parameter information and data for the Command object. The Parameters collection consists of Parameter objects. Individual parameter information (for example, size, data type, direction, and value) can be read or written through each Parameter object. There are four types of parameter directions: input, output, both input and output, and return value.
Sub ParameterExample()
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm As ADODB.Parameter
' Set the command's connection using a connection string.
cmd.ActiveConnection = "DSN=pubs;uid=sa"
' Set the command's text, and specify that it
' is an SQL statement.
cmd.CommandText = "byroyalty"
cmd.CommandType = adCmdStoredProc
' Set up a new parameter for the stored procedure.
Set prm = cmd.CreateParameter("Royalty", adInteger, adParamInput, , 50)
cmd.Parameters.Append prm
' Create a recordset by executing the command.
Set rs = cmd.Execute
' Loop through the recordset and print the first field.
Do While Not rs.EOF
Debug.Print rs(0)
rs.MoveNext
Loop
' Close the recordset.
rs.Close
End Sub
You can use the Errors collection and Error object to retrieve provider error information when an error occurs. Errors can be generated by a method call or property of the Connection, Command, or Recordset object but are always retrieved from the Connection object. By using the Error object, you can retrieve the error description and source of the error. The SQLSTATE and database-native error information is also available from the Error object when working with the ODBC Provider.
Sub ErrorExample()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
On Error GoTo AdoError
' Try and open a connection using a DSN that doesn't exist.
cn.Open "MissingDSN", "sa", ""
Exit Sub
AdoError:
Dim Errs As ADODB.Errors
Dim errLoop As Error
Dim strError As String
' Loop through each Error object in the
' Errors collection and display properties.
Set Errs = cn.Errors
For Each errLoop In Errs
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next
End Sub
You use the Fields collection and Field object to access each data column of the current record. The Fields collection can be accessed through the Recordset object. You can use the Field object to retrieve the name, type, and values of each data field of the current record.
Sub FieldExample()
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Set rs = New ADODB.Recordset
' Open the recordset, specifying an SQL statement
' and a connection string.
rs.Open "Select * from authors", "DSN=pubs;UID=sa"
Debug.Print "Fields in Authors Table:" & vbCr
' Loop through each Field object in the
' Fields collection of the table and display properties.
For Each fld In rs.Fields
Debug.Print "Name: " & fld.Name & vbCr & _
"Type: " & fld.Type & vbCr & _
"Value: " & fld.Value
Next fld
' Close the recordset.
rs.Close
End Sub
ADO objects have two types of properties: built-in and dynamic. Built-in properties are those implemented in ADO, whereas the underlying data provider defines dynamic properties. A dynamic Property object has four built-in properties of its own: Name, Type, Value, and Attributes. Property objects can be found in the Properties collection of the ADO objects.
Sub PropertyExample()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
' Open the connection, specifying data source name,
' userid, and password.
cn.Open "pubs", "sa"
' Set the command's connection using a connection string.
Set cmd.ActiveConnection = cn
' Set the command's text, and specify that it
' is a SQL statement.
cmd.CommandText = "Select * from titles"
cmd.CommandType = adCmdText
' Create a recordset by executing the command.
Set rs = cmd.Execute()
' Show the ConnectionTimeout property of the connection.
Debug.Print cn.Properties("Connect Timeout")
' Show the CommandTimeout property of the command.
Debug.Print cmd.Properties("Command Time out")
' Show the Updatability property of the recordset.
Debug.Print rs.Properties("Updatability")
' Close the recordset.
rs.Close
End Sub