Restart transfertext on error

[aas4mis]

I have a loop that constantly checks a text file for modifications. On mod the data will be imported to a table via docmd.transfertext. The problem is if another user (ftpuser) is uploading at the same time I get a 3051 error.
Is there a way to fail on error with no warning and restart the loop? This would be much easier if there was a way to get a return value out of docmd.transfertext.

Here's my failed attempt. (FileExists() available at http://allenbrowne.com/func-11.html)

Private Sub Form_Timer()
Dim strSQL As String
 
On Error GoTo Catch:
Catch:
    If FileExists(strFileToCheck) = True Then
        If varFileDateTime <> FileDateTime(strFileToCheck) Then
            DoCmd.SetWarnings False
            DoCmd.TransferText acImportDelim, "InHouseManImportSpecs", "tbl_tempManifest", "c:\inetpub\ftproot\inhouseman.txt", False
            DoCmd.OpenQuery "qry_ManifestUpdateAndInsert"
 
            strSQL = "DELETE * FROM tbl_TempManifest"
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
 
            Me.txtUpdated.Value = Now()
            'MsgBox "The file has been modified!"
            varFileDateTime = FileDateTime(strFileToCheck)
        Else
        Me.txtActivity.Value = Now()
        'MsgBox "The file is the same!"
        End If
    End If
 
 
End Sub

Thanks!

[TheSmileyCoder]

I have modified your code to wait 5 seconds before trying again, and trying a maxium of 5 times.

Private Sub Form_Timer() 
Dim strSQL As String 
 
On Error GoTo Err_Handler: 
Catch: 
    If FileExists(strFileToCheck) = True Then 
        If varFileDateTime <> FileDateTime(strFileToCheck) Then 
            DoCmd.SetWarnings False 
            DoCmd.TransferText acImportDelim, "InHouseManImportSpecs", "tbl_tempManifest", "c:\inetpub\ftproot\inhouseman.txt", False 
            DoCmd.OpenQuery "qry_ManifestUpdateAndInsert" 
 
            strSQL = "DELETE * FROM tbl_TempManifest" 
            DoCmd.RunSQL strSQL 
            DoCmd.SetWarnings True 
 
            Me.txtUpdated.Value = Now() 
            'MsgBox "The file has been modified!" 
            varFileDateTime = FileDateTime(strFileToCheck) 
        Else 
        Me.txtActivity.Value = Now() 
        'MsgBox "The file is the same!" 
        End If 
    End If 
 
exit sub
err_Handler:
  dim intErrorCount as intErrorCount
 
  if err.Number=3051 then
    'How many times has this error occured
      intErrorCount=intErrorCount+1
      if intErrorCount=5 then
        msgBox "The maxium amount of attempts was reached. Cannot Import"
        exit Sub
      End If
      Sleep 5000
      Resume 'Maybe you want to do GoTo Catch, im unsure, depends on how the failure effects the rest of the code
 
 
  end if
  msgbox "Error: " & err.Number & ". Description:" & err.Description
 
End Sub

The Sleep is a function of my own, which goes into a module, basicly it will pause for X number of milliseconds.

Option Compare Database
Private Declare Function GetTickCount Lib "kernel32" () As Long
 
Public Sub sleep(lngMilliSecs As Long)
    Dim lngTick As Long
    lngTick = GetTickCount
 
    Do While lngTick + lngMilliSecs > GetTickCount
        'Nothing!!
    Loop
 
End Sub
 

[aas4mis]

I still get the 'Run-time error '3051':' popup which stops the program. I won't be monitoring this so I don't want that popup to show.. Any way to disable the popup?

When entering debug mode, the cursor is on the docmd.transfertext line.

[aas4mis]

@aas4mis
My bad, I've been looking at the same code for too long. There was a msgbox in the error handler. I've modified to just sleep 1 second and retry, no messages. This seems to have done the trick. Here's the full code for the form (minus the modules noted in earlier posts).

Option Compare Database
Dim strFileToCheck As String
Dim varFileDateTime As Variant
 
 
Private Sub Form_Open(Cancel As Integer)
 
    Application.SetOption "Error Trapping", 1
    strFileToCheck = "c:\inetpub\ftproot\inhouseman.txt"
    varFileDateTime = FileDateTime(strFileToCheck)
 
End Sub
 
 
Private Sub Form_Timer()
Dim strSQL As String
 
On Error GoTo err_Handler:
 
    If FileExists(strFileToCheck) = True Then
        If varFileDateTime <> FileDateTime(strFileToCheck) Then
            DoCmd.SetWarnings False
            DoCmd.TransferText acImportDelim, "InHouseManImportSpecs", "tbl_tempManifest", "c:\inetpub\ftproot\inhouseman.txt", False
            DoCmd.OpenQuery "qry_ManifestUpdateAndInsert"
 
            strSQL = "DELETE * FROM tbl_TempManifest"
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
 
            Me.txtUpdated.Value = Now()
            varFileDateTime = FileDateTime(strFileToCheck)
        Else
            Me.txtActivity.Value = Now()
        End If
    End If
 
Exit Sub
err_Handler:
 
      sleep 1000
      Resume
 
End Sub
 

Thanks for the help TheSmileyOne!

[TheSmileyCoder]

If the error number is 3051, the error message should never be shown with the code I provided.

The msgbox and counter was there to allow a maxium amount of attempts before "timing out" or whatever, so you dont go into a infinite loop (which you could probably Break your way out of but still.

If your code works for you, thats good! I just dont really see how the msgbox's would fire unless it fails 5 times in a row, or the err.number is not 3051.

[aas4mis]

I probably stress tested it a little too much and reached the 5 count limit. But that's ok for my purposes, this will not be monitored so I don't need notifications. I didn't do a debug.print on the intErrorCount so I couldn't tell you if the limit was reached or not, but your code worked as planned, just not my plan. :)

side note:
for you copy and pasters out there (nothing wrong with that), the "dim intErrorCount as intErrorCount" in earlier posts should be dim'd as integer.

Thanks again TheSmileyOne, much time saved.