I’ve been getting into PowerShell scripting a lot at one my clients and I encountered a pretty unique situation that I spent days trying to find the answer to. I figure I would post a quick note about it and see if I could save someone else some time.
One of the tasks I have at this client is to put together a weekly Status Report MS Word document for management. Although I try to keep the information brief, we are running about 7-8 pages in length (we have a lot of plates spinning at the same time.) I have to follow up with a dozen people to get their updates. Although I’ve tried to get them to submit their information in a standardize format, etc, the information comes in wildly different formats, completeness, or lacks context, etc. The entire process can take a few hours to pull together – depending on the editing required. I thought that a better use of time was to come up with a SharePoint solution that would allow everyone to contribute their portion of the status report and have an automated weekly report generated automatically for management.
After setting up my SharePoint site/list, creating the MS Access database, and connecting it to said list, automating MS Access from PowerShell as a scheduled task on a server proved to be quite challenging. There were a number of error messages and access issues that needed to be addressed before everything worked.