4 Replies Latest reply: May 15, 2014 10:10 AM by Grant Perkins RSS

    Infrequent problems

    RalphB _

      Hi all,

       

      I've been having intermittent problems running part of a script.  It is part of a multi script process to balance out companies every morning.  The part I'm having problems with is the second script that needs to be ran. Ocasionally when running it as part of the entire process it stops trying to open a file.  But when you run the script by itsself, it runs fine.  File size doesn't seem to matter.  I checked some files and they are rarely over 100 KB in size.

       

      Dim stPmtProcMod As String, stLockboxGenericMod As String, stLockboxTotalMod As String

       

      On Error GoTo ErrLogLockBox

       

      stLBGeneric = "N274000.000"

      stLBPmtProc = "N297002.000"

      stPmtProcMod = "Payments Processed.xmod"

      stLockboxGenericMod = "Generic Report.xmod"

      stLockboxTotalMod = "LockboxTotal-a.xmod"

      stResultsSS = "G:\XX\Accounting\Daily Rec's\Regular\results.xls"

       

      i = 0

      Serveron = IsServerActive()

      If Serveron = 0 Then

          Set Monarchobj = CreateObject("Monarch32")

      End If

      t = Monarchobj.setlogfile("G:\Accounting\PRM_ACTG\Logs\MonarchLog\xxxBalance\" & Application.UserName & " - " & Format$(dDate, "mm-dd-yyyy") & " Import Results.log", True)

      For Each varCoNo In colCoNo

      stCoNo = varCoNo

      stReptPath = "R:\" & stCoNo & "\" & stReptDate & "\" & stLBPmtProc

      openfile = Monarchobj.SetReportFile(stReptPath, True)

      If openfile = True Then

          i = i + 1

      End If

      Application.Wait (Now() + TimeValue("00:00:05"))

      Next varCoNo

       

      If i > 0 Then

          openmod = Monarchobj.SetModelFile(stModPath & stPmtProcMod)

      End If

      If openmod = True Then

          DoEvents

          Application.Wait (Now() + TimeValue("00:00:02"))

          exportfile = Monarchobj.jetexportTable(stExpDB, "LockboxErrors", 0)

       

          Application.Wait (Now() + TimeValue("00:00:02"))

          DoEvents

          openmod = Monarchobj.SetModelFile(stModPath & stLockboxTotalMod)

          exportfile = Monarchobj.jetexportTable(stExpDB, "LockboxTotals", 0)

          exportfile = Monarchobj.jetexportTable(stExpDB, "Imports", 0)

          exportfile = Monarchobj.jetexportTable(stResultsSS, "LockboxTotals", 0)

      End If

      Monarchobj.closealldocuments

       

      For Each varCoNo In colCoNo

      stCoNo = varCoNo

      stReptPath = "R:\" & stCoNo & "\" & stReptDate & "\" & stLBGeneric

      openfile = Monarchobj.SetReportFile(stReptPath, True)

      If openfile = True Then

          i = i + 1

      End If

      Next varCoNo

       

      If i > 0 Then

          openmod = Monarchobj.SetModelFile(stModPath & stLockboxGenericMod)

          DoEvents

          Application.Wait (Now() + TimeValue("00:00:02"))

      End If

      If openmod = True Then

           

          exportfile = Monarchobj.jetexportTable(stExpDB, "LockboxErrors", 2)

      End If

      Monarchobj.closealldocuments

       

      openfile = Monarchobj.OpenDatabase(stExpDB, "", "qrynet", stModPath & "XXXNetLockbox.xmod")

      DoEvents

      Application.Wait (Now() + TimeValue("00:00:02"))

      exportfile = Monarchobj.jetexportTable(stResultsSS, "LockboxErrors", 0)

       

      Monarchobj.closealldocuments

      Monarchobj.Exit

      Set Monarchobj = Nothing

       

      Exit Sub

       

      ErrLogLockBox:

      Monarchobj.WriteToLogFile ("ERROR # " & Err.Number & " " & Err.Description & " " & Err.Source)

      Resume Next

      End Sub

      /code

       

      When it fails, it always stops at the line stReptPath = "R:\" & stCoNo & "\" & stReptDate & "\" & stLBPmtProc and on the first company it loads.

       

      Nothing has changed in the reports and we have successfuly used this for a few years now.  We run the entire process early in the AM before we get in because it takes close to an hour to run everything it does.

       

      We are running Monarch V10.5 Pro, and Windows XP service pack 2.

       

      Any suggestions Sandy, Grant, Nick, Olly?

        • Infrequent problems
          Data Kruncher

          Honestly... not too sure what the problem is here Ralph.

           

          I'd try adding the values of stCoNo , stReptDate and stLBPmtProc to the error log to see what they are when it blows up.

           

          Otherwise, is it safe to assume that this code compiles properly? What's the app? Excel, Access, something else?

           

          Is there an Option Explicit declaration to ensure that all of the variables are properly named (no typos)?

           

          Excel workbooks, especially macro enabled files built with Excel 97 through Excel 2003 sometimes have randomly appearing problems after they've been used extensively or the code has been altered several times. I've had good experience using a free tool called [URL="http://www.appspro.com/Utilities/CodeCleaner.htm"]VBA Code Cleaner[/URL] when my old files developed "glitchy" attitudes. This might not be your issue at the moment, but it's worth mentioning.

           

          Sorry, random ideas more than anything concrete so far...

            • Infrequent problems
              Olly Bond

              Hello

               

              My hunch is that the resource called R:/ might not be available in the early hours - perhaps there's a backup of the network drive taking place then? If the PC can't see R:/, or can't get a lock on the file it finds there, that might explain the hanging. DataPump allows you to handle this gracefully with retries and alerts, by the way.

               

              HTH

               

              Olly

                • Infrequent problems
                  RalphB _

                  Sandy, thanks for the suggestion.  The code is in Excel 2003.  I'll try the code cleaner as that sounds promising.

                   

                  Olly, the R drive is available as I have another script that just finished before calling this as this is the second script of 3 that runs and it accesses the same directories in R drive so that is not the problem.  I know about Data Pump but I know the company will not spring for it as I have asked in the past.  I have been pushing to get more departments and employees here using Monarch but the company will not buy it.  I have to beg to get any upgrades for Monarch.

                    • Infrequent problems
                      Grant Perkins

                      Ralph,

                       

                      Code is not my area at all but one odd thought that occurred to me was whether there was something in the data that caused the folder creation/recognition) to fail. (If my limited code reading ability has read things right.)

                       

                      Do the reports always start with the same first company? Or might there, sometimes, be something else that slips in there?

                       

                       

                      Grant