topic Updating MS Access Table with Values from BP Collection? in Product Forum

Updating MS Access Table with Values from BP Collection?

DeclanKeeton — Fri, 10 May 2019 14:54:00 GMT

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.

Google suggests using MSâ€¦

John__Carter — Fri, 10 May 2019 19:45:00 GMT

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

Hi John, Â I think the levelâ€¦

DeclanKeeton — Mon, 13 May 2019 20:25:00 GMT

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!