[AccessD] OT - file search results save - Thanks solved

ROTFL!  Sorry, completely forgot about that!  I normalized the path
structure that way, because it was much smaller to represent everything with
it's own ID.

Put the following function into that db...

Function GetFullPath(intRootID As Long, strDriveLetter As String,
strCurrentFolder As String) As String
Dim rs As Recordset
Dim intRoot As Long
Dim strPath As String
Dim strSQL As String
Dim strFolderName As String
intRoot = intRootID
If intRoot = 0 Then
    strFolderName = ""
Else
    strFolderName = strCurrentFolder & "\"
End If
Do Until intRoot = 0
    strSQL = "SELECT FolderName, RootFolder FROM tblFolders WHERE
FolderKey=" & intRoot
    Set rs = CurrentDb.OpenRecordset(strSQL)
    intRoot = rs.Fields(1).Value
    If intRoot = 0 Then Exit Do
    strPath = strPath & rs.Fields(0).Value & "\"
    rs.Close
Loop
GetFullPath = strDriveLetter & ":\" & strFolderName & strPath
End Function

Then use this query...the last field will be the full path of the file.

SELECT tblFiles.FileName, tblFiles.FileSize,
GetFullPath([tblFolders].[RootFolder],[DriveLetter],[FolderName]) AS Path
FROM tblFiles INNER JOIN (tblFolders INNER JOIN tblDriveHeader ON
tblFolders.DriveKey = tblDriveHeader.DriveKey) ON tblFiles.RootFolder =
tblFolders.FolderKey;


Drew
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of O'Connor,
Patricia 
Sent: Thursday, February 19, 2004 3:29 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] OT - file search results save - Thanks solved



Thanks for all the suggestions

Mark Matte sent me a db that gave me what I needed. It searched out and
enter into a table. all the directories in the drive. Then searched for the
MDB's and put the filename, size, time, and full path into another table. I
was able to create a nicely formatted report with the full path name, file
name, size, and date to send to our unit. I tweaked the forms to allow me to
search for all files or just A specific file extension.

Drew's Drive Mapper was another good utility. The only problem I had with it
was getting the full file path to print correctly. If there were several
levels of subdirectories I had to try to loop through to rebuild the full
path. I just really didn't have time to play with the code needed to do
this.

The karenware powertool was ok but with the db I was able to get a better
looking report.


Thanks again
Patti

 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com