Front end running under acc 2007, O/S = XP under VMWare, Backend SQL 2008
express.

Once again a client has asked me to effectively run an MS Access application
effectively as a service - ie polling records every 5 minutes and firing off
emails to their business clients.
Unfortunately, previous experience has shown me that when the front end runs
under VMWare and "gets busy" running queries against an SQL backend
(particularly on a repeating basis) it uses up "too much" of the VMware
memory resources and this manifests as ODBC errors.
(Personally I don't see this as a fault in MS Access but as a fault in
VMWare - but your try to get IT support to admit that)
Now I know that I can move processing to the SQL server by handling as much
of the work as possible in stored procedures (not very good at these yet)
and I know that I can copiously error trap every line of code accessing
tables.
Also there are plenty of utilities around to run an application as a service
and to smoothly restart them if they fail.
My reading so far vaguely hints at a service being more robust, gaining
more resources and restarting automatically.

Has anyone tried running an Access application as a service and have you
seen any benefits?

If this all proves too flaky I will need to rewrite it in visual C sharp
(beginner only) - but this would mean rewriting all my nice code that
converts a report to pdf and emails the pdf (and logs it to a document
management system, etc, etc)

Many thanks in advance
Tony Epton

Hi Tony,

I have played with the idea of running an Access App as a service, and
thrown the idea in the bin. The problem simply put is that if a pop-up
window or message appears and there is no-one there to deal with it
the application stops doing anything until that window is dealt with -
ie/ your service is dead. In your situation I would go with the re-
write, or look for an open source alternative that already does what
you want to do (or close to and modify it). If you are not use to
working with C# then you can also create the service in VB.Net - in
the end .Net is .Net for most intents and purposes. I remember seeing
a project for VB.Net that was released to the world specifically for
creating windows services. A bit of googling will probably turn it up.

What is it exactly that you want your application / service to do?

Cheers

The Frog

"The Frog" <mr.frog.to.you (AT) googlemail (DOT) com> wrote

Many thanks for the advice.
The utility I was planning to use will restart the service if it crashes -
so I was thinking of trapping every error to a "Quit"
(Mind you - it will keep on starting up and aborting if there is a hard
error)
Sounds like it would all be flaky from what you are saying.
The application is examining GPS data sent automatically from Blackberries
via a 3rd party application and contacts clients, via email, when a worker
is stopped at a nearby farm

Tony

"aceware" <aceware (AT) iinet (DOT) net.au> wrote in
news:fK2dnXxpoJLE-0HQnZ2dnUVZ_hydnZ2d (AT) westnet (DOT) com.au:

Can't be done because system services CAN NOT have user interface.
Period, end of statement.

You should investigate if what is needed can be done directly via
DAO from a VBScript that is run in the task scheduler.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

What if I have no forms at all, autoexec runs a procedure that examines a
record set every minute, the code has no msgbox commands and any error is
trapped to make the application quit immediately.
(the only problem is I was going use an invisible form with a 1 minute timer
to trigger my regular polling)

Tony

On Wed, 25 May 2011 10:15:42 +0800, "aceware" <aceware (AT) iinet (DOT) net.au>
wrote:

Given that it's SQL Server 2008 can you, somehow, write a little .Net
code which runs every five minutes under a SQL Server 2008 task. Or
run it as a trigger, or whatever is best, whenever the records are
added so it's immediate so you don't have to wait the five minutes.
(I think the recommendation is now to use something else instead of
triggers but I can't recall exactly what it is now.)

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

"aceware" <aceware (AT) iinet (DOT) net.au> wrote in
news:_JednYYW8dGpvX_QnZ2dnUVZ_jGdnZ2d (AT) westnet (DOT) com.au:

The app you're running is ACCESS and it has a UI that is created
when the service runs. Because running as a service service doesn't
initialize the desktop and provide the support for the UI to
generated, it won't work. That is, this isn't about whether or not
the UI generates prompts, but about whether or not the application
being run HAS a UI.

You could try faking it with the SRVANY.EXE from the Windows Server
Resource Kit. I've had luck using that with a few programs, but have
not tried it with Access.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

The Frog <mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:f6e5e79f-36bf-4049-9983-71ecd314b1bb (AT) hl1g2000vbb (DOT) googlegroups.co
m:

Er, merge replication is not a new SQL Server feature -- it's been
around since at least SQL Server 2000.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

I never said anything about merge replication being new. I first saw
it and used it in SQL Server 2000. It is however fairly unique in the
database world to have merge replication as an available feature. I am
not aware of another database that makes the merge scenario quite so
straight forward as SQL Server does. Therefore I consider it a great
strength, and that applies to SQL Server 2008 as much as any of the
previous versions going back to 2000. FWIW my first MCDBA training was
done on SQL Server 2000 and that is where my understanding of
databases really began. My only gripe with SQL Server is the cost to
have a 'full blown' server (not the Express) is really far too high
for a lot of SME's (50 or less employees). That aside it is a
brilliant product.

The Frog

The Frog <mr.frog.to.you (AT) googlemail (DOT) com> wrote in
news:67f5d89e-a75b-49f9-bae0-b938bed2f3b7 (AT) 32g2000vbe (DOT) googlegroups.com
:

!!!!

Jet had it in version 3.5, the one released with Access 95.

MySQL has had it for quite a long time, PostgreSQL had it before
then.

I don't see it as new at all. It's something I consider to be
standard for enterprise-level database engines.

It may be that it's easier than merge replication in other server
databases (you do know that the SQL Server team that implemented it
was the team the developed Jet Replication, right?), but it's not
what I consider a "remarkable" feature, in the sense of "worth
remarking about." I can't see how any database intended to be taken
seriously at the enterprise level could not have it.

I'm not disputing that it's a strength, but I don't see why it's
worth mentioning something that is really an expected feature at
this level of database product.

MySQL is getting better, and I find it very easy to use with
phpMyAdmin, but I'm a relatively casual user (and for read-only
websites only, so no large user populations or concurrency issues,
etc.). PostgreSQL was great long ago, but I haven't looked at it for
a long time.

I find SQL Server easy for the easy stuff, hard for the hard stuff,
where it often betrays its ancient roots (before they bolted the GUI
admin tools onto it).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/