Appending in code

[Guest]

I am looking for some advice/opinions on the best course of action for some
code that I have been working on. I have programmed before, but I have been
attempting to teach myself Access/VBA as I go along. After reading through
some of these discussion threads, I am starting to think that there may be a
better way than the brute force approach that I have been struggling with.

I am importing data from a text file into a temporary table. This data
however, may have a field that contains duplicate data. I need to remove the
corresponding duplicate record(s) can be removed. After this
data-simplification has occured, I then need to append the newly imported
data from the temp table into the actual table that I am storing my valid
data in. Another caveat - there is a date/time field that all data is sorted
by; the imported data will always overlap and supersede this date/time value
of the old data by some amount. The "old" records of the existing table must
be deleted and replaced by the newer, more current imported data.

Any suggestions on how to efficently do this? Right now I have a very ugly
nested-loop churning through the records so that I can compare
Current-to-Previous-to-Next records. It seems long-winded and system
consuming. With all the bits and pieces that I am picking up through this
discussion board and the help file, my gut is starting to tell me that this
is getting to be a lot uglier than it needs to be.

Thanks in advance, and please respond to this discussion thread - network
security prevents access to this email at work.

 

[George Nicholson]

Consider the following:

1) Create & save ImportQuery1 to identify "Old" records with DateTimeStamps
=> Min(DateTimeStamp) of TempTable
2) Create & save ImportQuery2 to delete the records identified in Query1

3) Create & save ImportQuery3 to identify duplicate records in whichever
table you want to delete them from (a little unclear on this)
4) Create & save ImportQuery4 to delete the records identified in query 3
5) Create & save ImportQuery5 to append TempTable to "old" data

6) Write code to run saved ImportQueries 2, 4 and 5
This assumes the name of TempTable is static and never changes. Once set up,
Step 6 is the only thing that needs "running".

7) Optional: Query/code to Delete all records in TempTable in preparation
for next import (Don't delete the table as a whole. The table structure
should always be left intact to make sure the stored queries contain "valid"
table and field references).

Advantage: breaking the process down into steps that can be done by a stored
query can make maintenance easier if/when things go wrong or if/when changes
are made to the data structure. Fixing/changing a query can be a lot easier
than doing the same thing within code, and might be able to be done by
someone who isn't at "developer" level, if necessary.

HTH,