By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,239 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

Restart transfertext on error

aas4mis
P: 97
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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer()
  2. Dim strSQL As String
  3.  
  4. On Error GoTo Catch:
  5. Catch:
  6.     If FileExists(strFileToCheck) = True Then
  7.         If varFileDateTime <> FileDateTime(strFileToCheck) Then
  8.             DoCmd.SetWarnings False
  9.             DoCmd.TransferText acImportDelim, "InHouseManImportSpecs", "tbl_tempManifest", "c:\inetpub\ftproot\inhouseman.txt", False
  10.             DoCmd.OpenQuery "qry_ManifestUpdateAndInsert"
  11.  
  12.             strSQL = "DELETE * FROM tbl_TempManifest"
  13.             DoCmd.RunSQL strSQL
  14.             DoCmd.SetWarnings True
  15.  
  16.             Me.txtUpdated.Value = Now()
  17.             'MsgBox "The file has been modified!"
  18.             varFileDateTime = FileDateTime(strFileToCheck)
  19.         Else
  20.         Me.txtActivity.Value = Now()
  21.         'MsgBox "The file is the same!"
  22.         End If
  23.     End If
  24.  
  25.  
  26. End Sub
Thanks!
Mar 5 '10 #1
Share this Question
Share on Google+
5 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
I have modified your code to wait 5 seconds before trying again, and trying a maxium of 5 times.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Timer() 
  2. Dim strSQL As String 
  3.  
  4. On Error GoTo Err_Handler: 
  5. Catch: 
  6.     If FileExists(strFileToCheck) = True Then 
  7.         If varFileDateTime <> FileDateTime(strFileToCheck) Then 
  8.             DoCmd.SetWarnings False 
  9.             DoCmd.TransferText acImportDelim, "InHouseManImportSpecs", "tbl_tempManifest", "c:\inetpub\ftproot\inhouseman.txt", False 
  10.             DoCmd.OpenQuery "qry_ManifestUpdateAndInsert" 
  11.  
  12.             strSQL = "DELETE * FROM tbl_TempManifest" 
  13.             DoCmd.RunSQL strSQL 
  14.             DoCmd.SetWarnings True 
  15.  
  16.             Me.txtUpdated.Value = Now() 
  17.             'MsgBox "The file has been modified!" 
  18.             varFileDateTime = FileDateTime(strFileToCheck) 
  19.         Else 
  20.         Me.txtActivity.Value = Now() 
  21.         'MsgBox "The file is the same!" 
  22.         End If 
  23.     End If 
  24.  
  25. exit sub
  26. err_Handler:
  27.   dim intErrorCount as intErrorCount
  28.  
  29.   if err.Number=3051 then
  30.     'How many times has this error occured
  31.       intErrorCount=intErrorCount+1
  32.       if intErrorCount=5 then
  33.         msgBox "The maxium amount of attempts was reached. Cannot Import"
  34.         exit Sub
  35.       End If
  36.       Sleep 5000
  37.       Resume 'Maybe you want to do GoTo Catch, im unsure, depends on how the failure effects the rest of the code
  38.  
  39.  
  40.   end if
  41.   msgbox "Error: " & err.Number & ". Description:" & err.Description
  42.  
  43. End Sub
The Sleep is a function of my own, which goes into a module, basicly it will pause for X number of milliseconds.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Declare Function GetTickCount Lib "kernel32" () As Long
  3.  
  4. Public Sub sleep(lngMilliSecs As Long)
  5.     Dim lngTick As Long
  6.     lngTick = GetTickCount
  7.  
  8.     Do While lngTick + lngMilliSecs > GetTickCount
  9.         'Nothing!!
  10.     Loop
  11.  
  12. End Sub
  13.  
Mar 5 '10 #2

aas4mis
P: 97
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.
Mar 5 '10 #3

aas4mis
P: 97
@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).

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim strFileToCheck As String
  3. Dim varFileDateTime As Variant
  4.  
  5.  
  6. Private Sub Form_Open(Cancel As Integer)
  7.  
  8.     Application.SetOption "Error Trapping", 1
  9.     strFileToCheck = "c:\inetpub\ftproot\inhouseman.txt"
  10.     varFileDateTime = FileDateTime(strFileToCheck)
  11.  
  12. End Sub
  13.  
  14.  
  15. Private Sub Form_Timer()
  16. Dim strSQL As String
  17.  
  18. On Error GoTo err_Handler:
  19.  
  20.     If FileExists(strFileToCheck) = True Then
  21.         If varFileDateTime <> FileDateTime(strFileToCheck) Then
  22.             DoCmd.SetWarnings False
  23.             DoCmd.TransferText acImportDelim, "InHouseManImportSpecs", "tbl_tempManifest", "c:\inetpub\ftproot\inhouseman.txt", False
  24.             DoCmd.OpenQuery "qry_ManifestUpdateAndInsert"
  25.  
  26.             strSQL = "DELETE * FROM tbl_TempManifest"
  27.             DoCmd.RunSQL strSQL
  28.             DoCmd.SetWarnings True
  29.  
  30.             Me.txtUpdated.Value = Now()
  31.             varFileDateTime = FileDateTime(strFileToCheck)
  32.         Else
  33.             Me.txtActivity.Value = Now()
  34.         End If
  35.     End If
  36.  
  37. Exit Sub
  38. err_Handler:
  39.  
  40.       sleep 1000
  41.       Resume
  42.  
  43. End Sub
  44.  
Thanks for the help TheSmileyOne!
Mar 5 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Mar 5 '10 #5

aas4mis
P: 97
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.
Mar 8 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.