asked on

Duplicate files listed in log file.

Hi Experts,

I have the following function that processes files (downloaded from an FTP server thru a PS Script every 15 minutes), and then keeps a log of those processed files.

Public Sub CallImportDataToCaspio()
   Dim StrFile As String, strTable As String, sFileStr As String
   Dim sDir As String
   Dim l As Long, s As String, i As Long
   Dim db As Database
   Set db = CurrentDb
   sDir = "E:\AppDev\FTP\Caspio\"
   'sDir = "E:\AppDev\FTP\Caspio\"
    'StrFile = Dir(sDir & "*PatChanges*")
    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0
    
        If Not IsFileOpen(sDir & StrFile) Then
            ''        Do While IsFileOpen(sDir & StrFile)
            ''            ' do nothing
            ''        Loop
            If InStr(1, StrFile, "Full") = 0 And InStr(1, StrFile, "Part") = 0 Then
                i = CountOfRecords(sDir, StrFile)
                If i > 1 Then
                    'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
                    If InStr(1, StrFile, "PatChanges") > 0 Then
                        strTable = "Patients"
                        l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                    ElseIf InStr(1, StrFile, "SchChanges") > 0 Then
                        strTable = "Schedule"
                        l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                    ElseIf InStr(1, StrFile, "CGChanges") > 0 Then
                        strTable = "Caregivers"
                        l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                        ''                ElseIf InStr(1, StrFile, "PatMedProfileChangesV2") Then
                        ''                    strTable = "Patients_Medications"
                        ''                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                    Else
                        strTable = ""
                        l = 0
                    End If
                    If l > 0 Then
                        s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & StrFile & "'," & l & ",'Success') "
                    Else
                        If strTable = "Patients" Or strTable = "Schedule" Then
                            s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & StrFile & "'," & l & ",'Failure') "
                        End If
                    End If
                    If Len(s) > 0 Then
                        db.Execute s
                    End If
                End If
            End If

            On Error Resume Next

            Kill sDir & StrFile
            
        End If

        LogFile "E:\AppDev\Logs\UpdateCaspio.txt", StrFile & " - " & Now
        
        StrFile = Dir
        
        On Error GoTo 0
        
    Loop
    
    If StrFile = "" Then
        DoEvents
    End If

End Sub

Open in new window

I saw in the log file that for some reason, either the PS script must of stopped for about an hour and a half or the above function stopped (which keeps running constantly being called in a loop).

My question is, as you see on attached, as soon it resumed to work they were duplicate files listed in the log file, however when checking either in the backup folder or in the FTP site, they are no duplicate files.

Can you help figure out how this could of happen?!

Thanks
Untitled.png

John Tsioumpris

If you have some files duplicated then probably for some reason the method exited while logging the files..logged some..and then the method restarted (timer) and relogged all the files.

bfuchs

ASKER

Actually I realize now it has nothing to do with the fact that it stopped to work for a while, as they're duplicate files listed before and after that period as well, as you can see on attached.

So perhaps something is wrong with the logic of function above.

Can you figure out what is the culprit?

@John,

There is no timer used, just the below constantly calls it.

Public Sub ExecuteCaspioSched()
    Do
        Call CallImportDataToCaspio
    Loop
End Sub

Open in new window

Thanks,
Ben

John Tsioumpris

So the PS Script sleeps for 15 minutes and restarts...so again...you have some network hiccup...some files where logged...then the FTP was unavailable and then it came up again and logged the files again.
Your code on first glance looks good but without running it on my own i can't say if its bullet proof.
Probably you could add some logging to the methods...like " method started ...sometime" , "file processing ended at ..."...etc

Gustav Brock

Things happen, so why not just not allow dupes to be stored and handled?
It should be easy to modify your code to check for dupes.

John Tsioumpris

Something that caught my attention...
You are using Powershell to download the files from your SFTP server...i never encountered a similar case but from what i see the Powershell doesn't use some builtIN functionality or standard .NET framework .dlls, instead it goes the usual way of WinSCP..and it makes me think...why don't you integrate everything in your Ms Access application...or a small one that will act as downloader/logger and give you better control on the what and when.
A quick Google search gave some examples of Winscp controlled by Access so maybe you should take look ...my first result was : Connect to WinSCP SFTP Via Access VBA

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

