asked on

In Access create a Table containing the contents of a file folder on my C: Drive

In Access, trying to create a table that displays the contents of a file folder on my C: Drive. Example: I have a folder (C:\Main\Pictures). This folder contains many sub-folders containing hundreds of files. I would like to create an Access table that lists all sub-folders and filenames contained in (C:\Main\Pictures). I know how to create a Command Button in Access so that in the OnClick event code I can launch Explorer.Exe. That displays the content, but does not achieve creating the Access Table. That's my objective. Is there someone experienced with this?

Eirman

Rather than trying to do it within Access, I suggest that you use an external application to create a text listing of the files/folders and then import them into access.

Here's one example of a file lister ...
http://www.tucows.com/preview/1587776/File-amp-Folder-Lister

This might be easier to import from as it can export in excel format
http://www.softpedia.com/get/System/File-Management/File-Listing-Maker.shtml

This one exports in CSV format which is perfect for importing into excel
http://list-maker.software.informer.com/

clock1

ASKER

Like that I can make your suggestion work; however, seeking a solution that works within Access. Going to give a bit more time to see whether someone has a solution for accomplishing this within Access. If not, glad to award your suggestion the points!

Eirman

CORRECTION:

This one exports in CSV format which is perfect for importing into ACCESS
http://list-maker.software.informer.com/

ASKER CERTIFIED SOLUTION

Jim P.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clock1

ASKER

Spot-on with an Access solution. Your points.

IrogSinta

Another way would be to use a recursive procedure to list the folder and subfolder contents. Here's a modification of some code from this site:
http://www.ammara.com/access_image_faq/recursive_folder_search.html

Public Function RecursiveDir(colFiles As Collection, strFolder As String, strFileSpec As String, bIncludeSubfolders As Boolean)

    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add files in strFolder matching strFileSpec to colFiles
    If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> ""
        colFiles.Add strFolder & strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Fill colFolders with list of subdirectories of strFolder
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop

        'Call RecursiveDir for each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If

End Function

Open in new window

Just add the above to a module and call it this way to add the contents to your table:

    Dim colFiles As New Collection
    Dim vFile As Variant
    Dim rs As Recordset
    
    Set rs = CurrentDb.OpenRecordset("Dir_Listing")
    DoCmd.RunSQL "Delete * From Dir_Listing"
    RecursiveDir colFiles, "C:\Main\Pictures\", "*.jpg", True
    
    For Each vFile In colFiles
        rs.AddNew
        rs!FileName = vFile
        rs.Update
    Next vFile
    
    rs.Close
    set rs = Nothing

Open in new window

Ron