[AccessD] Speed up DAO processing

Hi Lonnie

> I have four tables.
 
> Table1 is where all the records end up. It starts out empty.
 
> 1. I take all transactions from Table2 and append them into Table1.
 
> 2. I then check each record in Table3 and see if there is a
> corresponding record in Table1 based on two fields. If so, then I
> edit the existing record in Table1. If not, I add it.  
 
> 3. I the do the same as in step 2 for Table4.
 
> I am using DAO recordset processing and use the RS.FindFirst method
> to see if records from Table3 or 4 are in Table1. 
 
> It appears that this FindFirst method is greatly slowing my process.
> Is there a faster way. I pasted my code below if anyone wants to see
> it.

You should be able to run this with one query for each step.

1. This needs a simple appending query.
2. and 3. These need a combined updating/appending query.

Thus, this tip from Smart Access must be for you:

<quote>

Update and Append Records with One Query

By Alan Biggs

Did you know that you can use an update query in Access to both update
and add records at the same time? This is useful if you have two
versions of a table, tblOld and tblNew, and you want to integrate the
changes from tblNew into tblOld. 

Follow these steps:

1. Create an update query and add the two tables. Join the two tables
by dragging the key field of tblNew onto the matching field of tblOld.

2. Double-click on the relationship and choose the join option that
includes all records from tblNew and only those that match from
tblOld. 

3. Select all the fields from tblOld and drag them onto the QBE grid.

4. For each field, in the Update To cell type in tblNew.FieldName,
where FieldName matches the field name of tblOld.

5. Select Query Properties from the View menu and change Unique
Records to False. (This switches off the DISTINCTROW option in the SQL
view. If you leave this on you'll get only one blank record in your
results, but you want one blank record for each new record to be added
to tblOld.) 

6. Run the query and you'll see the changes to tblNew are now in
tblOld. 

This will only add records to tblOld that have been added to tblNew.
Records in tblOld that aren't present in tblNew will still remain in
tblOld. 

</quote>

I guess this will run at "blazing" speed compared with the DAO code
solution.

/gustav