Put Results of Stored Procedure in Recordset

[BillyRogers]

I have two questions

1. I'm trying to call a stored procedure from access and return the results
in a recordset. How do i do this?

2. Also, In SQL server I use a single quote to begin a comma separated list
of account numbers and use a single quote to end the list that is passed as a
variable. Do I just replace the Single quote with double quotes? otherwise
access treats is like a comment.

here's what i have so far

Set cnn = New ADODB.Connection

Set rs = New ADODB.Recordset


cnn.Open "Provider=SQLOLEDB;data source=SQL10;" & _
"database=Ext;Trusted_Connection=yes;"


'**** how do I get this stored procedure into a recordset?????
' *** and do I use double quotes? to replace the single quotes in sql server?

' **note: i'm trying to pass a single comma separated string as a parameter

cnn.GetAssocEquipment "1619986,1619995"




--
Billy Rogers

Dallas,TX

Currently Using Office 2000 and Office 2003

 

[RoyVidar]

I have two questions

1. I'm trying to call a stored procedure from access and return the
results in a recordset. How do i do this?

2. Also, In SQL server I use a single quote to begin a comma
separated list of account numbers and use a single quote to end the
list that is passed as a variable. Do I just replace the Single
quote with double quotes? otherwise access treats is like a
comment.

here's what i have so far

Set cnn = New ADODB.Connection

Set rs = New ADODB.Recordset


cnn.Open "Provider=SQLOLEDB;data source=SQL10;" & _
"database=Ext;Trusted_Connection=yes;"


'**** how do I get this stored procedure into a recordset?????
' *** and do I use double quotes? to replace the single quotes in sql
server?

' **note: i'm trying to pass a single comma separated string as a
parameter

cnn.GetAssocEquipment "1619986,1619995"

For question 1, and perhaps question 2, I'd suggest something along the
lines of the following air code. Replace the connection, and hopefully
with a bit of tweaking, it might work.

Dim cn As ADODB.Connection
Dim rs AS ADODB.Recordset
Dim cmd As ADODB.Command
Dim Prm1 As ADODB.Parameter

Set cn = CurrentProject.Connection

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cn
.CommandText = "GetAssocEquipment"
.CommandType = adCmdStoredProc

Set Prm1 = .CreateParameter("NameOfParameter", adVarChar, _
adParamInput, 8000)
.Parameters.Append Prm1
Prm1.Value = "1619986,1619995"

' for forwardonly/readonly recordset
'Set rs = .Execute

End With

' for other cursor/locktype
Set rs = New ADODB.Recordset
With rs
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open cmd
End With

The way you fire off the SP, probably works if you don't wish to
return a recordset.