kwol18
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
ASKER
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.
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.
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.
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.
ASKER
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
Thanks for the suggestions, I'll be trying them out today
<<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.
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.
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...
ASKER
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.
ASKER
Unfortunately our problem was not solved, it was deemed impossible. We ended up having to go for a different approach
ASKER
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.