Request: Build an MS Access 2013 file (.accdb) that will update a SharePoint 2016 list whenever a user makes edits to an MS Excel 2013 file (.xlsx)
Bottom Line Upfront: I work with a small group of people, and I’m the only one who understands how powerful centralizing data on SharePoint via lists can be. My problem is I am not well versed in Access query building or VBA. I have very limited permissions on SharePoint, but I am able to create lists and edit them. Using Quick Edit on SharePoint isn’t convenient, and neither is using Access tables to append, add, and/or delete records from a list.
I have created an access database. I imported my SharePoint list into it via a linked table. I can make individual field/cell edits from inside Access and they reflect on the SharePoint list. Now my goal is to go bigger, by having a linked excel sheet on the same access database and building update and append queries so that any changes to the excel file, gets pushed into the SharePoint list for all to see.
I understand the concept, but just answering “build an append query” or “create an update query” doesn’t help me at all. I’ll need a specific step by step walk through.
To be honest, I’m not all that incompetent, I understand that the columns from both the SharePoint List and the Excel sheet should match and I generally understand the theory behind it, I just don’t know how to build it. My best guess goes something like this:
- An update query between the linked excel sheet (tbl_1) and the linked SharePoint list (tbl_2).
- An append query that will take the results of #1 (qry_1) and edit the fields of tbl_2.
- A VBA Module that starts the process from a click button event on a form.
My company IT group can make this for me in less than 10 minutes, but they don’t lift a finger without a support ticket, and I can’t submit a support ticket until I can show the value of having something like this to my manager.