Error Handler Problem

[chizzy42]

Hi,
My aim is to import text files from a server and put them in an access database, the code I have works for this task, but occasionally there are file read and write errors due to the text files being accessed at the time they’re being written by a labview app. This results in a message box appearing and the code stopping updating, this has to run 24/7 why im im trying to make it error free. I’ve added an error handler to detect an error to prevent the hang up and to email me when this occurs. My issue is that when the error occurs and is flagged the program sends out an email every time the code runs , it’s on a timer so runs every few minutes.

Could someone check the code please and tell me how to have the email happen only once….im assuming that the error is somehow trapped in the code and that is why it is getting sent out every refresh.

(i) Is there a way to flush the error so when the code runs again , there are no errors trapped.

(ii) Ideally I’d like to see what DoCmd.TransferText block is causing the error( there could be around 40 DoCmd.TransferText blocks)..should I have an error handler around every DoCmd.TransferText block I have to identify where the error originates or is there another way to achieve this.

Any other feedback on the code would be appreciated to.

Thanks in advance

ian

Private Sub Form_Timer()
 
On Error GoTo Import_Err
 
Dim DateStringrockD As String
DoCmd.SetWarnings False
DateStringrockD = (Format(Day(Now()) + 1, "dd") & "D" & Format(Month(Now()) + 1, "dd") & Format(Now(), "yy"))
 
 
If Dir("T:\HOS UK07\Test data\siskin\siskinRF\" & DateStringsisN & ".txt") <> "" Then
DoCmd.TransferText acImportDelim, "FCS", "tblFCS", "T:\HOS UK07\Test data\siskin\siskinRF\" & DateStringsisN & ".txt"
End If
 
Dim DateStringsisD As String
DoCmd.SetWarnings False
DateStringsisD = (Format(Day(Now()) + 1, "dd") & "D" & Format(Month(Now()) + 1, "dd") & Format(Now(), "yy"))
 
If Dir("T:\HOS UK07\Test data\siskin\siskinRF\" & DateStringsisD & ".txt") <> "" Then
DoCmd.TransferText acImportDelim, "FCS", "tblFCS", "T:\HOS UK07\Test data\siskin\siskinRF\" & DateStringsisD & ".txt"
End If
 
'''''''''''''''ERROR HANDLING'''''''''''''''''''''''''''''
 
Import_Exit:
  Exit Sub
Import_Err:
 
Dim appOutLook As Object
    Dim MailOutLook As Object
             'assign our object references
    Set appOutLook = CreateObject("Outlook.Application")
                   Set MailOutLook = appOutLook.CreateItem(0)
 
    With MailOutLook
        'set the recipient list
        .To = "emailaddress"
                        'set the subject
        .Subject = "Trap Error"
                        'set the body text
        .Body = "A Trap Error has occured"
                        'send the email
        .Send
    End With
                         'get rid of object references
    Set appOutLook = Nothing
    Set MailOutLook = Nothing
 
     Resume Import_Exit
 
                   End Sub

 

[gemma-the-husky]

is the text file being added to continually, while you are trying to use it? Is that what you are saying?

 

[JHB]

...I’ve added an error handler to detect an error to prevent the hang up and to email me when this occurs. My issue is that when the error occurs and is flagged the program sends out an email every time the code runs , it’s on a timer so runs every few minutes.

You could add some boolean variable and set it when an error occur, then check if the boolean variable is set or not, before sending out the E-mail. And when the code runs to the end without any error, set it back to default.

 

[chizzy42]

Hi Dave, yes the text files are being written to continually as they are updated evertime a unit passes, so should be every couple of minutes. The vba code runs pulls the text file in every three minutes and every so ofen an error occurs.

thanks

ian

 

[chizzy42]

Hi JHB,

id thought about the boolean angle, but i had wanted to try and flush the error to prevent it being flagged again(my thinking is if it keeps on flagging an error there's a problem that hasnt reset). My other concern is that the error mail doesnt indicate what text file has errored, which is why i was thinking of adding an error handler for each text file

thanks for the reply