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



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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads