Batch update

[John Marshall, MVP]

I need to do a batch update of an Access table and there appears to be a
variety of methods of finding a record, is there an article that summarizes
the choices and describes the Pros and Cons.

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm

 

[Ted Allen]

Hi John,

I don't know of any papers, but I'll offer what I can.
Hopefully others will post their thoughts as well.

Usually when I need to do batch updates my first approach
is to use an update query. I think this is the fastest
and easiest way of doing updates if it is possible to
define what you want to do in the query.

As far as locating records, the options are somewhat
limited by the type of recordset and indexing. I think
that .Seek is probably faster than .Find, but it requires
a table type recordset and you have to specify an index
to be used.

The .Find family of search commands is much more
flexible, but probably slower.

Personally, I don't find myself using either of the above
very often though. If I need to loop through records
meeting a certain criteria, I usually just open a
recordset based on those criteria using a sql statement.
If the order of the records is important, I use an ORDER
BY clause in the sql statement so that they are properly
ordered. Then, I just loop through the records in the
recordset and make any necessary changes by starting at
the first record and using the recordset's .movenext
method to go through each one until the end of the field
is reached (.EOF).

This is all kind of general, but hopefully it will help
somewhat. Post back if you would like more detail on any
of the above.

-Ted Allen