Solved Accss being consistently inconsistent

[John Sh]

I know it rankles some when I say that but let me demonstrate!
I have three drives that are part of the data paths for my system.
Drive "J" is a portable hard drive.
Drive "Y" which is a mapped remote drive.
Drive "Z" Which is a mapped remote drive.
When I run the software in accdb mode access finds all three drives.
When I compile the software and the accde starts automatically, the "J" drive is found but the required folder is not.
The two mapped drives are interrogated successfully.
If I now run the accde file separately all three drives are found.
The problem is that "oFSO.FolderExists(sDrive & "Images")" doesn't find the folder in this instance

Private Sub getDrives()
'   tempvars!drive  is any removable drive.
'   tempvars!Patha is the network drive that hoalds image files and other documents.
'   tempvars!Pathb is the network drive that holds the back-end files and individual front-end files.
    Dim sDrive As Object
    For Each sDrive In oFSO.Drives
        If sDrive.DriveType = 2 And oFSO.FolderExists(sDrive & "Images") Then      ' this is the line that fails.
            TempVars!Drive = sDrive & "\"
        ElseIf sDrive.DriveType = 3 And oFSO.FolderExists(sDrive & "Images") Then
            TempVars!PathA = sDrive & "\"
        ElseIf sDrive.DriveType = 3 And oFSO.FolderExists(sDrive & "Split") Then
            TempVars!PathB = sDrive & "\"
        End If
    Next
End Sub

 

[John Sh]

It seems there is a reply to this thread but I can't see it so I will answer from the email.
oFSO is set up in a module as a public function so does not need to be Dimmed.
I will try setting drive as "Drive"
Drivetype 1 is a removable drive, I.E a floppy disc.An external drive is type 2. I fell into that trap initially..

The question remains why is the folder in "J" not recognised as described.

Edit
Dimming the sdrive as drive made no difference.

 

[arnelgp]

you have not define oFSO first:

Private Sub getDrives()
'   tempvars!drive  is any removable drive.
'   tempvars!Patha is the network drive that hoalds image files and other documents.
'   tempvars!Pathb is the network drive that holds the back-end files and individual front-end files.
    Dim sDrive As Object
    'define the object
    Dim oFSO As Object
    'instantiate the object
    Set oFSO = CreateObject("scripting.filesystemobject")
    For Each sDrive In oFSO.Drives
        If sDrive.DriveType = 2 And oFSO.FolderExists(sDrive & "\Images") Then      ' this is the line that fails.
            TempVars!Drive = sDrive & "\"
        ElseIf sDrive.DriveType = 3 And oFSO.FolderExists(sDrive & "\Images") Then
            TempVars!PathA = sDrive & "\"
        ElseIf sDrive.DriveType = 3 And oFSO.FolderExists(sDrive & "\Split") Then
            TempVars!PathB = sDrive & "\"
        End If
    Next
End Sub

 

[John Sh]

you have not define oFSO first:

Just to set the record straight, the following code is in a module and is called throughout the software without dimming.

Public Function oDB() As DAO.Database
    If pCurrentDb Is Nothing Then
        Set pCurrentDb = CurrentDb
    End If
    Set oDB = pCurrentDb
End Function

Public Sub oDB_Clear()
    Set pCurrentDb = Nothing
End Sub

Public Function oFSO() As Scripting.FileSystemObject
    If pFSO Is Nothing Then
        Set pFSO = CreateObject("Scripting.FileSystemObject")
    End If
    Set oFSO = pFSO
End Function

Public Sub oFSO_Clear()
    Set pFSO = Nothing
End Sub

 

[arnelgp]

it always create the Object, FSO and Currentdb.
you amend it to:

Option Compare Database
Option Explicit

Dim pCurrentDb As DAO.Database
Dim pFSO As Object

Public Function oDB() As DAO.Database
    If pCurrentDb Is Nothing Then

        Set pCurrentDb = CurrentDb
    End If
    Set oDB = pCurrentDb
End Function

Public Sub oDB_Clear()
    Set pCurrentDb = Nothing
End Sub

Public Function oFSO() As Scripting.FileSystemObject
    If pFSO Is Nothing Then
        Set pFSO = CreateObject("Scripting.FileSystemObject")
    End If
    Set oFSO = pFSO
End Function

Public Sub oFSO_Clear()
    Set pFSO = Nothing
End Sub

now:

Private Sub getDrives()
'   tempvars!drive  is any removable drive.
'   tempvars!Patha is the network drive that hoalds image files and other documents.
'   tempvars!Pathb is the network drive that holds the back-end files and individual front-end files.
    Dim sDrive As Object
    For Each sDrive In oFSO.Drives
        If sDrive.DriveType = 2 And oFSO.FolderExists(sDrive & "\Images") Then      ' this is the line that fails.
            TempVars!Drive = sDrive & "\"
        ElseIf sDrive.DriveType = 3 And oFSO.FolderExists(sDrive & "\Images") Then
            TempVars!PathA = sDrive & "\"
        ElseIf sDrive.DriveType = 3 And oFSO.FolderExists(sDrive & "\Split") Then
            TempVars!PathB = sDrive & "\"
        End If
    Next
End Sub

 

[John Sh]

it always create the Object, FSO and Currentdb.

[/code]

I can't remember who wrote this originally but the idea is that you don't create the object every time. You create it once and re-use it as often as you like.
oFSO and oDB behave the same. There is a slight time advantage as you only create the object on the first occasion, nor every time you want it.
While this is a talking point, it has nothing to do with the op.

 

[John Sh]

You can find the original article here. https://www.devhut.net/vba-fso-files-folders-drives-and-more/

 

[CJ_London]

the original code in the link has the oFS0 declared as a variable, much as Arnel suggests - your code does not. If you don't want a public variable, use a static one

Public Function oFS0() As Scripting.FileSystemObject
Static pFS0 as Object

    If pFS0 Is Nothing Then Set pFS0 = CreateObject("Scripting.FileSystemObject")
    Set oFS0 = pFS0

End Function

or a variation to resolve your time delay

Public Function oFS0() As Scripting.FileSystemObject
Static pFS0 as Object

    If pFS0 Is Nothing Then 
        Set pFS0 = CreateObject("Scripting.FileSystemObject")
        DoEvents
    end if

    Set oFS0 = pFS0

End Function

 

[John Sh]

the original code in the link has the oFS0 declared as a variable, much as Arnel suggests - your code does not. If you don't want a public variable, use a static one

The original code in the link has pfso set a an object and ofso = pfso. In my book they are both of type object.
The code I posted is the same but without the early / late binding option.

Many yeas ago I told my apprentices to "See what you're looking at"!.

#If FSO_EarlyBind = True Then
            Set pFSO = New FileSystemObject
        #Else
            Set pFSO = CreateObject("Scripting.FileSystemObject")
        #End If

If you don't agree with this take it up with the author.

Can we please stop the bickering and get back to my original question

 

[John Sh]

The problem was the missing "\".
For Each sDrive In oFSO.Drives returns E.G. "C:"
I was telling it to find "C:Images" instead of "C:\Images"
I also modified the code to reduce the times "If oFSO.FolderExists(sDrive & "\Images")" has to run

Private Sub getDrives()
'   tempvars!drive  is any removable drive.
'   tempvars!Patha is the network drive that hoalds image files and other documents.
'   tempvars!Pathb is the network drive that holds the back-end files and individual front-end files.
    Dim sDrive As Object
    For Each sDrive In oFSO.Drives
        If oFSO.FolderExists(sDrive & "\Images") Then
            If sDrive.DriveType = 2 Then
                TempVars!Drive = sDrive & "\"
            ElseIf sDrive.DriveType = 3 Then
                TempVars!PathA = sDrive & "\"
            End If
        End If
        If sDrive.DriveType = 3 And oFSO.FolderExists(sDrive & "Split") Then
            TempVars!PathB = sDrive & "\"
        End If
    Next
End Sub

 

[arnelgp]

The problem was the missing "\".

yes, i did mention that on the new code on post 5.

 

[John Sh]

yes, i did mention that on the new code on post 5.

Sorry, I must have missed that, I obviously didn't see what I was looking at!
Thank you for you time and input.
John

 

[DickyP]

If you read the gotchas documentation for FSO DriveType you'll find that Type = 2 is applied to portable drives that are only on the machine running the code, otherwise they are treated as Remote (Type=3). I don't know if this is having any effect in this case.

 

[John Sh]

If you read the gotchas documentation for FSO DriveType you'll find that Type = 2 is applied to portable drives that are only on the machine running the code, otherwise they are treated as Remote (Type=3). I don't know if this is having any effect in this case.

Hey DickyP.
This is exactly my situation. A portable hard disk that has a full copy of the system and the accdb copy, drivetype 2.
The two remote drives, type 3, have the split database and the accde files in separate folders.
So the portable drive goes with me all the time and is type 2 here at home and also when connected at the university.
Thanks for your comment.