Solved

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

Posted on 2014-03-01
6
2,136 Views
Last Modified: 2014-03-01
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?
0
Comment
Question by:clock1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 24

Expert Comment

by:Eirman
ID: 39897205
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/
0
 

Author Comment

by:clock1
ID: 39897263
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!
0
 
LVL 24

Expert Comment

by:Eirman
ID: 39897287
CORRECTION:

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

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 38

Accepted Solution

by:
Jim P. earned 250 total points
ID: 39897331
This is older code. I could improve it if I really needed to. But it can give you a basis to work from,

Basically I would do a dir "C:\Path\*.*" > c:\temp\LCEZ_DATA.TXT. That will dump the results into a test file. Then the file is read by the code below and dumped into the Dir_Listing table. You can get an idea of what the table looks like by the addnew statement.

Public Function ImportDirListing()
Dim FileNum As Integer
Dim InputFile As String
Dim InputString As String

Dim I As Integer
Dim Skip As Boolean

Dim DB As Database
Dim RS As Recordset
Dim SQL As String

Dim PathName As String
Dim FileName As String
Dim FileDate As Date
Dim FileSize As Double
Dim FileType As String

SQL = "DELETE * FROM Dir_Listing"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL, True
DoCmd.SetWarnings True

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Dir_Listing")

'Path_Name   File_Name   File_Date_Time  File_Size   Memo_Field
'Text        Text         Date/Time       Long        Text/Memo
FileNum = FreeFile()
InputFile = "c:\temp\dirlist.TXT"
Open InputFile For Input Access Read Shared As #FileNum

PathName = ""

Do Until EOF(FileNum) = True 'I >= 50
    Line Input #FileNum, InputString
    Skip = False
    If Left(InputString, 16) = " Volume in drive" Then Skip = True
    If Left(InputString, 16) = " Volume Serial N" Then Skip = True
    If Left(InputString, 16) = "     Total Files" Then Skip = True
    If Right(InputString, 10) = "bytes free" Then Skip = True
    
    If Trim(InputString) = "" Then Skip = True
    If Mid(InputString, 18, 4) = "File" Then Skip = True
    
    If Left(InputString, 14) = " Directory of " Then
        PathName = Mid(InputString, 15, 253)
        Skip = True
    End If

    
    If Skip = False Then
    
        FileName = Trim(Mid(InputString, 39, 253))

        
        If Left(InputString, 14) <> " Directory of " Then
            FileDate = Replace(Left(InputString, 18), "  ", " ") ' & " " & Trim(Mid(InputString, 13, 8))
        End If
        If Mid(InputString, 25, 5) = "<DIR>" Or Trim(Mid(InputString, 22, 17)) = "<JUNCTION>" Then
            FileType = "Dir"
            FileSize = 0
        Else
            FileType = "File"
            FileSize = (Replace(Trim(Mid(InputString, 22, 17)), ",", ""))
        End If
        
        'Debug.Print PathName & " " & FileName & " " & FileDate & " " & FileSize & " " & FileType
        
        With RS
            .AddNew
            !Path_Name = PathName
            !File_Name = FileName
            !File_Date_Time = FileDate
            !File_Size = FileSize
            !File_Type = FileType
            !Server = "SrvrName"
            .Update
        End With
        'Field names in the table
        'Index_Num   Path_Name   File_Name   File_Date_Time  File_Size   File_Type
        'AutoNum     Text - 255  Text - 255  Date/Time       Double      Text - 20
    End If
    
Loop

Close #FileNum
Set RS = Nothing
Set DB = Nothing

End Function

Open in new window

0
 

Author Comment

by:clock1
ID: 39897386
Spot-on with an Access solution.  Your points.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39897430
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
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question