Probably will happen again, so if you don't care which records are different, I'd do this if one of your table fields can be indexed (no dupes) and is pretty much guaranteed to have not been changed:
- identify those fields, create the index(es) in the target table
- run a query to append the new workbook records to the table. If a value already exists in the indexed field, Access will balk at appending the record (more on that in a moment).
- run an update query on the target table, setting each field to be the value of the corresponding workbook column. This will update both the original records and the ones that were newly appended, which is no problem and not worth trying to work around.
If you're just running those queries manually, Access will balk at adding records where the value already exists in the indexed field, and should ask you if you want further warnings. You click no, and at the end of the process it will tell you how many records could not be appended for each reason. No problem. Or you could turn off warnings in a macro that executes these queries, and turn warnings back on, but you wont' know how many records already existed. If you don't care, then OK. Some don't like the idea of turning warnings off, and I agree with that. However, because of coding experience I would create a procedure to execute the queries and do so using the CurrentDb.Execute method, but not only is that probably something you'd need help with, it's probably either not warranted if this is a rare problem, or there's no way you can create a unique index on your table field.
Again, the idea won't tell you which fields had different data in them (in the new workbook) but I'm guessing that's not important.
Last edited by Micron; 08-20-2017 at 12:41 PM.
Reason: clarification
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.