Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Access check if crashed

Posted on 2013-05-31
Medium Priority
Last Modified: 2013-06-01
Hi Experts,

I have a Access database that should be running 24/7. But some times it crashes and i have to close it down and re-open it.

Can someone help m e with a solution that insures that access is still running and if not force close it and open it again?

Or something like that?
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 58
ID: 39210512
I have the setup where I have two DB's that check on one another.  

Each writes a file out to disk when it starts and when it finishes.  Also at startup, you kill the last end file.

The other program then looks for these files and the timestamps of when they were created.

By looking at the end file, you can tell how long it's been since it's last run.

By looking at the end vs the start, you can tell how long it too to run.

By looking for a start with no end, you know if a program is still executing.   By having a timeout value, you can tell if a program is hung.

By have the two programs run on two different machines, you can have a very sound check if it is executing properly or not.

 This is of course with the programs being scheduled using Windows Task Scheduler and executing every xx minutes rather then running 24 x7, but you could use the same idea.   Just have the app write out a status update to a file every xx minutes.

 And BTW, I use files because it's easy to check from another machine, but any flag with a time stamp will do (ie. record in a table).


Author Comment

ID: 39210513
Great idea, what if they run on the same computer?

And do you have some code I can re-use?
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39210652
<<Great idea, what if they run on the same computer?>>

 Well it's a little less robust then...if the scheduler stops or the OS locks up, then both programs fail.   I guess it depends on how critical your operation is.  With that said, I have run it both ways.

<<and do you have some code I can re-use? >>

 Oh it's simple stuff (and that's the beauty of it).  Example below.  This is from a setup where the two programs run on a four minute cycle and never take more then a couple of minutes to execute.  This is the code in the startup form.


Private Sub Form_Open(Cancel As Integer)
        Const RoutineName = "Form_Open"
        Const Version = "1.0"
        Dim strFileName As String
        Dim intFile As Integer
        Dim strError As String
        Dim intRet As Integer
        Dim oSendMail As New OCS_SendMail
        Dim strMessage As String
        Dim lngTime As Long
        Dim lngDiff As Long
10      On Error GoTo Form_Open_Error
20      If DebugMode() = True Then Stop
        ' Get command line.
        ' Arg 1 is process flag
        '    '*' - Preform all processing
        '    'NOALERTS' - Performs all processing other then sending alerts.
        '    'ALERTSONLY' - Only check for alerts.  Do no other processing.
        ' Arg 2 is company or '*' for all
        ' Arg 3 is location or '*' for all
30      Call GetCommandLine(3)
        ' Check for anything passed
40      If IsNull(gvarCmdArgArray(0)) Or IsEmpty(gvarCmdArgArray(0)) Then
50        gstrMBTitle = "Command Arguments Missing."
60        gstrMBMsg = "Argument for processing flag required."
70        gstrMBMsg = gstrMBMsg & vbCrLf & "Please correct."
80        gintMBDef = vbCritical + vbOKOnly
90        gintMBBeep = True
100       gintMBLog = False
110       Call DisplayMsgBox
120       GoTo Form_Open_Exit
130     End If
        ' Check for valid processing flag
140     If gvarCmdArgArray(0) <> "*" And gvarCmdArgArray(0) <> "ALERTSONLY" And gvarCmdArgArray(0) <> "NOALERTS" Then
150       gstrMBTitle = "Command Arguments Invalid."
160       gstrMBMsg = "Argument for processing flag invalid."
170       gstrMBMsg = gstrMBMsg & vbCrLf & "Please correct."
180       gintMBDef = vbCritical + vbOKOnly
190       gintMBBeep = True
200       gintMBLog = False
210       Call DisplayMsgBox
220       GoTo Form_Open_Exit
230     End If
        ' Set wildcards if nothing passed for company or location.
240     If IsNull(gvarCmdArgArray(1)) Or IsEmpty(gvarCmdArgArray(1)) Then
250       gvarCmdArgArray(1) = "*"
260     End If
270     If IsNull(gvarCmdArgArray(2)) Or IsEmpty(gvarCmdArgArray(2)) Then
280       gvarCmdArgArray(2) = "*"
290     End If
        ' 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 gvarCmdArgArray(0) = "*" Or gvarCmdArgArray(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

1030    On Error Resume Next

1040    Call ApplicationExit

1050    Exit Sub

1060    UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
1070    Resume Form_Open_Exit

End Sub

Author Closing Comment

ID: 39212795
Thank you!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question