Hi Experts,

While some of you posted valuable comments on this thread, however I would prefer we focus only on what is causing this issue and how to resolve it.

I just paused the Access code from running and was doing manually for a while, then continue running it, and from that point on I don't see duplicates.

See attached.

Can someone figure out the culprit?

Thanks,
Ben
Untitled.png

ste5an

No, as John already wrote, the code itself shows no signs for that.

But you need to understand that your kind of error handling does not logging in the case of errors. And the code itself lacks proper logging.

How you repair that is on you. But clean code is our friend. One principles of it is seperation of concerncs. Doing everything in a single procedure violates that. And in sum it may hide the real error.

And you need additional logging in your PowerShell script.

John Tsioumpris

It could be a number of things...a bug from WinScp...a bug from the server...a background operation that interferes.....the use of 3rd party solutions to get job done means that you are also "handing the keys"..so you don't have complete control....you probably need to monitor closer...or maybe investigate if WinScp can have some extra logging in order to start ruling out possible culprits.
If not available ..I know that .Net has a very aggressive FileWatcher class for directory monitoring...maybe this will provide the necessary info

bfuchs

ASKER

Hi Experts,

I got it fixed by placing the log routine on the inside function ImportDataToCaspio instead.

See attached how this seems to work perfectly now.

However would still accept the answer of who can offer a sensible explanation, on what was the cause of the problem and why changing that fixed the problem...

Thanks,
Ben
Untitled1.png
Untitled.png

Gustav Brock

It's probably a timing issue.
But your screenshot shows at least six dupes …

ste5an

Your code structure hides the problem, as I already wrote.

But further more, you're using the same literals more than once, this is can lead to errors due to mistyped values (use enums and a translation function or constants instead).
You're using the an almost identical if constructs in at least two places, which is normally redundant.
You're calling more than one further function, which seem to be driven by additional global variables. Use parameters. Global variables are often a bad design decision, especially as in VBA they can be erased in some error conditoins. Do they handle errors properly also?
You're interacting with form internals instead of using simple calls or parameters.

The key for debugging and finding the real cause (which we can only guess about) is a clean, understable code structure and code flow. I'm not sure how to better express it: There is a difference between working code and correct code. Clean code tends to be more often also to be correct and much more it is easier to understand.

[..] I would prefer we focus only on what is causing this issue and how to resolve it.

Because of all the above reasons, this is exactly what I did. I adressed the core problems in your code.

btw, Gustave pointed out, that I maybe an timing issues. I did not mention or explain it. But this one further reason for my sample approach of gathering all files to process first. The idea is simple: get a list of what to do in a single run, at a single point in time. Log this information at this point. Then you also now what happens on from that point.

bfuchs

ASKER

@ste5an,

So you're saying that w/o restructuring the code, its impossible to figure out what was causing the issue, nor can we know how did the above fixed it?

If RecordCount > 1 Then ' Why 1 and not 0?
If InStr(1, rs![File], "PatChanges") > 0 Then

Some files only contain field headers.

ImportDataToCaspio "Patients", rs![File], RecordCount ' Log success or failure in that method.

Didn't realized that comment, so guess if no one comes up with a better explanation, will end up accepting that...

Thanks,
Ben

ste5an

At least from my side. It's here mere guessing. But for solving such a problem you need in general:

- Proper logging in each method (this includes a separate log for your PowerShell script).
- Logging requires a) good structure to isolate the areas were the problem happens and b) proper error handling.

So in the end:

So you're saying that w/o restructuring the code, its impossible to figure out what was causing the issue [..]

Impossible, no. But much harder (np:) then necessary.

Gustav Brock

Impossible, no. But much harder (np:) then necessary.

Exactly.
Also, have in mind please, that we only have your code; no data, no duplicated scenario, so we cannot debug anything.

bfuchs

ASKER

Thanks to all participants!