PowerShell, SharePoint, & MS Access – Oh My!

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.

PS-SP-Access

The setup:

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.

The Issue:

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.

Read more