MS Access VBA How to detect a Text File Been Import is Not Locked

<Access VBA code fails>
what is the error number?

create an error handling routine, use the error code to set the loop.

Ray, thanks for your contribution. The error is 3709. But I would like to detect the state of the file since I run some other code that builds tables, run queries etc. before this import, is there a way to detect the state of the text file from Access   when the Unix system is updating this file?

A few thoughts.

1.

One thought is why not just stagger the two scheduled jobs?  Let the data pump run at say 0 and 30 minutes after the hour, and then let the Access import run at 15 and 45 minutes after the hour.  This should prevent contention for the file.

2.

Another possibility could be to check the file's length. Often the data pump will hold the file open while it writes to it, and the length will be 0 bytes during that time.  Then when it is closed the file is flushed to disk, and the length becomes accurate.  This may work or may not, you would have to do some testing, since the data pump may manipulate the file in a way that does not follow this pattern

3.

Another approach could be to check for the process or scheduled job that is the data pump, and if it is running delay and check again until it ends.

4.

As mentioned, you can just wrap the import statement that throws an error in a "ON ERROR RESUME NEXT" and "ON ERROR GOTO 0" section.  Then after the import statement you can use the ERR object to check for errors on the import statement and loop.  Some info on this at http://www.cpearson.com/excel/errorhandling.htm and https://msdn.microsoft.com/en-us/library/ka13cy19%28v=vs.90%29.aspx~bp

Thanks Bill, I like the suggestions 2 and 3 especially 2 but I need to know what the real behavior of this text file is when the ERP system is doing the data dump. How can I know exactly how this process goes when this file it’s been update? The system dumping this file is Baan IV. Can somebody with Baan scheduled Job experience let me know how does the text file behaves when updating? Does the file shows a 0 bytes size while the process is happening?

The easiest way to check that might be to just monitor the size of the file from a DOS command line while the pump is running.  Keep doing a DIR on the file while the job is running and observe what it reports for the file size.  It will likely either stay at 0 until the pump finishes or it will increase in size while it runs.

~bp

Also, would it be possible for you to share the import code?  The error you are getting isn't what I would typically expect for a locked input text file, although errors can sometime be a little misleading.

~bp

My .02 (may or may not be worth it<g>)

1. As Rey suggested and bill commented on, use an error trap and re-try

2. As Scott said, use a "guard flag".   This technique is used a lot with FTP and MFT's (Managed File Tansfers) because a file doesn't get locked during a send/receive.  So it's entirely possible to pickup part of a file and other then using a guard/flag file, there's no way to determine if the file is complete or not.

  A variation of this method is to write the file with one extension (which the remote end doesn't process), then when the send is complete, you rename the file (to something the remote end will process).

 Personally, I would go with #2 if at all possible.  It's always better to be explicit in doing anything and not leave something up to chance.  The other methods might not always work in some situations.

 For example, as Bill  pointed out, if the ERP system writes part of the file, then comes back to it, you will have a brief amount of time where the file is not locked and you might pick it up.

 Overall, I've found option #2 to be the best for any situation and all the MFT products out there do some variation of this to ensure valid transfers.  Downside of course is that you need control over the sending/exporting end and that's not always possible.

HTH,
Jim.

Sorry, for the delayed answer, family needed attention.
 Bill this is a simplified version of the Macro I have running. The error is 3011 seems like the updating the file the text file is not visible in the network directory.

Private Sub Form_Load(Cancel As Integer)

    On Error GoTo errsub

    Me.Detail.BackColor = RGB(226, 167, 165)
    Me.TimerInterval = 0
    
    pause (1)
    Me.import_LBL.Caption = "Running Import Table1..." & Format(Now(), "ddd hh:mm AMPM")
    Me.Repaint
    If FileExists("\\networkLocation\DB_Table1.accdb") Then
        If DCount("[name]", "msysobjects", "[name]='Table1A'") >= 1 Then
            DoCmd.Rename "Table1", acTable, "Table1A"
        End If
     End If
     
     DoCmd.RunSavedImportExport "Import-Build_Table1"
     DoCmd.SetWarnings = False
     DoCmd.OpenQuery "makeTable1_Q"
     DoCmd.SetWarnings = True

    pause (1)
    Me.import_LBL.Caption = "Running Import Table2..." & Format(Now(), "ddd hh:mm AMPM")
    Me.Repaint
    If FileExists("\\network_FTP_Location\TextFile_Table2.txt") Then
        If DCount("[name]", "msysobjects", "[name]='Table2A'") >= 1 Then
            DoCmd.Rename "Table2", acTable, "Table2A"
        End If
    End If
    'Text File Import to make Table2
     DoCmd.RunSavedImportExport "Import-Build_Table2" '<-This is the Text file
                                                       'import that breaks code
                                                       'when data dump has the
                                                       'text file open

    pause (1)
    Me.import_LBL.Caption = "Waiting to run X process..." & Format(Now(), "ddd hh:mm AMPM")
    Me.Repaint

    Me.TimerInterval = 480000
    Me.Detail.BackColor = RGB(230, 237, 215)

exitsub:
    Exit Sub
errsub:
    Select Case Err.Number
    Case 3011
        MsgBox "Baan data not available right now. Run manual Bann Import from data input Form", vbOKOnly
    Case 3044:
            If DCount("[name]", "msysobjects", "[name]='Table1'") >= 1 Then
                DoCmd.Rename "Table1A", acTable, "Table1"
            End If
            If DCount("[name]", "msysobjects", "[name]='Table2'") >= 1 Then
                DoCmd.Rename "Table2A", acTable, "Table2"
            End If
    Case Else
        MsgBox "There was an error in the Application. Please notify administrator of the following error: Error number." & Err.Number & " " & Err.Description, vbOKOnly, "Please write this error number down and note what you were doing at the time this error happened"
    End Select
    Resume exitsub
End Sub

Select allOpen in new window

Scott thanks for your input, can you elaborate a little bit more on the idea you are suggesting? I don’t follow you when you said “write to a textfile in a directory, or a registry setting, when they're complete” the way is set up now is that a Form in Access wakes up (timer) and run macro to import Text File as Table the issue is when importing if Text File in FTP is been update by Baan Job then code breaks, what do you mean “so one action could query that file or registry setting. If ActionA is marked as "InProgress", for example, then ActionB would not fire.”?

Jim, thank for your time..when you said rename the Text File to another extension you mean from .txt to may be .csv and copy in same network directory and import it when Baan finishes the other updating process, please can you give me a Pseudo code example step to understanding your idea better.

Got it! beautiful, 100% understood, collaboration/speak up to the Baan Team to help with issue, any of the 3 methods will work for me.

Thanks for sharing the your experience and knowledge!