Pass parameter to stored procedure and return EDITABLE recordset

[jacq]

Hello
I have two methods for a recordset based on a stored procedure.

Without parameter (creates an editable recordset):
Set rstSP = New ADODB.Recordset
With rstSP
.Source = "usp_getOpenCalls"
.ActiveConnection = cnnServer1
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

With parameter (creates a read-only recordset):
Set prmCallID = cmd1.CreateParameter("callID", adInteger, adParaminput)
prmCallID.Value = intCallID
cmd1.Parameters.Append prmCallID
Set rstSP = cmd1.Execute

What I need is a way to pass a parameter and return an editable recordset - is this possible?

 

[RoyVidar]

Yup - try something like this

[tt]Set prmCallID = cmd1.CreateParameter("callID", adInteger, adParaminput)
prmCallID.Value = intCallID
cmd1.Parameters.Append prmCallID

Set rstSP = New ADODB.Recordset
With rstSP
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open cmd1
End With[/tt]

Roy-Vidar

 

[jacq]

Thanks
I tried that and am still getting This recorset is not updateable. This is my full code, is there something else that I am missing?

Set cnnServer1 = New ADODB.Connection
Dim prmCallID As ADODB.Parameter
With cnnServer1
    .Provider = "MSDataShape"
    .Properties("Data Provider").Value = "SQLOLEDB"
    .Properties("Data Source").Value = "server1"
    .Properties("User ID").Value = "callUser"
    .Properties("Password").Value = "pw"
    .Properties("Initial Catalog").Value = "db1"
    .Open
End With

Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = cnnServer1
cmd1.CommandText = "usp_getOpenCalls"
cmd1.CommandType = adCmdStoredProc

Set prmCallID = cmd1.CreateParameter("callID", adInteger, adParaminput)
prmCallID.Value = intCallID
cmd1.Parameters.Append prmCallID

Set rstSP = New ADODB.Recordset
With rstSP
    .Source = "usp_getOpenCalls"
    .ActiveConnection = cnnServer1
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open cmd1
End With
Set Me.Recordset = rstSP
Me.UniqueTable = tblOpenCall

 

[RoyVidar]

I haven't use such for form recordsets, but for ordinary recordsets, I'd remove the source and connection from the recordset object (as I did in my suggestion) - that is specified for the command object. Then test if the recordset was updateable. If so, then try to assign it to the form, and see what happens (remember, there's som funny stuff with the 2000 version, se below links)

Since you're assigning to form recordset, you might want to read this ACC2000: Forms Based on ADO Recordsets Are Read-Only, and this, for later versions How to bind Microsoft Access forms to ADO recordsets.

Roy-Vidar

 

[jacq]

Thanks, but it still doesn't work. I have used those two articles before and they helped me a lot. I have decided that it can't be done and will just use dsn links instead.
Thanks for your help anyway...

 

[jacq]

In case anyone is interested, i changed my stored procedure to include only one table (it previoulsy had 3 tables joined) and it allowed me to pass a parameter and return an updateable recordset using the code as RoyVidar suggested.
Id does say in the MSDN site that you can have joined tables as long as you set the me.uniquetable to the one you want to edit, but it didn't work for me.
Just need to find a way of getting the details required from the other two tables to display on my form...

 

[PHV]

Have you already read this ?

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

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

 

[cmmrfrds]

I can't remember for sure, but it seems to me there were limits on updateability with the
.Provider = "MSDataShape"

Is there some reason you need to use this provider or can you change to the Microsoft sql server provider.
.Provider = "SQLOLEDB.1"

 

[jacq]

According to Microsoft Help and Support:
Microsoft Access forms will only allow you to edit data from an ADO recordset if the ADO recordset is created by using a combination of the MSDataShape and SQL Server OLEDB providers.

I have also tried changing the select from a WHERE to INNER JOIN but it has made no difference.

 

[cmmrfrds]

I guess my memory wasn't so good. I had it backwards when trying to bind ADO recordsets to Forms.

Are Pass-through queries an option?

 

[jacq]

Haven't ever tried a pass-through query.

I have now created three stored procedures just to get the thing up and running (have one years worth of work to do in 5 weeks!!) so i'll probably try it when i have some more time.

Thanks...