Error Trapping

[aziz rasul]

I have two dbs (which are scheduled overnight) that use multiple DoCmd.TransferSpreadsheets commands.

The scheduled tasks overlap, which is unavoidable.

When db1 is using it's DoCmd.TransferSpreadsheets commands, db2 issues an error when it tries to carry out it's DoCmd.TransferSpreadsheets commands. I want to error trap db2 such that when db1 finishes, the DoCmd.TransferSpreadsheets commands for db2 can carry on. Heres the snippet of code that I'm using.

Goto1:
    Debug.Print Now & " : " & "qryTotal_IPC"
    strExcel = "qryTotal_IPC1"
    DoCmd.TransferSpreadsheet acExport, 8, "qryTotal_IPC", strCurrentpath, False, "RollingNPS"

ErrorHandler:
    If Err.Number = 0 Then
    ElseIf Err.Number = 3051 Then
        If Err.Description = "The Microsoft Jet database engine cannot open the file '\\w2k6001\data\CSDGAPP\BGB\BGB_MI_Reports\SME\ONE\OAMReport.xls'.  It is already opened exclusively by another user, or you need permission to view its data." Then
            For j = 1 To 6
                For i = 1 To 2000000000#
                Next i
            Next j
            If strExcel = "qryTotal_IPC1" Then
                GoTo Goto1
            End If
        ElseIf Err.Description = "The Microsoft Jet database engine cannot open the file '\\w2k6001\data\CSDGAPP\BGB\BGB_MI_Reports\SME\ONE\MEOAMReport_.xls'.  It is already opened exclusively by another user, or you need permission to view its data." Then
            For j = 1 To 6
                For i = 1 To 2000000000#
                Next i
            Next j
            If strExcel = "qryTotal_IPC2" Then
                GoTo Goto2
            End If
        End If
    End If

When it errors the first time, it error traps OK. But in the second time it shows up the error dialog box and doesn't go to the error handling code??? Almost as if it doesn't recognise the error trapping piece of code.

The question is why is this happening?

 

[Galaxiom]

When a error state is set, the code behaves differently until you issue a Resume command.

However I don't think trapping the error is the best way to deal with this. Ideally have databases set a flag to indicate it is busy with the file. Otherwise test that the file is open rather than using the error.

Errors should be for real errors. Usually we only routinely trap them as part of normal processing when there is no other way.

Also, putting the code through two billion cycles of a loop is not a very efficient nor elegant way to wait.

 

[aziz rasul]

What code could I use to test the file is open before performing the DoCmd.Spreadsheet command?

 

[gemma-the-husky]

the gotos in your error handler are wrong. as galaxiom says, they need to be resumes - otherwise you are still in the current event handler.

in the event hanlder you dont TEST for the description

try this sort of thing. hope you can see the difference.
also add an exit sub BEFORE you reach the error handler, so you don't have to test for no error
there is no reason for the time loop - you just ask whether to try again or abort the process

Goto1:
    Debug.Print Now & " : " & "qryTotal_IPC"
    strExcel = "qryTotal_IPC1"
    DoCmd.TransferSpreadsheet acExport, 8, "qryTotal_IPC", strCurrentpath, False, "RollingNPS"
    exit sub [COLOR="Red"] 'this stops dropping into the error handler!!!!![/COLOR]

ErrorHandler:
    If Err = 3051 Then
        If msgbox(Cannot open the file '\\w2k6001\data\CSDGAPP\BGB\BGB_MI_Reports\SME\ONE\OAMReport.xls'. " & _
            " It is already open, or you need permission to view its data. Try Again",vbyesno) = vbno  Then
            exit sub
        else 
              resume Goto1
        end if

    else
         [COLOR="Red"]'eg maybe the file was not found - thats a different error
                             ' or maybe there is some error with the docmd[/COLOR]
         msgbox("Unspecified Error" & vbcrlf & _
            "Error: " & err & "  Desc: " & err.description)
    end if

 

[aziz rasul]

The reason I'm testing the Err.Description is that I have the same query being used exported to a different spreadsheet.

Also I don't want to Exit Sub as I have other DoCmd.TransferSpreadsheet commands that follow the first one.

If I enter a resume will this not go to the next line of code whereas I want to return back to the original DoCmd.TransferSpreadsheet line? Am I missing something?

Note that the db's are scheduled to run overnight so I can't have message boxes which will effectively halt the code.

If I can have a looping code to check if the xls file is free before each DoCmd.TransferSpreadsheet line, then that should do the trick.

 

[gemma-the-husky]

I still thnik testing the err description is pointless.

if you try to open fileA, and get an error, its because you cant open fileA. Its the error number that matters.

if you want to do something different depending on the file, then test strExcel.

you use a resume exactly the same as a goto. Except that a resume enables onerror statement to reset. if you don't issue a resume you are STILL handling the error, and the onerror statement is ignored, and further errors are not trapped.


I don't quite understand how you are trying to synchronise the two processes. Why don't you get processA to create a "sentinel" text file when it finishes. Then processB can wait for the file to exist, and only then execute the process. Then yuo won't get this issue of a locked spreadsheet.

After it finishes, it can delete the sentinel file.

 

[aziz rasul]

The strExcel was introduced after an afterthought so yes I can remove the Err.Description stuff.

As far as resume is concerned can I do

    If Err.Number = 0 Then
    ElseIf Err.Number = 3051 Then
        For j = 1 To 6
            For i = 1 To 2000000000#
            Next i
        Next j
        If strExcel = "qryTotal_IPC1" Then
            Resume Next
            GoTo Goto1
        ElseIf strExcel = "qryTotal_IPC2" Then
            Resume Next
            GoTo Goto2
        End If
    End If

or can I get rid of the Goto labels altogether? In other words if the first DoCmd.Spreadsheet errors, will the Resume Next return to this point?
I can't test it right now, but will be able to do so tomorrow.

I will also look into your idea of creating a 'sentinel' file and see if that is a neater solution. I would have to do that for db1 AND db2 as db2 might be exporting to the spreadsheet and db1 is waiting for it to finish and vice versa.

 

[gemma-the-husky]

NO NO NO

Resume Next
GoTo Goto1

carries on the programme straight away at the line AFTER the one that caused the error. Not at the next line of code. Goto GOTO1 NEVER gets executed

JUST
RESUME GOTO1


resume next usage is this sort of thing

on error resume next
delete temptable  ' if the table does not exist, this line throws an error - but the resume next just casrries on and ignores it.  
.....
.....
.....
other code

 

[aziz rasul]

Ah OK, I understand now.

 

[Galaxiom]

Dave-with-the-Husky has covered the detail of much that I mentioned. As he says the best approach to the problem is the sentinel file (I called it a flag).

Even when working with the file lock test I would still suggest you get away from testing by attempting the TransferSpreadsheet task.

Google something like (file lock test VBA) and you will find a range of techniques. One way or another thay all respond to an error but the examples generally demonstrate how to handle this process in an elegant way.

It is a good practice to encapsulate the test into a separate function with an argument for the file (and path) and simply return a True or False result to the main procedure. This simplifies the main code and avoids the distraction of dealing with the "error" and separating that from any real errors with the main job.

 

[aziz rasul]

I will try that. A much better way of dealing with the problem.