Replace data problem with importing to Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to know how to import data to an existing table from a DBF and/or
Excel spreadsheet to replace the existing data in my Access table. I have
attempted importing an it appends with the Excel version, with the DBF it
creates a new table.
The table I am working with are employee records. Sometimes the change is
only in one field, such as the termination date field. I want to import by
replacing the blank field on that employee to show the termination date. If I
have not given enough information let me know what else to offer.
 
Hi Lisa,

Think of it not as importing new data but updating the existing table.

Start by creating a linked table (File|Get External data|Link) connected
to the Excel or DBF file. Then:

1) if you want the data in the external file to *replace* all the data
in the table, first use a delete query to empty the table and then an
append query to move the data from the linked table into the "real"
table.

(Or - if the external file is authoritative - you could simply delete
your existing table and use the linked table in its place.)

2) if you want to update the records already in the table to reflect
changes made in the external file, use an update query that joins the
table and the linked table on their (shared) primary key and updates
fields in the "real" table from their counterparts in the linked table.

3) if in addition to (2) you want to add new records from the linked
table, use an append query and rely on the primary key index(es) to
prevent duplicate records being appended.
 
Back
Top