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.
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.
Automation Code
The below block contains the code i used to perform the MS Access Automation:
PS D:\JTWorkFolder> $access = New-Object -ComObject Access.Application
PS D:\JTWorkFolder> $access.OpenCurrentDatabase("d:\AdminReports\CurrentInits\CurrentInitReport.accdb")
PS D:\JTWorkFolder> $access.DoCmd.OutputTo(3, "CurrentInitsRpt", "Rich Text Format (*.rtf)", "d:\AdminReports\CurrentInits\reportname.rtf",$false)
PS D:\JTWorkFolder> $access.DoCmd.OutputTo(3, "CurrentInitsRpt", "PDF Format (*.pdf)", "d:\AdminReports\CurrentInits\reportname.pdf",$false)
PS D:\JTWorkFolder> $access.quit
Obviously, it’s not the actual production script that I use, but it’s contains all of the important bits for automating.
Note – the two different commands to produce two different types of output – one for an RTF document and another for PDF.
Tip: In most of the examples I found on the internet, the values for the parameters in the DoCmd.OutputTo command had assume that you had a reference to the pre-defined constants. To find the actual value of these constants, go into the Visual Basic editor for that application and search against the Object Browser. For example the output format for pdf is acFormatPDF – in Access’ Object Browser, that returns back a value of: “PDF Format (*.pdf)” — yeah, the gotcha is that it’s a string and not numeric value.
I entered the above into PowerShell directly to see how each step would work before I put it into a script. At this point I got my first error.
Initial Error
Every time i ran my code, a security dialog box would pop up and challenge me for credentials. This was curious as i had thought that i had full rights, etc. Obviously, as I was interacting with the shell, entering in additional credentials isn’t an issue – the issue is when I take all the code and wrap it up in a nice script to be executed as a Schedule Task. We can’t have the dialog box pop up. The security dialog box only pops up the first time you access the resource.
For an embarrassing amount of time, i thought it was MS Access that was challenging me for security. I researched and tried a dozen of different ways of trying to get that dialog box intercepted and/or re-write my code so that i submitted different credentials, etc. As it turns out, I was mistaken, it was SharePoint via the linked list that was challenging me.
This article explained why we get the security prompt – it’s IE not Access: https://accessexperts.com/blog/2012/01/18/how-to-save-your-sharepoint-password-in-windows/
This allowed me to store my credentials on the server and the dialog box never popped up again.
Scheduled Task
After getting the automation to work interactively on the server, I wrote a script that produced the pdf version of the report I wanted. Of course, that’s when I ran into issues running the exact same code as a scheduled task on the server.
In essence, the script would execute but hang where the report was being generated. To make matters worse, it would leave MS Access open as a running process. I knew I shouldn’t be using MS Office programs on a server, but I figured that this was a scheduled job and I didn’t have multiple users trying to perform the same automation, say if the code was in a webpage. Hence I thought I could get away with it.
The error code was (after adding error logging in to the production script…):
run time error ‘2302’:
Microsoft Access can’t save the output data to the file you’ve selected
I changed my script to output to RTF format and everything worked! I was very excited till after I started adding more than just test data into my SharePoint list. After i had enough information in the system to generate more than one page, the RTF format started showing it’s limitations (‘bugs’.) The footer was in on the wrong page – this cause literally a ‘blank’ page between actual pages of content just to produce the footer. After even more research, it i found out that the RTF export feature has been buggy for years. It all boiled down to line space is different in MS Word versus MS Access.
This basically forced me to go back to PDF format. The PDF format is perfect – the only issue is that I had to get around the above error.
Well, it turns out that I needed to make one more change to force the application to work as I expected it to. I had to force the DCOM component of MS Access to operate under my user account name as opposed to the user account associated with the scheduled Task. One would assume that the security information of the schedule task would be passed along to everything it needed within the task. Yet, it didn’t. So following the information in this link, http://theether.net/download/Microsoft/kb/288367.html, I was able to configure my the DCOM component to execute with my credentials.
Tip: Also, if you are using DCOMCNFG in 64bit mode, you will probably not see the Office objects if you installed the 32-bit version of Office. You’ll need to fire up MMC with the /32 bit option (yeah, this one took a little bit of time to find):
C:\WINDOWS\SysWOW64>mmc comexp.msc /32 (source link)
After the above configuration change, the scheduled task worked perfectly.
Wrap up
The above was pretty high level with details where I think you would need it. I hope the above helps someone else out or at least shave some time off their own projects as it took me quite some time to pull all the parts together.
Thanks!
JT