Opening Access from a windows service

[sebair]

I am writing a windows service in VB.Net 2008 to run on Win Server 2003.
Here is my code.

Public Sub PrintReport()

Dim acc As New Access.Application

Try

acc.OpenCurrentDatabase(My.Settings.dbLocation)

acc.DoCmd.OpenReport(_name, Access.AcView.acViewNormal, ,
_filter, Access.AcWindowMode.acHidden)

Catch ex As Exception

Throw ex

Finally

acc.CloseCurrentDatabase()
acc.Quit()

End Try

End Sub

The code errors at the OpenCurrentDatabase line. The thrown error is
written to the event log by the calling function. The error is "Rejected
Safe Mode action : Microsoft Office Access." Source: MS Office 11, Category:
None, Event ID: 2001.

When I run the same code in a console app it works fine. The db is opened
and the report is printed.

Does anyone have any idea why the code does not work when used in a service?

Thanks.

 

[David H]

The error message suggests that its security related. Since an Access
database can contain VBA code which can be executed automatically, its
probably a precaution against anything ontoward from happening.

You might be able to pull it off by going with a VBScript that opens the
designated *.mdb file using the command line switch that automatically calls
a macro. The macro of course would use the RunCode Macro action to turn
control over to the VBA module.

However, and a BIG however, is that the information that I've read generally
suggests that Office apps should *not* be run on a server. I don't recall all
of the specifics. Google around and you should be able to get additional and
more specific information.

A viable alternative is to set up a PC and let the PC handle the work.

 

[David H]

Here's some reference material about automating an Office app like Access on
a server.

http://support.microsoft.com/default.aspx/kb/257757

 

[sebair]

David,

Thank you for your quick response. The article you suggested in your second
post pretty much sums it up -- don't do it.

I wonder if SQL server suffers the same problems?

BTW, the reason I am doing this is that I have created a web site on which
users fill in a form. This data is stored in an access db. The server
hosting the web site is remote ( a Liquid Web dedicated server). and I access
it using Remote Desktop. I have written the code to run as a console app and
set a scheduled task to run the app every minute. This works perfectly.
Unfortunately, when I log off the server, all scheduled tasks stop.

I had thought that a windows service might be the answer as it will continue
running even after I log off the server.

Perhaps there is a simpler way to handle this. If you have any thoughts, I
would love to hear them.

Again, thanks.

 

[David H]

I almost mentioned about doing an ASP.NET solution. SQLServer can be viewed
as strictly a back-end database and is not intended to provide the general
end-user with slick forms and reports like Access. It is designed to run on a
server, however you can't use it to do what you're trying.

The fact that you're running the report every minute concerns me.

What exactly does the report contain? Is it for your use only or is it sent
to the users by email? Is it streamed to their machines?

Regardless, it sounds like you need an ASP.NET based reporting solution.
Snoop around 4guysfromRolla.com and asp.net. Also, depending on the nature of
the report you might be able to duplicate it using some of the server
controls available. There's a lot that you can with a Repeater.

 

[sebair]

Thanks David.

I'm not running the report every minute. The service simply checks the db
every minute to see if any new data has been entered in a particular table.
If not, it just goes back to sleep. If there is a new record, then I want to
open access, get the data and use it as a filter for an access report. New
records appear very infrequently.

The web front end is ASP.Net and has no trouble accessing the db. If I
could do the reports from there it would be great. I'll look around and see
what I can find.

Thanks again.

 

[David H]

What information exactly does the report contain?

Also depending on the infrastructure, it might be possible to connect a PC
to the server like a traditional network and run the report on your local
machine. I was investigating server hosting for a client and found a company
that provided hosting solutions where the web server and file server were the
same machine.

Also, if you just need to know when new records are added, you may want set
up ASP.NET to automatically general email notifications.

 

[David H]

The cat jumping on my chest distracted me. If I'm not mistaken, you Access
can connect to SQLServer over IP which would allow the front end to sit on a
local machine and the run the report that way. SQLServer comes in a free
edition that is more than comparable to Access and upsizing from Access to
SQLServer is not that difficult of a task.