Re: reusing command object

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

This is usually the fact of a missing primary key. To be sure, you must
take a look with the SQL-Server Profiler to see what's happens on the
SQL-Server side

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Andy G" <ajgould@xxxxxxxxxxx> wrote in message
news:eO8YEgbLGHA.2912@xxxxxxxxxxxxxxxxxxxxxxx
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 news:eJ25egZLGHA.2216@xxxxxxxxxxxxxxxxxxxxxxx
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


"Andy G" <ajgould@xxxxxxxxxxx> wrote in message
news:%23E8KDJYLGHA.3100@xxxxxxxxxxxxxxxxxxxxxxx
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 news:%23hxOZ8RLGHA.536@xxxxxxxxxxxxxxxxxxxxxxx
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


"Andy G" <ajgould@xxxxxxxxxxx> wrote in message
news:udW36RQLGHA.3732@xxxxxxxxxxxxxxxxxxxxxxx
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












.

Relevant Pages

  • Re: reusing command object
    ... the recordset and make updates to certain records. ... Dim cmd As New ADODB.Command ... Dim rst As ADODB.Recordset ... command object as its connection parameter. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: reusing command object
    ... opening the recordset using the command object I want to move through the ... recordset and make updates to certain records. ... Dim cmd As New ADODB.Command ... "Dim rst As Recordset", its better to write "Dim rst As ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Opening next form in series based on lookup table
    ... Dim rst As DAO.Recordset ... So Access knows you want a DAO and not an ADO recordset. ... Dan Artuso, Access MVP ...
    (microsoft.public.access.forms)
  • Re: Number of records from Query
    ... Dim rst As Recordset ... Dim rst As DAO.Recordset ... For example, to ensure that you get a DAO recordset, ... Access will take the first reference it finds with a Recordset ...
    (microsoft.public.access.formscoding)
  • Re: reusing command object
    ... recordset and make updates to certain records. ... Dim cmd As New ADODB.Command ... create a recordset and open it using the command object ... instead of simply writing "Dim rst As ...
    (microsoft.public.access.adp.sqlserver)