Table to include folders in a directory - Multilevel

[georgetk]

Hi,
I have seen some of the threads but and looking to create a table in Access 2003 with the following information

Address / File / Type / size /
C: / Access / Folder / 375KB
C: / TableExcel.xls / xls or excel. / 200 KB
C:Access / Test2Access.Mdb / mdb or access / 200Kb
C:Access / TestAccess.Mdb / mdb or access / 50 Kb
C:Access / Templates / Folder / 100KB

I wont my table to include all folders and files in both C and D drive.

Is this possible. If so can u give me a program to do the same. I am a novice to Acess.

THanks for ur help and Support

[NeoPa]

We don't provide solutions here George. We help you to learn how to do it yourself. If you're interested we can do a certain amount of hand-holding.

What you talk about is certainly possible. You would need to be clearer exactly what you are looking to do. This could all be quite involved you understand (sounds like it may be quite fun though, if you're up for it).

[georgetk]

Thanks NeoPa for your response.
Sorry if I sounded like I wanted a solution. I actually wanted to learn how I could achieve the following.
Really what I am looking to achieve is a code that could create a table. The table is to include all files/folder that are currently located in C: drive multilevel)The table should have the following field.
Address: this is the address - for example: C:/Access
File/Folder name - This is all the files/folders that exist in this folder
Type - states the extension (.mdb, .xls or if it is a folder)
Size - states the size of the item.

So far I have managed to do the code below. This basically brings out all files and folders stored in my C drive. I am looking for help to extract the other fields and make it multilevel. And ofcourse if possible a Link that will take me directly to the folder address. Hope u could teach/help me.

Sub InsertDirectories()
    Dim DirectoryName
    Dim sql
 
    DirectoryName = Dir("C:\", vbDirectory)
    Do Until DirectoryName = ""
        If DirectoryName <> "." And DirectoryName <> ".." Then
 
            sql = "INSERT INTO TBL1 ( Name ) " & _
                "SELECT '" & DirectoryName & "' AS Directory"
            CurrentProject.Connection.Execute sql
        End If
        DirectoryName = Dir
    Loop
End Sub

[ChipR]

To get further information about the files, you will probably need to use the FileSystemObject. See Scripting Run-Time Reference on MSDN and Microsoft Scripting Runtime #1 from the Insights section here on Bytes.

[NeoPa]

Well, first of all you need to understand that because Dir() can only work relative to the previous call, it is not going to be straightforward to populate this table in a simple, recursive, fashion. Secondly, of course, Chip is right about needing something else to return the size values of the files. It is possible to determine this by opening the file, but this is time-consuming and the better approach seems to be to use the FileSystemObject as he suggests.

With this in mind, and understanding that this FileSystemObject also has a means whereby you can determine the files in the structure too (probably easier to work with too than the Dir() function), it seems appropriate to ignore Dir() entirely and look at a completely FileSystemObject based solution.

First you need a reference to the "Windows Script Host Object Model".

I have knocked up some code to start you off with. It simply trawls through from the point you enter and displays the values you're after in the Immediate Pane of the debugger. The code isn't very long, and I've included the database as an attachment for you to have a play around with.

Option Compare Database
Option Explicit
 
Private Sub cmdTrawl_Click()
    Dim fso As FileSystemObject
    Dim fld As Folder
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(FolderPath:=Me.txtRoot)
    Call DoFolder(fld)
End Sub
 
Private Sub DoFolder(fldParent As Folder)
    Dim fld As Folder
    Dim fil As File
 
    With fldParent
        Debug.Print .Path
        For Each fld In .SubFolders
            Call DoFolder(fldParent:=fld)
        Next fld
        For Each fil In .Files
            With fil
                Debug.Print .ParentFolder, .Name, .Type, .Size
            End With
        Next fil
    End With
End Sub
 
Private Sub cmdExit_Click()
    Call DoCmd.Close
End Sub

[NeoPa]

Just as I was waking up this morning it occurred to me that something I'm doing at work atm could benefit directly from a database structure such as the one you describe (with maybe a few more fields populated). I will see if I can come up with something more complete and post it. Perhaps as an article. Watch this space.

[NeoPa]

While exploring in this area I noticed something I feel I ought to share :
The Size property of a folder is calculated at the time by trawling through all the contained files and subfolders (and their files and subfolders (etc.)). Generally to be avoided as :

1. It can take an extraordinarily long time.
2. It is likely (particularly on any system drive) to come across files or folders that have been protected and access denied. In this case it trundles along seemingly forever, only to fail to provide a result in the end anyway.

[NeoPa]

Another, related, thread can be found at Automatic update of excel spreadsheet.

[NeoPa]

I'm just playing with a version that I've prepared for work. I've found out much about file usage that I wouldn't have guessed :

1. File Types > 50 chars long. Will need to increase
2. Full pathnames longer than 255 (even though this has always been published as a practical limit I believe). As this is pretty fundamental to linking files to their holding folders, Memo fields are not an option. I've had to truncate to 255 instead.
3. Sizes larger than can fit in a 32-bit Long Integer - No great surprise I suppose nowadays. I have had to handle this by showing and storing values as Bytes, through KiloBytes, MegaBytes, etc to YottaBytes (1024 ^ 8), as and when required.
4. Data drives are ok, but anything with security applied (almost all System drives) causes the code to stop dead in its tracks. Not even allowing continuation by ignoring the secure folders.

All in all a bit more work to do :(

[georgetk]

Great i will wait for ur article. I hope it helps me. Please do let me know once u get there. Thanks once again

[NeoPa]

I wouldn't recommend that George. I first have to produce a working database for work, which may take a fair while. After that I will only get a chance to put it into language fit for an article when I have a chunk of time to spare. That may be months.

Besides, this really isn't about someone doing it for you. It's about your looking at an example, with any helpful comments too, and playing with it yourself to produce your own thing. I will post what I have already as it will certainly be more of the basics done for you, but that's all I plan on for now.