Network Path (1 Viewer)

[Noson5434]

Hi, I'm having trouble checking if a network folder exists.
If a local folder exists then it's all good Ex: (C:\), but if I check a network folder the code takes almost 3 seconds to return False when the folder doesn't exist.

Any suggestions on how to speed up this process?
Thank you.

Public Sub Test123()
    On Error GoTo Err_Handler

    Dim blnFound As Boolean
    Dim dblStartTime As Double
    Dim dblSecondsElapsed As Double
    
    dblStartTime = Timer
    
    blnFound = IsFolder("C:\")
    
    dblSecondsElapsed = Round(Timer - dblStartTime, 2)
    
    Debug.Print "The code ran in " & dblSecondsElapsed & " seconds."

Exit_Err_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Procedure: Test123", vbCritical + vbOKOnly, "Test123 - Error"
    Resume Exit_Err_Handler
End Sub

Private Function IsFolder(ByVal strFolder As String) As Boolean
    On Error GoTo Err_Handler
    
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    IsFolder = objFSO.FolderExists(strFolder)
    
Exit_Err_Handler:
    If Not objFSO Is Nothing Then Set objFSO = Nothing
    Exit Function
    
Err_Handler:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Procedure: IsFolder", vbCritical + vbOKOnly, "IsFolder - Error"
    Resume Exit_Err_Handler
End Function

 

[Minty]

I think you are stuck with that delay as that's the amount of time Windows will take to check if the folder is accessible or not.

If you connect a network drive (let's call it x:\) then, make it unavailable, now check how long it takes windows to report that it isn't available from the file explorer. I suspect it's very similar.

 

[Noson5434]

I think you are stuck with that delay as that's the amount of time Windows will take to check if the folder is accessible or not.

If you connect a network drive (let's call it x:\) then, make it unavailable, now check how long it takes windows to report that it isn't available from the file explorer. I suspect it's very similar.

Is there a way to stop the execution of "IsFolder" after 1 second for example? and determine False?

 

[Minty]

Not really, as Access is using the windows filesystem object to get the result, and Access will wait for an answer as far as the code is concerned.
So while it's waiting Access has passed control to the FSO, and that's where the delay come from.

The only other route would be to check if the drive existed first - which I believe is quicker, but it won't guarantee the folder exists on the drive.

Function fn_validate_drive(ByVal sDrive As String) As Boolean

    Dim fso As FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")

    fn_validate_drive = fso.DriveExists(sDrive)

    Set fso = Nothing

End Function

 

[Noson5434]

Not really, as Access is using the windows filesystem object to get the result, and Access will wait for an answer as far as the code is concerned.
So while it's waiting Access has passed control to the FSO, and that's where the delay come from.

The only other route would be to check if the drive existed first - which I believe is quicker, but it won't guarantee the folder exists on the drive.

Function fn_validate_drive(ByVal sDrive As String) As Boolean

    Dim fso As FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")

    fn_validate_drive = fso.DriveExists(sDrive)

    Set fso = Nothing

End Function

Ok thank you.

 

[The_Doc_Man]

Regarding a search for a specific networked path that might not exist:

Your internal device list will include "local physical" drives and "remotely mapped" drives. If the drive exists but the folder does not, you have to search the drive for the sequence of folders in your path. The longer your path, the more search steps are required and since this is across a network, everything is slower. A complication is that for Windows, it is inevitable that the system "hosting" the device in question is the security manager for that device, so when you ask to examine the device, FIRST you have to get permission to take that peek. The formal term is "access arbitration" and it involves some network data exchanges. Remember that what is in memory is available in a few nanoseconds (physical memory bus speed) but what is available on the network is available in microseconds ... or milliseconds - multiple orders of magnitude slower, depending on network speed. That is why a local file search is much faster.

If you wanted remote path Z:\Y\X\W\V\U\T\, you first would look for drive Z, then for folder Y, then for folder X, etc. And if it was folder T that didn't exist, you would have had to look through 5 layers of folders before you finally failed. And in that sequence, access arbitration occurs for every path step. That your search only took a couple of seconds is good. I've seen cases where old, slow networks took as many as 10 or 15 seconds depending on the number of hops and number of folders in the path. (Granted, this WAS a geographically large area with many hops involved, so it was an extreme case.)

In general there is no way to stop the execution of a function or subroutine in Access because Access is single-threaded. In order to stop execution, you would need a second execution thread that wasn't blocked by the function still executing, and that just doesn't happen for Access without a lot of WinAPI calls that get complicated really fast. Or you could declare a timer event. It would not help because the code interrupted by the timer would still be in the same state as it was before the timer interrupt - and worse, your timer interrupt probably also blocked the continued execution of that function you wanted to accelerate. The timer code would therefore be more like a speed bump than an express lane.

In general, Access takes what it takes. Minty's suggestion - look for the drive first and move on to the path only if possible - is an example of optimizing the code to consider possible short-cut outcomes ahead of time. The only way to speed code execution is to make those intelligent choices ahead of time to optimize the decision/flow process. Since Access is NOT an open-sourced program, we have no way to know how to make it faster internally.

 

[cheekybuddha]

Have you tried the more traditional test to see if a folder/file exists?

Function FolderExists(Path) As Boolean

  FolderExists = Len(Dir(Path, vbDirectory)) > 0

End Function

It returns pretty instantaneously for me:

?FolderExists("C:")
True
?FolderExists("E:")
True
?FolderExists("G:")
False
?FolderExists(CurrentProject.Path)
True
?FolderExists(CurrentProject.Path & "\dummy\")
False

 

[Noson5434]

Regarding a search for a specific networked path that might not exist:

Your internal device list will include "local physical" drives and "remotely mapped" drives. If the drive exists but the folder does not, you have to search the drive for the sequence of folders in your path. The longer your path, the more search steps are required and since this is across a network, everything is slower. A complication is that for Windows, it is inevitable that the system "hosting" the device in question is the security manager for that device, so when you ask to examine the device, FIRST you have to get permission to take that peek. The formal term is "access arbitration" and it involves some network data exchanges. Remember that what is in memory is available in a few nanoseconds (physical memory bus speed) but what is available on the network is available in microseconds ... or milliseconds - multiple orders of magnitude slower, depending on network speed. That is why a local file search is much faster.

If you wanted remote path Z:\Y\X\W\V\U\T\, you first would look for drive Z, then for folder Y, then for folder X, etc. And if it was folder T that didn't exist, you would have had to look through 5 layers of folders before you finally failed. And in that sequence, access arbitration occurs for every path step. That your search only took a couple of seconds is good. I've seen cases where old, slow networks took as many as 10 or 15 seconds depending on the number of hops and number of folders in the path. (Granted, this WAS a geographically large area with many hops involved, so it was an extreme case.)

In general there is no way to stop the execution of a function or subroutine in Access because Access is single-threaded. In order to stop execution, you would need a second execution thread that wasn't blocked by the function still executing, and that just doesn't happen for Access without a lot of WinAPI calls that get complicated really fast. Or you could declare a timer event. It would not help because the code interrupted by the timer would still be in the same state as it was before the timer interrupt - and worse, your timer interrupt probably also blocked the continued execution of that function you wanted to accelerate. The timer code would therefore be more like a speed bump than an express lane.

In general, Access takes what it takes. Minty's suggestion - look for the drive first and move on to the path only if possible - is an example of optimizing the code to consider possible short-cut outcomes ahead of time. The only way to speed code execution is to make those intelligent choices ahead of time to optimize the decision/flow process. Since Access is NOT an open-sourced program, we have no way to know how to make it faster internally.

The users don't actually have the path as a mapped network drive so it won't really work.

 

[Noson5434]

Have you tried the more traditional test to see if a folder/file exists?

Function FolderExists(Path) As Boolean

  FolderExists = Len(Dir(Path, vbDirectory)) > 0

End Function

It returns pretty instantaneously for me:

?FolderExists("C:")
True
?FolderExists("E:")
True
?FolderExists("G:")
False
?FolderExists(CurrentProject.Path)
True
?FolderExists(CurrentProject.Path & "\dummy\")
False

Same issue

 

[The_Doc_Man]

The users don't actually have the path as a mapped network drive so it won't really work.

That "not mapped as a network drive" situation amplifies the problem in that if you have a domain setup, you now have to first interrogate either the domain controller or the in-house router to find the system name to establish a route via ARP (address routing protocol), after which the rest of the path search occurs as I suggested.

IF the system name is legit because they got the reference from something else in the database, you are guaranteed to go through some access arbitration activity. To be honest, even a mapped drive letter doesn't help THAT much since the first thing that a drive letter mapping does is it gets substituted with the formal system name and path, something like \\system\drivetag\path ... typical UNC format.