Appending with an external Excel Sheet

[Cameroncha]

Hello. I have database that i need to append information from a spreadsheet. What is the most straightforward way of doing this

The spreadsheet has some primary keys that are not in the Access db yet, but for now i just want it to skip these and append the existing matches with the new information << preferably if the spreadsheet is blank, and the db has something, i would like existing info to stay instead of the blank.

Thank You

 

[The_Doc_Man]

The bit about retaining Access info when the Excel equivalent is blank helps define one thing you must do. I'll discuss that part first.

Since you want to do appending AND updating (implied by the "retain info when Excel has no new info"), I would first and foremost pull the Excel data into a temporary table. Then I would massage it a bit. Change field formats. That type of thing.

After that, I would write append and update queries as needed. The way to not overwrite good data with blanks is to test whether the imported data happens to be blank or null.

For that, I make my queries use

Len(Nz([MyField],""))

If the above is 0, then I don't use that record for the updates to [MyField].

Once you have gleaned everything you can from the imported table, moved it to where you REALLY wanted it, etc. - just delete the table, compress your database, whatever other cleanup is appropriate, and go on to whatever is next for you.