Command

The ADO Command object represents SQL commands, roughly equivalent to queries in Access databases, or QueryDefs in the DAO object model. You don't need to use this object to query or filter Access data; this can be done using a SQL statement (as in the preceding code segment) or the name of a saved query for the Source argument when opening a recordset. However, the Command object can be useful when you want to reuse a command later in the code, or if you need to pass detailed parameter information with the command.

The procedure uses a Command object to create a recordset, which can be used later in the code:

Private Sub OpenRecordsetCommand()

On Error Resume Next

Dim cnn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim cmdSQL As ADODB.Command

Dim strDBName As String

Dim strConnectString As String

Dim strSQL As String

Dim strCursorType As String

Dim strLockType As String

Dim strDBNameAndPath As String

Dim strCurrentPath As String

Dim fso As New Scripting.FileSystemObject

Dim fil As Scripting.File

Dim strPrompt As String

Create connection to an external database.

strCurrentPath = Application.CurrentProject.Path & strDBName = "Northwind.mdb"

strDBNameAndPath = strCurrentPath & strDBName

Attempt to find the database, and put up a message if it is not found.

Set fil = fso.GetFile(strDBNameAndPath) If fil Is Nothing Then strPrompt = "Can't find " & strDBName & " in " _ & strCurrentPath & "; please copy it from the " & "OfficeiiXSamples subfolder under the main " & "Microsoft Office folder " _ & "of an earlier version of Office" MsgBox strPrompt, vbCritical + vbOKOnly GoTo ErrorHandlerExit End If

On Error GoTo ErrorHandler

Set cnn = New ADODB.Connection Set rst = New ADODB.Recordset

Need to specify the Jet 4.0 provider for connecting to Access .mdb format databases.

With cnn

.Provider = "Microsoft.Jet.OLEDB.4.C>" .Open strDBNameAndPath strConnectString = .ConnectionString End With

Set cmdSQL = New ADODB.Command Set cmdSQL.ActiveConnection = cnn

Use a SQL string to create a command.

strSQL = "SELECT CompanyName, ContactName, " _ & "City FROM Suppliers " _ & "WHERE Country = 'Sweden' " _ & "ORDER BY CompanyName;" cmdSQL.CommandText = strSQL Set rst = cmdSQL.Execute

Check cursor and lock type of recordset.

strCursorType = Switch(rst.CursorType = _ adOpenDynamic, _

"Dynamic (" & adOpenDynamic & ")", _ rst.CursorType = adOpenForwardOnly, _ "Forward-only (" _ & adOpenForwardOnly & ")", _ rst.CursorType = adOpenKeyset, "Keyset (" _ & adOpenKeyset & ")", _

rst.CursorType = adOpenStatic, "Static (" _ & adOpenStatic & ")")

strLockType = Switch(rst.LockType = _ adLockOptimistic, _

"Optimistic (" & adLockOptimistic & ")", _ rst.LockType = adLockReadOnly, "Read-only (" _ & adLockReadOnly & ")", _ rst.LockType = adLockBatchOptimistic, _ "BatchOptimistic (" _ & adLockBatchOptimistic & ")", _ rst.LockType = adLockPessimistic, _ "Pessimistic (" _ & adLockPessimistic & ")")

Debug.Print "Recordset cursor/lock type: " _

& strCursorType & ", " & strLockType & vbCrLf

Iterate through the recordset, and print values from fields to the Immediate window.

With rst

.MoveFirst Do While Not .EOF

Debug.Print "Swedish Company name: " _ & ![CompanyName] _

& vbCrLf & vbTab & "Contact name: " _ & ![ContactName] _

& vbCrLf & vbTab & "City: " & ![City] _ & vbCrLf rst.MoveNext Loop

End With

ErrorHandlerExit:

Close the Recordset and Connection objects.

If Not rst Is Nothing Then

If rst.State = adStateOpen Then rst.Close Set rst = Nothing End If

End If

If Not cnn Is Nothing Then

If cnn.State = adStateOpen Then cnn.Close Set cnn = Nothing End If

End If

Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit End Sub

0 0

Post a comment