Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 05-27-2014, 08:14 AM   #1
BlueIshDan
 
Join Date: May 2014
Posts: 1,122
Thanks: 155
Thanked 160 Times in 156 Posts
BlueIshDan is on a distinguished road
Smile [CODE] Fill table with all subdirectories & files of database location.(DEPTH OPTION)

This is a set of code that allows you to place the access database file into a directory and then mine down to suck in all of the subdirectories' files into a table. The function allows for a depth setting, indicating how deep you would like to mine.

Here it is

TABLE: tbl_indexed_files

Code:
ID  : AutoNumber
name: Text
path: Text
file: Text
CODE:
Code:
Private Sub btnMineDown_Click()
    
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("tbl_indexed_files")
    Dim fs As New FileSystemObject
    
    RunSQL "DELETE * FROM tbl_indexed_files", False

    For Each var_file In fs.GetFolder(CurrentProject.Path).Files
        rs.AddNew
        rs!Name = fs.GetFile(var_file).Name
        rs!Path = fs.GetFile(var_file).ParentFolder.Path
        rs!File = var_file
        rs.Update
    Next

    For Each var_folder In GetSubFolders(CurrentProject.Path, 25)
        For Each var_file In fs.GetFolder(var_folder).Files
            rs.AddNew
            rs!Name = fs.GetFile(var_file).Name
            rs!Path = fs.GetFile(var_file).ParentFolder.Path
            rs!File = var_file
            rs.Update
        Next
    Next
    
    MsgBox "Complete!: " & rs.RecordCount: DoEvents
    
    rs.Close
End Sub

Public Function GetSubFolders(address, depth) As String()
    
    Dim fs As New FileSystemObject
    Dim sub_folders() As String
    Dim folder_count As Long
    Dim new_bundle_count As Long
    Dim temp_folder_count As Long
    
    ' Check the existance of the directory.
    If fs.FolderExists(address) Then
        
        ' Store the initial list of subfolders into an array.
        For Each var_folder In fs.GetFolder(address).SubFolders
            ReDim Preserve sub_folders(folder_count)
            sub_folders(folder_count) = var_folder
            folder_count = folder_count + 1
        Next
        
        ' With i = 1, we will look into each of the initially collected _
        ' subfolders, and collect of their sub folders.
        
        ' For each increment until we reach the requested depth, we will
        ' collect the subfolders within each of the last result of subfolders.
        
        ' For your knowledge: _
        '                    The result will return an array that seems
        '                    to be sorted by depth.
        For i = 1 To depth
            temp_folder_count = folder_count
            For j = new_bundle_count To temp_folder_count - 1
                new_bundle_count = new_bundle_count + 1
                For Each var_folder In fs.GetFolder(sub_folders(j)).SubFolders
                    ReDim Preserve sub_folders(folder_count)
                    sub_folders(folder_count) = var_folder
                    folder_count = folder_count + 1
                Next
            Next
        Next
        
    End If
    
    GetSubFolders = sub_folders
    
End Function

Public Sub RunSQL(ByVal sql As String, ByVal set_warnings As Boolean)
    If Not set_warnings Then: DoCmd.SetWarnings False
    DoCmd.RunSQL sql, false
    If Not set_warnings Then: DoCmd.SetWarnings True
End Sub
Regards,
BlueIshDan


Last edited by BlueIshDan; 05-27-2014 at 09:50 AM.
BlueIshDan is offline   Reply With Quote
The Following User Says Thank You to BlueIshDan For This Useful Post:
Lightwave (03-18-2017)
Old 05-27-2014, 10:09 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,142
Thanks: 81
Thanked 2,010 Times in 1,958 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: [CODE] Fill table with all subdirectories & files of database location.(DEPTH OPT

Have you seen Allen Browne's materials?
http://allenbrowne.com/ser-59alt.html
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
BlueIshDan (05-27-2014)
Old 05-27-2014, 10:10 AM   #3
BlueIshDan
 
Join Date: May 2014
Posts: 1,122
Thanks: 155
Thanked 160 Times in 156 Posts
BlueIshDan is on a distinguished road
Re: [CODE] Fill table with all subdirectories & files of database location.(DEPTH OPT

Quote:
Originally Posted by jdraw View Post
Have you seen Allen Browne's materials?
http://allenbrowne.com/ser-59alt.html
I have not no, I'll go take a look thanks

BlueIshDan is offline   Reply With Quote
Old 05-27-2014, 10:15 AM   #4
BlueIshDan
 
Join Date: May 2014
Posts: 1,122
Thanks: 155
Thanked 160 Times in 156 Posts
BlueIshDan is on a distinguished road
Re: [CODE] Fill table with all subdirectories & files of database location.(DEPTH OPT

Oh cool, I'm wondering which would be faster with a greater amount of depth to be mined. His way seems to call itself like a QSort funtion would until there is nothing left to return. This would require some kind of global variable to be counted outside of the function, or two variables to be passed around in it's loop. A counter and a max. Cool beans
BlueIshDan is offline   Reply With Quote
The Following User Says Thank You to BlueIshDan For This Useful Post:
Lightwave (06-19-2014)
Old 06-19-2014, 04:23 AM   #5
Lightwave
Ad astra
 
Lightwave's Avatar
 
Join Date: Sep 2004
Location: Edinburgh
Posts: 1,401
Thanks: 123
Thanked 137 Times in 129 Posts
Lightwave will become famous soon enough
Re: [CODE] Fill table with all subdirectories & files of database location.(DEPTH OPT

Ok so I think I see an application...
Photos named with IDs in subdirectories. Import their directories using above code then parse out the ID numbers place in separate field and relate to PKID of main field hey presto can link to all photos from the main table..

Good job.
Lightwave is offline   Reply With Quote
The Following User Says Thank You to Lightwave For This Useful Post:
BlueIshDan (06-19-2014)
Old 06-19-2014, 04:40 AM   #6
BlueIshDan
 
Join Date: May 2014
Posts: 1,122
Thanks: 155
Thanked 160 Times in 156 Posts
BlueIshDan is on a distinguished road
Re: [CODE] Fill table with all subdirectories & files of database location.(DEPTH OPT

Hope this helped in any minor way
Thanks

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Blue's Age Poll:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Animated Sorting:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Mining File Directories:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GetSubFolders:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

GetFileLines:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

FindSimilarValues:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Move (Avoid moving objects game):
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BlueIshDan is offline   Reply With Quote
Reply

Tags
files , folders , index , search

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
split database unable to find table location issue. ltvrdy General 5 03-25-2014 07:52 AM
Stock Transaction Table - Location A to Location B Lyconal Forms 8 06-01-2009 06:15 AM
Automate User Option to Move files from one server location to another jsadler General 3 10-19-2006 10:40 AM
Auto Fill Location Details on Form? blu3shirt Forms 2 09-15-2006 09:05 AM
List files on subdirectories michaelfischer Modules & VBA 2 03-27-2002 10:46 AM




All times are GMT -8. The time now is 09:43 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World