VB to Compare two tables, update some records, add/delete others

[Aubs010]

I am fairly certain I have seen something similar to this before, but can't for the life of me find it. Maybe I'm asking the wrong question in the search...

a brief "what I need" out of the question is at the bottom, if you don't want to know the background.


I have a table tblTU with a number of fields. Each record has a unique reference code within the field TUid.

I then have a text file that contains data to update tblTU. This is imported into two tables:

Table 1 tbl_TmpUploads where updates are in the required format (i.e. no blank/missing data)

Table 2 tbl_TmpIncompleteUploads where updates are not recognised. There are only a few in each upload, so I will ignore these for now.


tbl_TmpUploads has an additional field action with either ADD, REMOVE, MODIFY in the text. This identifies how the data should be interperated in tblTU:
If it is ADD, then add the record.
If it is MODIFY, then an existing entry is to be modified (if it exists, as I do not have the original base data)
If it is REMOVE, then remove the corresponding record from tblTU.

BASICALLY:

Fastest route to read each record in tbl_TmpUploads, if the reference in TUid matches to a reference in tblTU then carry out the action in action (ADD, REMOVE, MODIFY). If the reference in TUid does not exist in tblTU, then add the record.

I'm sure this can be done with two nested while loops, but I could imagine this would take forever to go through either of the tables multiple times for each update.

Would it be quicker adding each table to an array (is there any limitation? - multiple arrays - found 'Multidimensional Arrays' should work)

Many thanks in advance for any help. All comments greatly appreciated.

Aubs

 

[PHV]

For me no VBA is required but simply 3 action queries ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aubs010]

Will look into that, thanks PH

Aubs

Aubs

 

[randy700]

I've dealt with this exact situation in the past, to update personnel tables. As PHV suggested, I also solved the problem with three queries.

Query 1 - INSERT query. Inserts all records with ADD in the action field.

Query 2 - UPDATE query. Updates the records with MODIFY in the action field.

Query 3 - This one can be accomplished in either of two ways. My method was to run an UPDATE query that simply modified a field in the personnel table (I made the employee INACTIVE). This provided me with a means to reinstate at a later date if necessary.

The other option, of course, is a DELETE query which deletes the records with DELETE in the action field.

Process in order -- add, update, delete.


Randy

 

[Aubs010]

Brilliant Randy, you've answered it exactly as I was hoping. Thought it must have been an easier way!

Incidently, I have now split the sections up into different tables (ADD, REMOVE, MODIFY) so the records can be checked if need be then a single button press to call each query in turn.

Thanks for your help, much appreciated.


Aubs