Update failed

[mistux]

I am using Access 20002 .ADP and MS SQL2000 and when I try to run a
update query I get the following error:
Current Recordset does not support updating. This may be a limitatio
of the provider, or of the selected loctype. Number: 3251
I am using:

Set rstSched
CurrentProject.Connection.Execute("T_Schedule_ProposedSchedule")

and I get the error on the statment:

rstSched!StartTime = lTimeStart

Is there something I am not specifying in the set statement: Se
rstSched
CurrentProject.Connection.Execute("T_Schedule_ProposedSchedule")
that I need to be. I don't see any options for specifying the loc
type, etc ?

 

[RoyVidar]

mistux submitted this idea :

I am using Access 20002 .ADP and MS SQL2000 and when I try to run an
update query I get the following error:
Current Recordset does not support updating. This may be a limitation
of the provider, or of the selected loctype. Number: 3251
I am using:

Set rstSched =
CurrentProject.Connection.Execute("T_Schedule_ProposedSchedule")

and I get the error on the statment:

rstSched!StartTime = lTimeStart

Is there something I am not specifying in the set statement: Set
rstSched =
CurrentProject.Connection.Execute("T_Schedule_ProposedSchedule")
that I need to be. I don't see any options for specifying the lock
type, etc ??

Hi!

I think assigning a recordset through the .execute method of the
connection
gives you a forwardonly readonly recordset. Have you tried

set rstSched = new adodb.recordset
with rstSched
.activeconnection=currentproject.connection
.locktype = adlockoptimistic
.cursortype=adopenkeyset
.open "T_Schedule_ProposedSchedule",,,,adcmdtable ' or relevant
' commandtypeenum
end with

Or - if you're updating or inserting, have you considered executing a
query
in stead of the recordset approach?

 

[Robert Morley]

Just a note on the code provided by Roy:

.activeconnection=currentproject.connection

would be much better as:

Set .ActiveConnection = CurrentProject.Connection

Otherwise what happens is that the RecordSet object only gets the default
property for the Connection object (ConnectionString), and opens an entirely
new connection based on that connection string. By using "Set", the
RecordSet object gets a reference to the Connection object, and uses the
existing connection instead.

It most cases, it makes little difference which way you use in terms of end
result, but you'll use much fewer resources and get *much* faster access to
your table by adding the "Set" command at the beginning.



Rob