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.transfert ext. 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.transfert ext.

Here's my failed attempt. (FileExists() available at http://allenbrowne.com/func-11.html)
[CODE=vba]
Private Sub Form_Timer()
Dim strSQL As String

On Error GoTo Catch:
Catch:
If FileExists(strF ileToCheck) = True Then
If varFileDateTime <> FileDateTime(st rFileToCheck) Then
DoCmd.SetWarnin gs False
DoCmd.TransferT ext acImportDelim, "InHouseManImpo rtSpecs", "tbl_tempManife st", "c:\inetpub\ftp root\inhouseman .txt", False
DoCmd.OpenQuery "qry_ManifestUp dateAndInsert"

strSQL = "DELETE * FROM tbl_TempManifes t"
DoCmd.RunSQL strSQL
DoCmd.SetWarnin gs True

Me.txtUpdated.V alue = Now()
'MsgBox "The file has been modified!"
varFileDateTime = FileDateTime(st rFileToCheck)
Else
Me.txtActivity. Value = Now()
'MsgBox "The file is the same!"
End If
End If


End Sub[/CODE]

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.transfert ext line.

[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.transfert ext line.

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).

[CODE=vba]Option Compare Database
Dim strFileToCheck As String
Dim varFileDateTime As Variant


Private Sub Form_Open(Cance l As Integer)

Application.Set Option "Error Trapping", 1
strFileToCheck = "c:\inetpub\ftp root\inhouseman .txt"
varFileDateTime = FileDateTime(st rFileToCheck)

End Sub


Private Sub Form_Timer()
Dim strSQL As String

On Error GoTo err_Handler:

If FileExists(strF ileToCheck) = True Then
If varFileDateTime <> FileDateTime(st rFileToCheck) Then
DoCmd.SetWarnin gs False
DoCmd.TransferT ext acImportDelim, "InHouseManImpo rtSpecs", "tbl_tempManife st", "c:\inetpub\ftp root\inhouseman .txt", False
DoCmd.OpenQuery "qry_ManifestUp dateAndInsert"

strSQL = "DELETE * FROM tbl_TempManifes t"
DoCmd.RunSQL strSQL
DoCmd.SetWarnin gs True

Me.txtUpdated.V alue = Now()
varFileDateTime = FileDateTime(st rFileToCheck)
Else
Me.txtActivity. Value = Now()
End If
End If

Exit Sub
err_Handler:

sleep 1000
Resume

End Sub
[/CODE]

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.