Import Theory

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I have data from another system that I want to import to
Access. These records will import everyday. What I want to
know is if Access can, using this text import, be
configured to import the records, compare the records to
the records in the database from the day before and not
overwrite the data, but add any new records not in my
database and delete any records that are in my database
that are not on the text import? If so I need some
direction.

I know how to import the file but am confused how to
compare tables, add and delete. Maybe a series of queries
i.e. update and delete queries? In a few cases, I will
need to keep occasional records that may not be on the
text file and would normally be deleted. I thought about a
field I could make as a check box to tag these files as
keepers?

Thank you for any input,

Dennis
 
Sounds like you are importing data from a pda!

All what you ask is easy to do as long as you have a unique way of identify
each record. So, does each text record have a id, or some unique value that
can be used for a key?

if the answer is yes to the above..then what you ask is very easy.

on the other hand, if you don't have some means of unique identify each
record, then how do you plan to test, or compare if the record already
exists?
 
Dear Dennis:

First, your question about having Access "add any new records not in
my database and delete any records that are in my database that are
not on the text import."

Do your records have a permanent identity in them? Is there some
column or set of columns that uniquely identify every record and NEVER
change? If so, then this is easily done. If not, this is utterly
impossible.

Imagine someone alters a record, changing every piece of information
in that record. Is it still the same record? Or is it now a
different record? That can become a very philosophical, and ambiguous
question.

One solution might be to throw away all the rows in your table(s) and
re-import them daily. That certainly removes any ambiguity.

Otherwise, it is YOUR task to define exactly what your requirements
are in an unambiguous manner. Then you can implement the rules of
your definition.

You also mention updating records. Again, you would need some
unchanging key that matches the old records to the new ones to be able
to do this.

Perhaps this will be a starting point for your thinking.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I definitely have a primary key. So to clarify I import
the file into a table then what? Make two compare queries
to compare to my existing data table? Records that are not
in my database and another for records that are not on the
import file? Once I do this how do I merge the compare
query with? Then make an update query to update my
database? Then make a delete query of the records that are
not on the text import.

Am I on the right track?

Thanks, Dennis
 
Back
Top