Solved

Access check if crashed

Posted on 2013-05-31
4
253 Views
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?
0
Comment
Question by:DCRAPACCESS
  • 2
  • 2
4 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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).

Jim.
0
 

Author Comment

by:DCRAPACCESS
Comment Utility
Great idea, what if they run on the same computer?

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

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
<<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.

Jim.

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

Form_Open_Exit:
1030    On Error Resume Next

1040    Call ApplicationExit

1050    Exit Sub

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

End Sub
0
 

Author Closing Comment

by:DCRAPACCESS
Comment Utility
Thank you!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now