How to print an item from a recordset using ADO abd SQL Server

[maupiti]

Access 2002. SQL 2000 Server

The procedure below does run and gives no output message, but the statement below did not execute.

MsgBox rst!ord_date

///////////////////////////////////////////

Private Sub ADOAsyncQuery_2()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim err As ADODB.Error
Dim strConnect As String

On Error GoTo Err_ADOAsyncQuery

Set cnn = New ADODB.Connection
strConnect = "Provider=sqloledb; Data Source= _
Win-2000-Server;" & "Initial Catalog=pubs; _
Integrated Security=SSPI"
cnn.ConnectionString = strConnect
cnn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "SELECT * FROM Sales WHERE qty = 5"
cmd.Execute , , adAsyncExecute

''''''''''''''''''' Recordset ''''''''''''''''''

Set cnn = CurrentProject.Connection
rst.Open "SELECT * FROM Sales WHERE qty = 5", _
cnn, adOpenDynamic, adLockOptimistic, adCmdTable
rst.MoveFirst
MsgBox rst!ord_date

'Perform other tasks

If cmd.State = adStateExecuting Then
cmd.Cancel
End If

Exit_ADOAsyncQuery:
On Error Resume Next
cnn.Close
Exit Sub

Err_ADOAsyncQuery:
For Each err In cnn.Errors
Debug.Print err.Number, err.Description
Next err
Resume Exit_ADOAsyncQuery
End Sub

 

[maupiti]

Sorry for the typo. The question heading should have said
"How to print an item from a recordset using ADO and SQL Server

 

[cmmrfrds]

This should not have given results because of the 5th parameter. It is not a table but an sql statement which is the default.

rst.Open "SELECT * FROM Sales WHERE qty = 5", _
cnn, adOpenDynamic, adLockOptimistic, adCmdTable
rst.MoveFirst
MsgBox rst!ord_date

Delete the 5th parm.
cnn, adOpenDynamic, adLockOptimistic

Should check that a recordset is returned before starting to display.

If Not (rst.BOF and rst.EOF) then
Msgbox rst!ord_date
else
Msgbox "No recordset"
End if

Also, you might as well make this adOpenDynamic as adOpenStatic since Access will make it static anyway.

 

[PHV]

Err is a standard VBA object so avoid to names a variable err ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886