It may be that we have built a DTS package to synchronise data held in an Access database and our SQL Server. The Access databases all have a consistent name but due to the fact that they are used by travelling salesmen on their laptops we cannot guarantee where the database will be.

For this we need to loop through every file in every folder on each drive looking for the file. Once we find it then we can set the Access connection in the package to it's location.

Option Explicit

Public oFSO
Public oFolder
Public oPkg

Function Main()

	' Create a reference to the FileSystemObject
	Set oFSO = CreateObject("Scripting.FileSystemObject")

	' Set the variable that determines if we have found our file to False (0)
	DTSGlobalVariables("gv_Found").Value = 0

	' Call the function to enumerate through the drives
	GetMyDrives

	' Clean Up
	Set oFSO = Nothing
	Set oPkg = nothing

	Main = DTSTaskExecResult_Success
End Function


Function GetMyDrives()
	Dim oDrive

	' As long as we haven't found our file then we continue to loop
	' through the drives. We then call the	function that will process the folders
	For Each oDrive In oFSO.Drives
		If DTSGlobalVariables("gv_Found").Value = 0 Then
	    		If oDrive.IsReady = True Then
	       			 GetMyFolders oDrive.DriveLetter & ":\"
			End If
		Else
			Exit Function
		End If
	Next

	Set oDrive = Nothing
End Function

Function GetMyFolders(str_Folder)
	On Error Resume Next

	' In this function we are going to look in every folder calling
	' our FindMyFile function every time we get to another folder

	Dim oSubFolder
	Set oFolder = oFSO.GetFolder(str_Folder)
	FindMyFile oFolder.Path

	For Each oSubFolder In oFolder.SubFolders
		If DTSGlobalVariables("gv_Found").Value = 0 Then
	       		FindMyFile oSubFolder.Path
        		GetMyFolders oSubFolder.Path
		Else
			Exit Function
		End If
	Next

	Set oSubFolder = Nothing
End function


Function FindMyFile(str_Folder)

	'Here is where we check inside the folder for the file
	'if it exists then we set our Access Database connection path
	'to it's location and set the GlobalVariable to indicate we have found the file
	'so we can stop processing

	Dim oConn
	Dim sFilename
	sFilename = str_Folder & "\" & DTSGlobalVariables("gv_FileToFind").Value

	If oFSO.FileExists(sFilename) = True Then


		If Right( str_Folder ,1 ) <> "\" then
			str_Folder = str_Folder & "\"	
		End If
	
		DTSGlobalVariables("gv_FoundFile").Value = str_folder & _
			DTSGlobalVariables("gv_FileToFind").Value

		Set oPkg = DTSGlobalVariables.Parent
		Set oConn = oPkg.Connections("Microsoft Access")
	
		' Update connection with filename found
		oConn.DataSource = DTSGlobalVariables("gv_FoundFile").Value	
	
		' Set Found variable to True
		DTSGlobalVariables("gv_Found").Value = -1
		
		Exit Function
	End If

	Set oConn = Nothing
End Function