MS Access create multi level subfolders

Question

How can I programmatically create multiple levels of subfolders in VBA for MS Access? I know that MKDir only allows me to create one level, but I want to create 2 levels. The first level folder is based on the year the shipment took place, then the sub-level folder to that is the shipment number. The idea is to check and see if a folder(s) exists, and if not to create and open them.

Here is what I have so far:

Private Sub Command173_Click()
 Const strParent = "S:\shipments\"
 Dim strYearEntered As String
 Dim strEntryNumber As String
 Dim strFolder As String
 Dim fso As Object
 strYearEntered = Me.YearEntered
 strEntryNumber = Me.EntryNum
 strFolder = strParent & strYearEntered & "\" & strEntryNumber
 Set fso = CreateObject("Scripting.FileSystemObject")
 If fso.FOLDEREXISTS(strFolder) = False Then
     fso.CreateFolder strFolder
 End If
 Shell "explorer.exe " & strFolder, vbNormalFocus

End Sub

Using this code gives me an error at the "fso.CreateFolder strFolder" line. This problem only occurred when I placed the "\" in the strFolder line, without the "\" it will only create one folder by cramming together the YearEntered and EntryNum values. Can anyone assist in this matter?

Thanks.

Answer 1

This answer maybe is too late but I will like to share with you this VBA util function where you can create subfolders recursive (multi-level) in a path.

Function createDirIfNotFound(ByVal sPath As String)
  Dim iStart          As Integer
  Dim aDirs           As Variant
  Dim sCurDir         As String
  Dim i               As Integer
  If sPath <> "" Then
    aDirs = Split(sPath, "\")
    If Left(sPath, 2) = "\\" Then
        iStart = 3
    Else
        iStart = 1
    End If
    sCurDir = Left(sPath, InStr(iStart, sPath, "\"))
    For i = iStart To UBound(aDirs)
        sCurDir = sCurDir & aDirs(i) & "\"
        If Dir(sCurDir, vbDirectory) = vbNullString Then
            mkDir sCurDir
        End If
    Next i
  End If
End Function

And you can use it like this, and folders will be created deeply if not found:

createDirIfNotFound "c:\root\level1\level2" 

I hope this helps somebody.