Recordset Update or Run Query...which is better?

[BigJimSlade]

Hi, Big Jim here:

I was wondering which was the better way to add or delete a record from a table in this situation:

I have recordset A and recordset B. Both recordsets pull records from Table1.

I make a change to data that was in recordset A, and that change should be reflected in recordet B. Should I....

A) Update recordset A to change the data...ex:
rstA.Edit
rstA!Field = "NewValue"
rstA.Update

Or

B) Run a Query...ex:
Docmd.RunSQL("UPDATE Table1 SET [Field] = "NewValue"
WHERE ect ect;")

Which one of these is a better way to do things? I know that I often have recordsets that do not show updated data immediately, and I was wondering if one of the above options is better than the other.

Thanks in advance...

Big Jim

 

[pbaldy]

Generally speaking, executing SQL is faster than using a recordset (and Currentdb.execute is faster than docmd.runSQL).

You may need to requery rstB if changes have been made to the underlying data while it's open.

 

[BigJimSlade]

pbaldy! Thanks for the response, I will have to try that.

Pat:

I think the added rows not being recognized by the refresh was what was happening to me. Sometimes I run requery and refresh though (both of them), and it doesn't seem to catch all the changes I made. My solution as of now is to create a loop through the recordset missing the change in the record, looking for that change in the record, and when I find it, continue on with my code. It eventually finds it after a second or two (or three)

Thanks again!

Big Jim