Batch Update to Access Table (random fields and records)

[cryswater]

I have a table in Access that I pull quarterly data out of and export it to Excel. This is then given to multiple users for updating information. There are fields that also don't receive updates. I take the updated information and have been doing a manual process of searching for the unique ID of the record (contract number) and updating the fields of information one at a time.

This manual process is a real pain...



Is there a way to batch update a table with information that only alters the fields of information that were changed and doesn't affect any other record or any other field that wasn't changed?

Also, I need method of verifying that the process was successful; by comparing the newly updated table to the backup I'd make before the update?

Happy Holidays!

[MAF4Fam6]

Access provides no automatic way, that I know of, to batch update a Table without the use of VBA and/or SQL programming or even Conditional Macros using Built-In-Macros along with Append, Delete, and Update queries to make this happen. Any of these options would require a very detailed step-by-step process in the programming. Is this achievable? Definitely!
No doubt, your process is quite cumbersome.
A concern is that there are no checks and balances for your user base on the data they are updating in Excel.
A few recommendations:
1. If feasible, try sharing your database. A split database in this case would be the preferred method.
Or 2...Build a separate database for your user base. Have users import your excel sheet into their own separate database so they can update the information you require via a form. At least this way, their form can perform the checks and balances they need, to ensure they are giving you a quality product in return. Once they complete all their actions, they can export an excel file from Access and forward to you for import into your main database.

RC

[NTC]

you would have to do a compare between what you sent them and what you receive back from them...while a step up from your manual eyeball search -- still kind of a pain; easiest thing is to add a new field yes/no check box and insist that they check it if there is any change...though with humans that is unreliable...

b.t.w. is very similar to a situation where we moved over to AccessTables.com service...there is a Case Study on their site very similar to your description...

[MAF4Fam6]

As stated on my previous post...batch updating to an Access database table from an external source is definitely achievable and highly recommended in this situation with the amount of manual work being put forth in the current process. But again...getting Access to achieve this is not an easy task depending on how many fields are being changed.
I am almost tempted to write a step-by-step process on my personal Blog on this Forum as to how to accomplish this without the use of any VBA programming. Yes...this can be done through the use of regular Macros and Update/Append/Delete queries. But of course, it can also be accomplished by incorporating VBA programming for those who prefer this option.
My biggest concern I have in this scenario is the possibility of multiple users having access to the same record or records by unique IDs on their respective copy of the quarterly spreadsheet. This could potentially cause some data loss during the database refresh process depending on which spreadsheet is imported and processed first into the db than the other(s). Therefore, in order to prevent any data conflicts, I would highly recommend each spreadsheet holder be allocated a unique block of unique IDs (i.e. contract no (PK)).

-RC

[specterunseen]

As stated on my previous post...batch updating to an Access database table from an external source is definitely achievable and highly recommended in this situation with the amount of manual work being put forth in the current process. But again...getting Access to achieve this is not an easy task depending on how many fields are being changed.
I am almost tempted to write a step-by-step process on my personal Blog on this Forum as to how to accomplish this without the use of any VBA programming. Yes...this can be done through the use of regular Macros and Update/Append/Delete queries. But of course, it can also be accomplished by incorporating VBA programming for those who prefer this option.
My biggest concern I have in this scenario is the possibility of multiple users having access to the same record or records by unique IDs on their respective copy of the quarterly spreadsheet. This could potentially cause some data loss during the database refresh process depending on which spreadsheet is imported and processed first into the db than the other(s). Therefore, in order to prevent any data conflicts, I would highly recommend each spreadsheet holder be allocated a unique block of unique IDs (i.e. contract no (PK)).

-RC

I'm interested! Let me know if you post up a tutorial on this!

[MAF4Fam6]

Will do! I am strongly considering this with the amount of interest out in the Access programming world...not to mention the amount of man-hours saved in the process. This is what Microsoft Access is all about--Automation.

-RC