Thank you. I believe I am a little closer to making this work. After
opening the recordset using the command object I want to move through
the recordset and make updates to certain records. After the rst.Update
(below) I get the error, "Row cannot be located for updatin. Some
values may have been changed since it was last read." If this is
something you haven't seen before don't spend too much time on it, but
if you can point me in the right direction then that would be greatly
appreciated.
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim rst As ADODB.Recordset
With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"
.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open cmd, , adOpenStatic, adLockOptimistic
Do Until rst.EOF
rst![ColorBlue] = clrBlue
rst![ColorGrey] = clrGrey
rst![ColorBlack] = clrBlack
rst![ColorRed] = clrRed
rst![ColorNone] = clrNone
rst![StartTime] = apptStartTime
rst![Locked] = apptLocked
rst![ID] = apptID
rst.Update
rst.MoveNext
Loop
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message Oh, I see now where your misunderstanding is. All you have to do is
drop the .Execute call, create a recordset and open it using the
command object as its connection parameter. Also, instead of simply
writing "Dim rst As Recordset", its better to write "Dim rst As
ADODB.Recordset".
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
With cmd
.CommandType = adCmdStoredProc
.CommandText = "sApptDateStaffFinalCC"
.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
End With
Dim rst As ADODB.Recordset
set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenStatic, adLockOptimistic
Finally, I'm not sure what you want to do exactly with your last
statement about ApptPopulate.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail:
http://cerbermail.com/?QugbLEWINF
I'm trying to do exactly what the code says I'm trying to do, I don't
understand your confusion. I need to use the command object to
populate a recordset and then update certain values in that recordset
by looping through it.
That's why I use the lines;
rst.LockType = adLockOptimistic
rst.Open cmd
If I don't the recordset will be opened forward only (read only) and I
will not be able to update the recordset. If I should approach this a
different way then that's fine, but I know the way that I intially
proposed will work fine. In ADO you can reuse a single command
object.
-Andy
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in message
Not sure what you want to do here: first you open a recordset using
the .Execute function of the command object and then you reopen this
same recordset using its .Open function.
You should make a choice between these two opens.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail:
http://cerbermail.com/?QugbLEWINF
I want to run the below code and then use the recordset after the
update to loop through and do necessary updates to the data that came
through in the stored procedure. I'm getting some locking errors.
Any ideas would be great.
Thanks.
Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
Dim rst As Recordset
With cmd
.CommandText = "sApptDateStaffFinalCC"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@staffNumber",
adBigInt, adParamInput, 15, curUser)
.Parameters.Append .CreateParameter("@Date", adDate,
adParamInput, , curDate)
Set rst = .Execute
End With
rst.LockType = adLockOptimistic
rst.Open cmd
Set ApptPopulate = rst
