Queries vs VBA Code

[Simon Webb]

I am doing some financial calculations where I have one table of eg 700
accounts and another table of eg 50+ transactions per account (35,000
entries). Before I do these calculations I prepare a temporary table of
intermediary results.

My question is, to prepare this table should I:

a) Use VBA code to loop through each account and populate the intermediary
table on an account by an account basis

b) Or use a series of queries (including some union queries) which will work
on all the accounts in one hit.

My application is being developed for a target PC of Pentium 2, 128Mb Ram
Win98, slow Hard Disk (It's being shipped to a third world location).

My concerns are
1) Speed
2) The thinging not crashing out because of lack of system resources
3) Simplicity of development.

So far I have followed approach A, but it is slow and the code while not
complex is not easy for some one else to pick up.

Any thoughts on the above much appreciated

regards

Simon

 

[Douglas J. Steele]

Queries are almost always more efficient than using VBA.

A potential issue from a support perspective is that fact that you can't put
comments into queries, but if you use VBA code to run your queries, you can
put explanations in the code as to what each query does.