Workaround for syncing all records in access w/update

[specterunseen]

I've googled here for over an hour trying to find an answer to this question, so if this has been answered, please let me know. I just can't find out how to best proceed. Need some help after trying to find out the answer myself.



I'm using a tool I created as a workaround to using the daily excel list handed out. This means my daily data import normally deletes and creates a new table. I have to update the primary key and format 2-3 things correctly in table design view.

Now that I'm wanting to have a relationship defined to keep two tables in sync with referential integrity, I'm running into the problem that I can't delete records that have a relationship. So I'm trying to use an update query instead of a make table query as before.


Question

Can I use the update query to update ALL columns, all rows, in the entire table from my linked excel sheet, without MANUALLY SPECIFYING EVERY TARGET RECORD?? ( I've got it working with 2 columns, but this will be a pain to type out every single record to update. I'd like to update the entire table with a click)

Can I use a delete query to then properly clean up any removed records after the update is completed?

[specterunseen]

btw, i look forward to participating in the community, thanks for any help!

[specterunseen]

best result I've found so far was this: https://www.accessforums.net/access/...html#post12725

seem to be the basic problem, updating from an external excel source right?

[MAF4Fam6]

Question:
Can I use the update query to update ALL columns, all rows, in the entire table from my linked excel sheet, without MANUALLY SPECIFYING EVERY TARGET RECORD?? ( I've got it working with 2 columns, but this will be a pain to type out every single record to update. I'd like to update the entire table with a click)

Can I use a delete query to then properly clean up any removed records after the update is completed?

The answers to your questions above are: Yes...and Yes.
However, in my experience with refreshing Microsoft Access databases through the use of an external source like Excel--this is not an easy task which requires extensive access programming as stated in my last post on the following thread: Batch Update to Access Table (random fields and records), which can be found on the link you provided.
I understand there is a desirable interest out there in the Access programming world as to how to achieve such an automated process as this. As a result, I am thinking about posting step-by-step instructions, in the simplest format possible on this forum as to how to accomplish this without the use of any VBA programming, through the use of regular Macros and Update/Append/Delete queries.
With that said, I noticed you are using Access 2003. Unfortunately, I only have limited access to Access 2003 on a different machine. Therefore, as a heads up, the instructions would be posted using Access 2007. But...the process would still be achievable in Access 2003.

-RC