Append vs Recordset

[Jason Lepack]

I have a loop that loops through all records in all tables that have
"TSE" as the first letters. In that loop, based upon conditions of the
current record I have to add records to 1 of 5 different table. Now is
it quicker to use 5 recordsets and add a new record to them, or is it
faster to use append statements to append the records.

ex.

for each t in db.tabledefs
if t.name like "tse*" then
set rs = db.openrecordset(t.name)
do while not rs.eof
if condition1 then
rsA.addnew
rsA.update
' strsql = append statment for A here
' docmd.runsql strsql
elseif condition2 then
rsB.addnew
rsB.update
' strsql = append statment here
' docmd.runsql strsql
end if
loop
end if
next t

which is better?

[Lyle Fairfield]

Jason Lepack wrote:

I have a loop that loops through all records in all tables that have
"TSE" as the first letters. In that loop, based upon conditions of the
current record I have to add records to 1 of 5 different table. Now is
it quicker to use 5 recordsets and add a new record to them, or is it
faster to use append statements to append the records.

ex.

for each t in db.tabledefs
if t.name like "tse*" then
set rs = db.openrecordset(t.name)
do while not rs.eof
if condition1 then
rsA.addnew
rsA.update
' strsql = append statment for A here
' docmd.runsql strsql
elseif condition2 then
rsB.addnew
rsB.update
' strsql = append statment here
' docmd.runsql strsql
end if
loop
end if
next t

which is better?

SQL is better 99.44% of the time. It may not be if recursive or
extensive record manipulation is being done, as in a time table
calculation where we assign periods, check the suitability of the
assignment, then make formative changes in several passes.

CurrentDb.Execute "UPDATE ATable Set AField =" & aValue, dbFailOnError
or
CurrentDb.Execute "UPDATE ATable Set AField ='" & aString & "'",
dbFailOnError

from help:
"The Execute method runs an action query. The dbFailOnError option can
allow your application to determine whether a QueryDef object fails to
perform its designated action for all records that meet its criteria.
As long as a QueryDef object is syntactically correct, it does not
generate an error-even if it fails to perform its action. The
dbFailOnError option rolls back any changes if the QueryDef object
cannot perform all the changes. This option also generates a run-time
error to help you perform any associated processing, such as providing
feedback to a user."

At one time I thought I should try to keep my action queries to a
minimum. I have no idea why. Perhaps because I saw so much Recordset
updating here and in MS examples. I was wrong.
Execute can be called millions of times; it stays fast, simple and
safe.

Recordsets almost always send more data over wires or loads more data
into memory than is required. Recordset updating often is problematic.
Recordset updating is slow. And pointers to Recordsets may have to be
explicitly released to prevent ghosting (nah, not anymore but it's an
old shibboleth that we're fond of.)