Cannot read file, permission denied.

Hi Experts,

We have a script that is downloading files from FTP server every 15 minutes.

I'm writing a function that will do the following.

Loop thru all files, process them, then delete them.

Part of the function is to see if file contains some data besides of just headers.
this is the code

Function CountOfRecords(sFolderPath As String, sFile As String) As Long
Dim fso
Dim folderPath
Dim file
Dim oFile
Dim fileContent
Dim arr
Dim cnt
Dim recCnt
Set fso = CreateObject("Scripting.FileSystemObject")
'folderPath = "C:\FTP\5-18\31\" 'Change the folder path as required and remember to add a back slash in the end
        Set oFile = fso.OpenTextFile(sFolderPath & sFile)
        fileContent = oFile.ReadAll
        arr = Split(fileContent, vbCrLf)
        If UBound(arr) > 0 Then
            cnt = cnt + 1
            recCnt = recCnt + UBound(arr)
        End If
        oFile.Close
        Set oFile = Nothing
CountOfRecords = recCnt

End Function

Open in new window

However some times I get an error, permission denied.

guess when its still in middle the download process...

what is the solution?

Thanks on advance.
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
I suppose step one would be to verify that the underlying issue really is that it is still in progress.
If you wait for a few seconds and retry, does the issue go away? If so you could build in a re-try functionality, e.g. wait X seconds try again, and if you've tried more than Y times, ask user if we wants to exit, so you don't get the user stuck in infinite wait mode.
0
bfuchsAuthor Commented:
Hi,
If you wait for a few seconds and retry, does the issue go away?
Yes this is the case.
ask user if we wants to exit, so you don't get the user stuck in infinite wait mode.
No user involved, I need this to run indefinitely.
How would you modify this code to accomodate this suggestion?
     sDir = "H:\FTP\test\"
    'StrFile = Dir(sDir & "*PatChanges*")
    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0
        If InStr(1, StrFile, "Full") = 0 Then
            If CountOfRecords(sDir, StrFile) > 1 Then
                'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
                If InStr(1, StrFile, "PatChanges") > 0 Then
                    l = ProcessFile("Patients", sDir & StrFile)
                End If
                If l > 0 Then
                    s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & sTable & "','" & StrFile & "'," & l & ",'Success') "
                Else
                    s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & sTable & "','" & StrFile & "'," & l & ",'Failure') "
                End If
                db.Execute s
            End If
        End If
        On Error Resume Next
        Kill sDir & StrFile
        StrFile = Dir
        On Error GoTo 0
    Loop

Open in new window


Thanks,
Ben
0
bfuchsAuthor Commented:
Should I check for the following before continuing the loop?
https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open
Thanks,
Ben
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bfuchsAuthor Commented:
I added the following
        Do While IsFileOpen(sDir & StrFile)
            ' do nothing
        Loop

Open in new window

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Open in new window

Wondering if this is the right way to handle this?

Thanks,
Ben
0
Gustav BrockCIOCommented:
No, because it will wait for the open file to close. It will be much more efficient to ignore it and proceed with other files ready to be processed.

In the next run, the locked file may be ready. If not, in the next run, etc.
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Yes, that could be one way of doing it. There might be smarter ways, as Gustav mentions, that way should work well enough.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Actually it's worse than you think; during a download, a FTP process will not lock a file, so you can get all kinds of weird results.   Anything from "permission denied" to blank files, or partially filled ones.

Are you in control of the FTP process?   In other words are you pulling these files from someplace, or is someone pushing them to you?  Assuming it's the later.   If so, do you have control over that process?

Typically one uses a "flag" to indicate that the download/upload is complete.   A couple different ways that might happen:

1. File extension is set to something like   ".sending", and then renamed to a valid extension when the transfer is complete.

2. A "guard" file is used.  This is a variation of #1.   If the the file is being sent is abc.txt,   a guard file is first created called abc.locked.  The file is then transmitted and when complete, the .locked file is deleted.    As long as the guard file exists, you don't touch the file.

3. Different folders are used  - you send to something like \myFolder\inbox and then move the file to \myFolder\Unprocessed when the transfer is complete (you do a remote rename).   The receiver then only looks for files in the \unprocessed folder knowing that any file there is complete.

 In some way, you need a semaphore (a flag) to indicate when you can start working with the file.

Jim.
0
bfuchsAuthor Commented:
It will be much more efficient to ignore it and proceed with other files ready to be processed.
So I changed the loop for the following
    Do While Len(StrFile) > 0 And Not IsFileOpen(sDir & StrFile)

Open in new window

How does that sounds?

@Jim,
Actually the FTP process does name that file differently while its in process, therefore I suspect the logic that I have on script to copy from the download folder to the processing folder is the culprit.

Here is the script,
https://www.experts-exchange.com/questions/29112971/Script-causing-an-error-after-running-for-long-time.html?anchor=a42664409¬ificationFollowed=212064540#a42664409
Wonder how easy would be to modify that, in order to fix this issue...

Thanks,
Ben
0
Gustav BrockCIOCommented:
the FTP process does name that file differently while its in process

That changes the picture completely. If so, the file can be locked only for a split second, and you should be able to use the simple:

        Do While IsFileOpen(sDir & StrFile)
            ' do nothing
            DoEvents
        Loop

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks to all experts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.