Insert Subfolders into a table in Access 2003

Mar 7th, 2007 by seanh420

Question: Is there a way in Access 2003 to print a directory list using the dos dir command? I want to add all the subfolders in a given folder into a table.
by Ro Chabot

Answer:
by Sean Henderson

Yes. You can use the oldschool dir command from DOS using VBA’s Shell command. This method however would be cumbersome because we’d first have to launch cmd.exe, pass it the parameters to dirlist to a file, then open the file, parse each line before inserting into the table. Instead, a better approach would be to use ADO and the FileSystemObject to insert the subfolders into the table. The FileSystemObject also gives us a lot of information about the subfolder that we can’t easily get from the dir command. In this example, I will create an Access 2000 format database in Access 2003, and demonstrate this. I will also include my demo mdb for download at the bottom.

Step1: Open Access 2003 and create a new database called subfolders.mdb

Step2: Create a table in design view

Step3: Add two fields to the table: The first one’s field name is "FolderName". It’s datatype is Text. Set it’s FieldSize to 255. Make it the Primary Key (no two subfolders in a folder have the same name ever). The second one’s field name is "CreatedDateTime". It’s datatype is Date/Time. You can add more subfolder information to this table later if you like. Close the table, save it as "tblSubFolders".

Step4: Create a new form in design view.

Step5: Add two command buttons to the form. If a command button wizard pops up, click cancel for both buttons. Set the caption to the first button to "use command dir print to txt file." Set the caption of the second button to "insert subfolders to table using ADO"

Step6: Now we want to code these buttons. To add VBA code to a button, right click the button and select "Build Event…" from the popup menu. The Microsoft Visual Basic editor will open and this is where we will enter the code.

Code for the first button:

[code lang="VB"]

Private Sub Command0_Click()
Dim x As Integer, command As String

command = "dir c:*.* > c:test.txt"
x = Shell("cmd.exe /c" & command, vbHide)

' after this, you'd have to parse the text file before inserting
' fuck that. use ado instead

MsgBox "The dirlist has been created."

End Sub

[/code]

Code for the second button:

[code lang="VB"]

Private Sub Command1_Click()
' Add list of subfolders and createdtime to a table in access 2000
' Coded by Sean Henderson
' 2007-03-01
Dim FSO As Object, objFolder As Object
Dim fld As Object, fyl As Object
Dim n As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim cmd As ADODB.command
Dim strSQL As String
Dim strFile As String
Dim strFullPath As String

Set cmd = New ADODB.command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' wipe the table
strSQL = "DELETE * FROM tblSubFolders"
cmd.CommandText = strSQL
cmd.Execute

' set your parent folder here that you want subfolders from
Set objFolder = FSO.GetFolder("C:Documents and Settings")

' fill tblSubFolders with the folder names and created date
If objFolder.SubFolders.Count Then
For Each fld In objFolder.SubFolders
lDirNum = lDirNum + 1

strSQL = "INSERT INTO tblSubFolders(FolderName, CreatedDateTime) VALUES (" & _
Chr(34) & fld.Name & Chr(34) & "," & Chr(34) & fld.DateCreated & Chr(34) & ")"

cmd.CommandText = strSQL
cmd.Execute
Next fld
End If

Set cmd = Nothing
Set fld = Nothing
Set objFolder = Nothing
Set FSO = Nothing

MsgBox "The Subfolders have been succesfully inserted"

End Sub

[/code]

When you click on the first button, it will tell the command prompt to print a directory list to a file. I decided not to code the rest of what I mentioned earlier because it’s a waste of effort. When you click the second button, it will add all the subfolders of a given folder to the table tblSubFolders. First it wipes the table clean of any records that are in it so that we can start fresh each time. Next it tells the FileSystemObject which parent folder we want to get subfolders from. If the parent does indeed have subfolders, then it loops through them and inserts each subfolder and its creation date. You can add more subfolder information later if you want. Below I’ve included a table of other subfolder information you could get from the FileSystemObject if you wanted to add it. Also notice that when you view the table after clicking the button, all the subfolders and their creation date are in the table now.

Properties of the **Folder** object
taken from http://www.aivosto.com/visdev/fso.html
Property	Description
DateCreated	Date and time the folder was created. * this is the one I used
DateLastAccessed	Date and time the folder was last accessed.
DateLastModified	Date and time the folder was last modified.
Drive	Drive letter of the drive where the folder resides.
Files	A Files collection containing all the files in the folder.
IsRootFolder	True if the folder is the root, False otherwise.
Name	Sets or returns the name of the folder.
ParentFolder	Returns a Folder object representing the folder’s parent folder.
Path	The path of the folder, including drive letter.
ShortName	The short name used by programs that require the old 8.3 naming convention.
ShortPath	The short path used by programs that require the old 8.3 naming convention.
Size	The size, in bytes, of all files and subfolders contained in the folder.
SubFolders	A Folders collection containing one Folder object for each subfolder.