Solved

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

Posted on 2014-03-01
6
1,858 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
6 Comments
 
LVL 23

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 23

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now