Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a situation where two computers (A and B) are on a LAN and access a shared folder. This is all working fine. Our Access app is interfacing with a POS system that writes a text file in a particular folder. We pick up this file and process it. 99.9% of the time this works without a problem.

    Occasionally, we think we have a situation where we are trying to open the text file before it is fully closed by the POS app. Our code goes something like (pseudo code follows):

    findfile = dir(full-path-name)
    if findfile <> "" then
    sleep (2000)
    open text file
    process text file
    end if

    The sleep command was put in specifically to allow sufficient time for the file to be closed by the other app before we open it. We think, however, that we need a more foolproof way of detecting of the file is fully closed (or, if as we suspect, it only has an entry in the Windows FAT).

    Can anyone suggest some VBA code to detect if a text file is really closed?

    Thanks.
    Carol W.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You might try opening the text file for output first. As long as the POS application is writing the file, that should not be possible, so you should get an error that you can intercept using an error handler.
    If you succeed, close the file, then reopen it in the mode you want.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,

    Thanks for the reply.

    I had thought of this solution but we have some concerns about opening "their" file for output, even with error checking.

    What about the idea of checking the file size using the LOF function after we open it for input? The text files are all 1 KB in size. So, if a zero is returned from the LOF function, do you think that would show that the file isn't yet closed by the POS app?
    Carol W.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Whether the file size will be 0 depends on the way the POS application writes the file - in one chunk or in smaller steps. But it's worth a try.

    You can check the file size before opening the file, by using the FileLen function:

    Code:
    Dim strFile As String
    Dim lngFileSize As Long
    strFile = "..."
    Do
      DoEvents
      lngFileSize = FileLen(strFile)
    Loop Until lngFileSize > 0
    Open strFile For Input As #1
    ...

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Carol you could check if it is open using code similar to this:

    Code:
    Sub TestIfOpen()
     Dim strFileName As String
     
     strFileName = "C:\test.txt"
     
     If Not FileLocked(strFileName) Then
     	' If the function returns False, open the document.
     	Open strFileName For Random As #1
     End If
     'Do some stuff here otherwise
     'Close
     
    End Sub
    in conjuction with

    Code:
    Function FileLocked(strFileName As String) As Boolean
     On Error Resume Next
     
     Open strFileName For Binary Access Read Write Lock Read Write As #1
     Close #1
     
     If Err.Number <> 0 Then
     	
     	MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
     	FileLocked = True
     	Err.Clear
     End If
    End Function
    Jerry

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, Jerry.

    I'll give it a try!
    Carol W.

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    I had a similar issue and found the best method was to rename the file after writing had completed. This only works properly if the writing application performs the rename.

    cheers, Paul

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to all for your replies.

    Upon reconsideration, I have decided to wait until I can speak with the POS app developers (or as close as I can get to them) before I make any changes in my app. Unfortunately, they are on "break" this week so I'll have to wait until next week to get further insight into how they write their file.

    Happy New Year to all!
    Carol W.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •