Opening Microsoft Access database from server side code on IIS

HI,

I was hoping someone would be able to help me out.  We have a .net MVC web application running on IIS that needs the ability to launch a batch file which opens up a Microsoft Access database with some command parameters and prints a specified report to pdf, to be downloaded to the client.

The database has the AutoExec macro, and all necessary forms in place to open the report (passed in through /cmd parameters), and exports it to pdf format.  I created a bat file that works just fine.

When running it through the web app's server side code, the MSACCESS.exe process is started in task manager for the correct user, but the Access application does not open, nor is there any lock on the database file.  If I change the appPool to run as Local system, the process starts and the lock file appears, but nothing is executed.

As far as user permissions, I've already set full control for the directory housing the MSACCESS.exe, DCOM component services for Microsoft Access, and any directories it interacts with, for the DefaultAppPool identity, and IIS_IUSRS.  My appPool is running as ApplicationPoolIdentity, so the user is IIS AppPool/DefaultAppPool.

All permissions seem to be in place.

Does anyone have any suggestions (besides get rid of Access) on why the process would start, but not the application.  
We are stuck with integrating Access Reports for the existing client base due to the customization they already have in place.

Thanks in advance
kwol18Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
First let me say I don't know the answer to your question directly, but:

1. Access is designed to run as a foreground app meaning it wants to have a user logged in and be able to interact with a desktop.

2. If you don't log in with the correct user, available printers and paths may be different and cause problems.

In short, I believe "get rid of Access" is the correct answer here because what you want to do is not possible.

But, before going there, I would:

1. Check event logs for any issues.

2. Put some flagging/logging (writing a record to a table) into the app to try and determine where it's dying and on what.   That might give you a clue as to a work around, but I have to say I'm doubtful.

Access was not designed to be used the way your using it.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kwol18Author Commented:
I wish getting rid of Access was that simple.  I understand that we're using it in a very un-intentional way, but as of now it's our only option.  We're looking into going with something like Crystal Reports, but due to budget reasons we haven't approved anything yet.  Our biggest hurdle is the ~90 existing clients with over 25 reports each that would need converted.  If we could get around this issue and utilize Access we'd be able to have a temporary solution for existing clients.  

Thanks for the quick reply, and from everything else I've read online, it's probably not possible, but I have to keep looking.  If you have any other suggestions, please send them my way.
pcelbaCommented:
I am skeptic... Does MS Access support the run without appropriate interactive session? Does the report generation work when you execute your PDF report creation from e.g. Task Scheduler when the interactive session user is not logged in?

You could also test it under PSExec from PSTools (https://technet.microsoft.com/en-us/sysinternals/bb896649.aspx). I know PSExec is derived for remote execution but it should also work locally and the -i and -s parameters could help to investigate possible way for you.

If the PSExec succeeds then you may try to call PSExec from IIS app.

If it fails then you'll need to use different report engine and access the Access data via ODBC.

Sometimes the interactive application waits for keyboard entry when running under PSExec. The solution is to provide some input by DOS pipe:

echo.|PowerShell.exe -File "SomeScript.ps1"

But MS Access is very different from PowerShell...
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

kwol18Author Commented:
pcelba,

I'll give the PSExec a shot.  in the app code, I'm starting cmd.exe and passing the path the the bat file as the argument.  This does execute, as I can see cmd.exe start in the task manager.
OriNetworksCommented:
The only possible workaround I can think of involves creating a report queue and having someone manually run the reports.

For example,

1. When a user wants to run a report it puts a records of which report they want, any parameters, the person requesting it/email address. Possibly send an email to a report admin when this happens, see why below.
2. On a scheduled basis a person opens the access file and runs a macro, perhaps every hour or receives any email whenever someone requests a report that has been added to the queue. This macro can check the queue table, run any requested reports and save results. The macro can also email the user the results file or by some other means inform the requester that the report has been generated.
3. the requester now has their report file after waiting some time for someone to run it.
kwol18Author Commented:
A queue approach has been brought up, and also the option for windows service that runs, and wakes up periodically (every few minutes during business hours since they run a lot of reports), checks a file or table for new requests and runs it, but we're not sure if we'll run into the same behavior having a windows service try to run the exe.  

Thanks for the suggestions,  I'll be trying them out today
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<but we're not sure if we'll run into the same behavior having a windows service try to run the exe.  >>

 You will, which is why I'm pretty certain of my comments.

 But you could instead of a service have a station constantly logged in as a user, but locked, checking the queue.  That would work.

Jim.
pcelbaCommented:
a station constantly logged in as a user, but locked, checking the queue.  That would work.
I also agree. We've been running instant messaging this way but I personally don't like such solution because it seems to be a little bit twisted...
kwol18Author Commented:
Thanks all for the suggestions, but as you all pretty much agree, running Access through a back end process is not going to work.  We are looking at other reporting services that can use Access as the data source, such as Crystal Reports.  This allows us to keep the Access database as the core data source while still being able to run reports from server side logic.
kwol18Author Commented:
Unfortunately our problem was not solved, it was deemed impossible.  We ended up having to go for a different approach
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.