Getting notification upon code pausing execution.

Hi Experts,
I have some (Access) code that is supposed to be running all the time.
Would like to get an email notification in case the code stops executing for whatever reason.
What is the best way to accomplish that?
Thanks
LVL 6
bfuchsAsked:
Who is Participating?
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.

Martin LissOlder than dirtCommented:
If it's because of an error then you could add an error routine or routines that display a msgbox and/or sends you an email when the error happens. If it's not then without some external program checking some value produced by the Access program I don't how how you'd do it.
bfuchsAuthor Commented:
Hi,
If it's not then without some external program checking some value produced by the Access program...
Perhaps upon starting the Access code we can get the handle of the Access program, then have a batch file start looking for that handle id and if doesn't find it, send an email...
Does that make sense?
Thanks,
Ben
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Stops running how?  An error?  The user stops it?  Something else?

If you mean an error, then add an error handler that included an email routine (SendObject is the simplest way to do this).
If you mean the user stops it, then the question becomes how do they stop it?  Close, cancel button, ... you'd need to trap the button click, form closure, ... and use a similar email routine as previously mentioned.
If it's stopped by the db being closed, then open a hidden form at the startup, and use that's form's close event to send an email.  Thus when the db is closed, it will first close the hidden form triggering the email.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Martin LissOlder than dirtCommented:
Perhaps upon starting the Access code we can get the handle of the Access program, then have a batch file start looking for that handle id and if doesn't find it, send an email...
Does that make sense?
Yes.
bfuchsAuthor Commented:
Stops running how?  An error?  The user stops it?  Something else?
This will be placed on the server and keep running indefinitely, no user intervention.

What I'm looking to oversee is

 A) If got stuck by an unhandled error event.
 B) System crash.
 C) Server restart w/o restarting the program.
 D) Hardware failure etc...

Any idea how to put it to work the idea mentioned above?

Thanks,
Ben
Gustav BrockCIOCommented:
If the code stops executing, it stops, thus cannot do anything like sending an e-mail.

So, you will have to generate some kind of heartbeat with a certain interval, then watch if this stops.
You could:
  • send an e-mail as the heartbeat; if the e-mails stop arriving, the application has stopped.
  • create a log file and have another application to watch this; if no new entry is created, an e-mail will be sent to you
  • create a syslog entry if you have a syslog server running

What to choose depends very much on your scenario.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I do this by using two Access programs that check that the other is running.   I do this in each by:

1. writing a text file at startup and deleting any shutdown file.
2. Writing a text file at shutdown.

The other program then can look for the file(s) and look at the date/time stamps.    If there is a startup file and no shutdown file, then the app is currently running.   If that exceeds a certain amount of time, then the app is stalled.

If both files exist, then the app is not currently running and you know how long the last run was, and also how long it has been since the last run, which might also trigger an error.

Example of the code below.

Jim.

          '
          ' Take care of time stamp files.
          '

          ' Write out a date/time stamp for the start of this run.
300       strFileName = GetPathPart_TSB(CurrentDb.Name) & AppShortName() & "_START.TXT"
310       On Error Resume Next
320       Kill (strFileName)
330       On Error GoTo Form_Open_Error

340       intFile = FreeFile
350       Open strFileName For Output As intFile
360       Print #intFile, "Started processing"
370       Close #intFile

          ' Kill the END file.
380       strFileName = GetPathPart_TSB(CurrentDb.Name) & AppShortName() & "_END.TXT"
390       On Error Resume Next
400       Kill (strFileName)
410       On Error GoTo Form_Open_Error

420       Call AppMain

          ' Now check if OrdMon is running.
430       If GetCommandLineArg(0) = "*" Or GetCommandLineArg(0) = "ALERTSONLY" Then
440           If CheckIfAppRunning("OrdMon") = False Then
                  ' Not currently running.
                  ' Look at end date/time stamp.
                  ' Overdue if more then xx minutes old.
450               strFileName = GetPathPart_TSB(CurrentDb.Name) & "ORDMON_END.TXT"
460               If Dir(strFileName) = "" Then
                      ' No END timestamp file - ORDMON never run.
470                   oSendMail.SetParams "ORDPROC", ".", "."
480                   oSendMail.Subject = "ALERT - Order monitor not running."
490                   strMessage = "Checked at: " & Format$(Now(), "General Date") & vbCrLf & vbCrLf
500                   strMessage = strMessage & "Order monitor has not run - ORDMON_END.TXT not found."
510                   strMessage = strMessage & "1. Check that Order monitor (ORDMON.MDB) is scheduled and enabled." & vbCrLf
520                   strMessage = strMessage & "2. Check that task scheduler is running." & vbCrLf
530                   strMessage = strMessage & "3. Check that Order monitor (ORDMON.MDB) is not failing with an error at startup." & vbCrLf
540                   oSendMail.Send
550               Else
                      ' Have END timestamp file - check if over threshold
