I had to help out with trying to automate a process that updates an Access file unattended.
I get my geek hat on and try to automate this using a SSIS package. That didn’t work out as expected so I then write a little C# program to do the same thing. In both cases, I had to install the Microsoft Office Primary Interop Assemblies (PIAs). With the SSIS package, I tried implementing this with a Script Task. When that failed, I wrote the console application to do the same thing.
This console application worked. Then I did further testing and realized that I don’t think I’ll ever get this working like I want it to work. At the end of my testing I came to the conclusion that to automate this, the account used needs some permission (account I used had administrative privileges) and needs an interactive session. Further research shows that Microsoft didn’t create Access with server-side automation in mind (see here (until the link dies) if you don’t believe me).
This is upsetting but until I find a workaround, I’ll shelve this task too.