How to populate one table with data from a second?

[mikkl]

I am relatively new to VBA programming and would appreciate your help with
what <should be> easy--but with which I am struggling.

I have inherited a database project with two temporary tables. Both of
these are created when a form is run and the final output is saved in Excel
for further manipulation. These tables are then deleted when the
application exits.

One table is dynamically created through a VBA sql call and contains in
excess of 500,000 records. Each of these records has a index value ("IIN")
that identifies the subject of the record. The second table contains the
up to 20 records and contains information for each IIN. There are no
duplicates in the second table.

What I need to do is to assign the records in the second table to each of
the entries in the first table so that I can perform some calculations on
each of the records in the large table.

The code that I have inherited performs a .seek and calculation in a While
loop, but this is incredibly slow and inefficient. (~15 minutes to perform
the calculations on 500,000 records on a 3.5GHz machine)

My thought was that it would be more efficient to populate each record in
the first table with the values from the second table. I can then perform
a very efficient calculation loop.

The problem is, I am not clear on how to best take the values from Table 2
and insert them into Table 1. Assume that an empty column exists in Table
1 for taking the new values.

Any and all help is appreciated,

mikkl

 

[mikkl]

My thought was that it would be more efficient to populate each record
in the first table with the values from the second table. I can then
perform a very efficient calculation loop.

It would probably be even easier/more appropriate to simply
programmatically add a link between the two tables. Can this be done in
VBA?

Some more details--
The form prompts the user for an index related to a master database. An
SQL query is created which queries on the index and populates a temporary
table. A second temporary table is then created listing each unique IIN in
the first temporary table. The user is then prompted to input a column of
data associated with each IIN. Thus, there is a time delay between the
creation of the two tables.


Would an Append Query be the right thing to try? If so, how would I do
that?

TIA,

mikkl