Import Overlay

[Erik70NY]

Is it possible to set up a text import into Access whereby any rows with a particular primary key within a table are updated with the new values, and if the row doesn't exist within the table, append the new one. Here is an example:

Access Table Sample Before Import

PrimKey Name Phone #
123A John 212-555-1212
121A Sam 516-555-1212

Text File XYZ to Import

PrimKey Name Phone #
123A John 718-555-1212
124A Mark 914-555-1212


Desired Results after Import in Access Table Sample

PrimKey Name Phone #
123A John 718-555-1212
124A Mark 914-555-1212
121A Sam 516-555-1212

Any help provided is much appreciated!

 

[WildHare]

The only way to actually do this is to import the external file into a 'working' table, then use two queries to 1) update and 2) append the matching / non-matching records in the working table.

For the Update query, join the working table to the production table based on your key values, and update the relevant fields.

For the Append query, just use the working table, and append to the production table. Ignore the duplicates that don't go in.

Does the text file have just "new" guys and "updates" ?

What I mean is that if the text file is the whole shebang each time, it might also be just as easy to delete the production table and reload it entirely each time with just the append query.

If this is your case, remember to do a compact every now and then to recover the deleted record space.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm