Finding a file and displaying its full path

[mazza]

Have been searching for a long time but cannot find what I want
Using access 2000/2003

I want to search for the full path of a specific database (file) (not the current database) display the full path in a message box and use the result to relink my tables.

I have a code for the relinking using a file browser and that is not the problem. I want the users to see where the database is on its pc or server directory.

thanks

Marius

 

[ByteMyzer]

The following function will search a specified root directory, including all subfolders, and return the full path of a file given the parameters sFile (the name of the file without the path) and sRoot (the root directory to search):

Public Function GetFullPath( _
    ByVal sFile As String, _
    ByVal sRoot As String) _
    As String
    
    Dim dCol As Collection
    Dim lCol As Long
    Dim dPath As String
    Dim dFile As String
    Dim X As Long, Y As Long
    
    Set dCol = New Collection
    
    dCol.Add sRoot
    
    Do While lCol <= dCol.Count
        lCol = lCol + 1
        dPath = dCol(lCol)
        dFile = Dir(dPath & "\*", 63)
        Do While dFile > ""
            If dFile = sFile Then
                GetFullPath = dPath & "\" & dFile
                GoTo GetFullPath_Exit
            ElseIf (GetAttr(dPath & "\" & dFile) And 16) = 16 _
                And dFile <> "." _
                And dFile <> ".." Then
                    dCol.Add dPath & "\" & dFile
            End If
            dFile = Dir(, 63)
        Loop
    Loop
    
GetFullPath_Exit:
    Set dCol = Nothing
    
End Function

For example, if you have a file named MyDatabase.mdb in the directory C:\My Folder\My Databases, calling the function with:

? GetFullPath("MyDatabase.mdb", "C:")

...will return:

C:\My Folder\My Databases\MyDatabase.mdb


For another example, if you have a file named MyDatabase.mdb on a network directory \\MyServer\My Folder\My Databases, calling the function with:

? GetFullPath("MyDatabase.mdb", "\\MyServer")

...will return:

\\MyServer\My Folder\My Databases\MyDatabase.mdb


See if this solution works for you.

 

[ByteMyzer]

Whoops:

Do While lCol <= dCol.Count

...should be:

Do While lCol < dCol.Count

 

[mazza]

tks

tried your code

while executing the code I get the following error
runtime error 5
invalid procedure call or argument

the debug line highlights the GetAttr line

any ideas?



entered the function in a module

Public Function GetFullPath(ByVal sFile As String, ByVal sRoot As String) As String

Dim dCol As Collection
Dim lCol As Long
Dim dPath As String
Dim dFile As String
Dim X As Long, Y As Long


Set dCol = New Collection

dCol.Add sRoot

Do While lCol < dCol.Count
lCol = lCol + 1
dPath = dCol(lCol)
dFile = Dir(dPath & "\*", 63)
Do While dFile > ""
If dFile = sFile Then
GetFullPath = dPath & "\" & dFile
GoTo GetFullPath_Exit
ElseIf (GetAttr(dPath & "\" & dFile) And 16) = 16 And dFile <> "." And dFile <> ".." Then
dCol.Add dPath & "\" & dFile
End If
dFile = Dir(, 63)
Loop
Loop

GetFullPath_Exit:
Set dCol = Nothing

End Function


called the function in a form

Private Sub Command0_Click()
MsgBox GetFullPath("SQSAddOn.mdb", "C:")
End Sub

 

[mazza]

it seems it doesn't like some system files and falls over on the root directory.
If I enter c:\program files\ it works fine


Apart from this problem my idea was to find the file in any directory available on the server, not knowing whether it is on C or D or any drive on the server

 

[ByteMyzer]

After the line:

Public Function GetFullPath( _
    ByVal sFile As String, _
    ByVal sRoot As String) _
    As String

...enter this line:

On Error Resume Next

this should fix the error you encountered.

Apart from this problem my idea was to find the file in any directory available on the server, not knowing whether it is on C or D or any drive on the server

You still have to specify the server name. If your server name is MyServer, and the file you are looking for is MyDatabase.mdb, then:

GetFullPath("MyDatabase.mdb", "\\MyServer")

...will iterate all the server directories and sub directories and return the full path of the first occurence of MyDatabase.mdb that it finds.

 

[mazza]

thanks

it working now