.runsql works / .execute fails? help appreciated

[ms]

i have a select query that returns a recordset. in my form
code (command button click), i loop through the rs and
create an update sql statement using some of its field
values. if i use .execute it tells me it
needs 'dbSeeChanges' in openrecordset. I already have the
option specified!! if i change the code to .runsql it
works fine. i'd prefer to have the extra options available
(through .execute) though. any ideas what the problem
might be? i've been experimenting all day and having
gotten anything else to work.

here's some code snippets.
Private Sub cmdBtn_Click()
Dim rsProj As Variant
Dim d As Database
Set d = CurrentDb

Set rsProj = d.OpenRecordset("qryItem_Value",,dbSeeChanges)

rsProj.MoveFirst
While Not rsProj.EOF

SQL = "UPDATE items SET p00_value=" & rsProj!p00_value

DoCmd.RunSQL SQL 'works
'd.Execute SQL 'does not work

rsProj.MoveNext
Wend

rsProj.Close
Set d = Nothing
End Sub

i tried other ways like using
Dim qdf As QueryDef
Set qdf = d.QueryDefs("qryItem_Value")
but get the same error.

many thanks in advance.
ms

 

[chris]

The message about "SeeChanges" applies to the .Execute
statement

d.Execute SQL 'does not work
d.Execute SQL, dbSeeChanges 'does work

 

[ms]

chris,
thanks! it looks like that did the trick.
is it possible to use multiple options? i was hoping to
add dbFailOnError but it doesn't seem to like it.

out of curiousity, did i misread the error message or is
ms reusing it for multiple issues. it reads to me that the
option (see changes) is supposed to be on the
openrecordset statement. doesn't mention anything about
the execute. oh well, guess it works so i shouldn't
complain. thanks again.

error 3622
"you must use the dbseechanges option with openrecordset
when accessing a SQL Server table that has an IDENTITY
column"