Importing from a spreadsheet, update or append query?

[Tony Williams]

I get a spreadsheet that contains data I need to add to an existing table.
However some of the spreadsheet data is to update cureent records and some is
to be added as new records. I imagined I could do this with two queries an
append query and an update query but I'm not sure how I would get the queries
to work from the import. How do I get the querries to decide if records
already exist? The data is a list of vehicles and the VRM is the unique
identifier, HOWEVER some records don't yet have a VRM so I have a problem of
identifying those records to update where the VRM is not yet present.

Anyone help with suggestions? I'm a VBA novice so if that's the root please
treat me gently!

Thanks
Tony

 

[Douglas J. Steele]

I showed how to run a single query that does what you want in my November,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access". You
can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

Link to your spreadsheet, and create the query to update the table in your
database using the linked table.

 

[Tony Williams]

Thanks Douglas that does look as though It's what I want. The only problem I
think I will be struggling with is the fact that I may have to find some way
to match the records where the VRM isn't present.
Thanks again and a Happy New Year
Tony

 

[Douglas J. Steele]

One way to match missing values is to pick a value that should never occur
naturally, and use the Nz function to convert Nulls to that value.

Where you've currently got

Table1.VRM = Table2.VRM

in your SQL, change it to something like

Nz(Table1.VRM, "XXX") = Nz(Table2.VRM, "XXX")

 

[Tony Williams]

Thanks Douglas, that'll be a great help.
Regards
Tony

 

[Craig]

Hi Doug,

I was having the same problem and your solution will work for me as well ---
thank you!

Two additional questions:

1. Will the update/add query REMOVE records from ExistingData if they do not
occur in the NewData?

2. Do you need exclusive access to the database in order to run the
update/add query?

Thanks,
Craig