560                   lngTime = GetAppConfigValue("ORDPROC", "ORDMONSchedule", ".", ".")
570                   lngDiff = DateDiff("n", FileDateTime(strFileName), Now())
580                   If lngDiff > lngTime Then
590                       oSendMail.SetParams "ORDPROC", ".", "."
600                       oSendMail.Subject = "ALERT - Order monitor run is overdue."
610                       strMessage = "Checked at: " & Format$(Now(), "General Date") & vbCrLf & vbCrLf
620                       strMessage = strMessage & "Order monitor (ORDMON.MDB) has not run in " & lngDiff & " minutes" & vbCrLf
630                       strMessage = strMessage & "1. Check that Order monitor (ORDMON.MDB) is scheduled and enabled." & vbCrLf
640                       strMessage = strMessage & "2. Check that task scheduler is running." & vbCrLf
650                       strMessage = strMessage & "3. Check that Order monitor (ORDMON.MDB) is not failing with an error at startup." & vbCrLf
660                       oSendMail.Message = strMessage
670                       oSendMail.Send
680                   End If
690               End If
700           Else
                  ' ORDMON is running.
                  ' Look at start date/time stamp.
                  ' May be stalled if more then xx minutes
710               strFileName = GetPathPart_TSB(CurrentDb.Name) & "ORDMON_START.TXT"
720               If Dir(strFileName) = "" Then
730                   oSendMail.SetParams "ORDPROC", ".", "."
740                   oSendMail.Subject = "ALERT - Order monitor may be stalled."
750                   strMessage = "Checked at: " & Format$(Now(), "General Date") & vbCrLf & vbCrLf
760                   strMessage = strMessage & "Order monitor is running, but ORDMON_START.TXT not found." & vbCrLf
770                   strMessage = strMessage & "1. Check for Order monitor (ORDMON.MDB) being stalled with error."
780                   oSendMail.Message = strMessage
790                   oSendMail.Send
800               Else
810                   lngTime = GetAppConfigValue("ORDPROC", "ORDMONStalled", ".", ".")
820                   lngDiff = DateDiff("n", FileDateTime(strFileName), Now())
830                   If lngDiff > lngTime Then
840                       oSendMail.SetParams "ORDPROC", ".", "."
850                       oSendMail.Subject = "ALERT - Order monitor may be stalled."
860                       strMessage = "Checked at: " & Format$(Now(), "General Date") & vbCrLf & vbCrLf
870                       strMessage = strMessage & "Order monitor (ORDMON.MDB) has been running " & lngTime & " minutes" & vbCrLf
880                       strMessage = strMessage & "1. Check Order monitor (ORDMON.MDB) for error."
890                       oSendMail.Message = strMessage
900                       oSendMail.Send
910                   End If
920               End If
930           End If
940       End If

          ' Now write out a date/time stamp for the end of this run
950       strFileName = GetPathPart_TSB(CurrentDb.Name) & AppShortName() & "_END.TXT"
960       On Error Resume Next
970       Kill (strFileName)
980       On Error GoTo Form_Open_Error

990       intFile = FreeFile
1000      Open strFileName For Output As intFile
1010      Print #intFile, "End processing"
1020      Close #intFile

Open in new window

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
bfuchsAuthor Commented:
Thanks to all participants!

@Jim,
I was thinking (this morning) about similar approach.
To have a file on the server and every time the code runs modify that file, then later have a program examining the time of that file last updated...

Thanks,
Ben
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Jim, but what guarantees the 2nd db is running?
bfuchsAuthor Commented:
@Daniel,
Once I have this split into two applications, and they can be running in separate servers, it should be solving most of my concerns, as very unlikely both servers are down at the same time.
Thanks,
Ben
Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Valid point.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Apologies to all....I am not getting any notifications on my questions.

@Daniel,

<<Jim, but what guarantees the 2nd db is running?>>

 The first.  They check each other.

@Ben,
<<Once I have this split into two applications, and they can be running in separate servers, it should be solving most of my concerns, as very unlikely both servers are down at the same time.>>

 Exactly.   You'd want them running on separate servers to cover all the bases.  But in the example above, these happen to be running on the same server.  It would have been better had they not been though.

 Even so, it's rare for a server to be down (lots of other things get noticed) and it's never been an issue.   This setup has been running for over fifteen years and I'm sorry to say, the server has never been the problem<g>.

Jim.
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
VB Script

From novice to tech pro — start learning today.