ACC2000:Sample Procedure to Find the Location or Locations of a File

ACC2000: Sample Procedure to Find the Location or Locations of a File

The information in this article applies to:

Microsoft Access 2000
Microsoft Visual Basic for Applications

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SUMMARY

This article describes how to create a sample user-defined function that uses the FileSearch object contained in the Application object to locate a file on a particular drive.

MORE INFORMATION

The LocateFile() function takes the file that you are looking for as a string argument and returns all locations of that file. To create this function, follow these steps:

Start Microsoft Access, and then create a new database.
Create a module, and then type the following line in the Declarations section if it is not already there:

Option Explicit

Type the following procedure in the module sheet:

Function LocateFile(strFileName as String)

Dim vItem As Variant

With Application.FileSearch

.FileName = strFileName

.LookIn = "C:\"

.SearchSubFolders = True

.Execute

For Each vItem In .FoundFiles

Debug.Print vItem

Next vItem

End With

End Function

To test this function, type the following line in the Immediate window, and then press ENTER:

?LocateFile("Northwind.mdb")

Note that all locations of Northwind.mdb are printed in the Immediate window.