Update of table

[Erictsang]

Dear all

I got a question on table update

I have imported a table from an excel file - around 100 records with around 20 fields



i work on it and somehow, a colleague has updated/amended the excel file and send back to me, as they do not have ACCESS

I do not know which records have been updated and i can not overwrite it to my existing database as i have worked on it for sometime.

What can i do ?

[June7]

Do you want the colleague's edits to have precedence? Or do you need to make decision for each record which edit to accept?

Could try a FIND UNMATCHED query.

Otherwise, for only 100 records and 20 fields, use your eyeballs. Read each record of each table side-by-side.

[Micron]

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.