ADO command will not return recordset

[tron08]

Hi,
I have this bit of code that I am testing using ado command and parameters. The code goes against a Access 97 database query named "ClaimsShipmentInfoByShipment". The query requires one parameter, "Shipment". The query works in Access, but does not return a recordset using the snipit below.

Any help will be appreciated.


Set cmd = New ADODB.Command
Set Parm = New ADODB.Parameter
Set rs = New ADODB.Recordset

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:/db/shipping.mdb"

cmd.ActiveConnection = Conn
cmd.CommandType = 4

dummystring = "37940 SJZ"
RecordSelects = "ClaimsShipmentInfoByShipment"
Set Parm = cmd.CreateParameter("Shipment", 8, 1, Len(dummystring), dummystring)
cmd.Parameters.Append Parm
cmd.CommandText = RecordSelects
cmd.Execute
rs.Open cmd
Do Until rs.EOF
MsgBox (rs.Fields("name&quot)
rs.MoveNext
Loop

 

[jgpasko]

What error is raised when you execute?

Just off the top...is your Connection Object global? I see

Conn.Open "Provider..."

But I don't see

Dim Conn As ADODB.Connection, etc

John John Pasko
john@rts-sd.com
"No matter where you go, there you are."

 

[mcentore]

You need to set the recordset equal to the command.execute method i.e. Set rs=cmd.execute

 

[tron08]

Yes,
It got lost in the cut and paste

Set Conn = New ADODB.Connection

thanks

 

[tron08]

I did try the syntax (Set rs = cmd.Execute)as well. Return with the same results. From what I know they are functionally equivalent.

thanks

 

[jgpasko]

Is an error raised? If so what is it?

Thanks John Pasko
john@rts-sd.com
"No matter where you go, there you are."

 

[zemp]

This is the basic syntax that I use,

With cmd
.ActiveConnection = Conn
.CommandText = "ClaimsShipmentInfoByShipment"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("Shipment", adVarChar, adParamInput, 20)
.Parameters("Shipment&quot.Value = dummystring
End With
rs.Open cmd, , adOpenStatic, adLockOptimistic

The number 20 is the maximum field length as defined in Access.

Hope it helps. Thanks and Good Luck!

zemp

 

[tron08]

I have checked the errors on the Connection object and I didn't see any errors that were generated.

 

[tron08]

Zemp,

I tried the your code with the same results. I keep thinking the problem is in the code, but perhaps not.

Any other thoughts?

 

[jgpasko]

Could be your driver. MS Access 97 is MS Jet 3.x, you're using JET 4.x.

Good Luck,

John Pasko
john@rts-sd.com
"No matter where you go, there you are."