Updating MS Access Table with Values from BP Collection?

[Declan__Keeton]

Hi, I'm wondering if it is possible to bulk update a field within a table in MS Access, using values from a field within a Collection in BP? I'm also wondering how the Syntax for that looks. I use the Action - Get Collection within the OLEDB object to run a Select query to pull the information back. Once a process has been ran and values in my collection are updated, I want to use this collection to update the Table in the DB. For example- Table1 ID / Owner/ Task ID/ Status 1 / Joe       /45        / Pending  2  / Alan    /46        / Pending 3  / Mellisa/ 47      / Pending   BP Collection- [Results] ID / Owner/ Task ID/ Status 1 / Joe       /45        / Successful 2  / Alan    /46        / Exception 3  / Mellisa/ 47      / Successful   I want to update Table1's Status field with the values from [Results.Status]   For those that want some more Context. The operation are going to be posting records onto a SharePoint, ready for the robot to data scrape and contact the customer, where possible. I am unable to use any of the SharePoint APIs due to restrictions I have created a Linked Table within MS Access that will update the SharePoint.

[John__Carter]

Google suggests using MS Data Access Objects  https://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/dbengine-object-dao

[Declan__Keeton]

Hi John,   I think the level of complexity with DAO is going to mean I won't have time due to my delivery date.   I don't have to deal with a large volume of records fortunately so for now I can just use a loop stage and reference the field in the collection as part of the SQL Query.   // For anybody reading this like me that struggled with how to piece the Syntax together, then it's actually really simple. Don't know why it seemed difficult to me.   The solution I've opted to go for is Loop Start Calculation to Data Item [SQL Query] " UPDATE Test_Deck_Table SET Status= ' "& [Results.Status]  & "' WHERE ID = & [Results.ID]  Action - Data.OLEDB - Execute  input- [SQL Query] Calculation to data item [SQL QUERY] ""  (To reset the data item) LOOP END   -Notice that when referencing Results.ID I didn't use quote marks as numerical value.   If I get the time to explore the DAO avenue discussed in the forum then I'll post back my results here. If anybody has any experience of that then please share!