I found below code that would retrieve all the directories and insert to a table. The code seems to work well until it runs to a directory that has an apostrophe, for an example, if the directory name is "O'Hare Airport". How can the below sql statement be modified so that it would return however the directory is named?
Code:
Private Sub cmdCreateDirectories_Click() Dim DirectoryName
Dim sql
Dim Folder As String
Folder = "C:\MyDictory\MySubfolder\"
DirectoryName = Dir(Folder, vbDirectory)
Do Until DirectoryName = ""
If DirectoryName <> "." And DirectoryName <> ".." Then
If (GetAttr(Folder & DirectoryName) And vbDirectory) = vbDirectory Then
sql = "INSERT INTO MyTable ( Folder ) " & _
"SELECT '" & DirectoryName & "' AS Directory"
CurrentProject.Connection.Execute sql
End If
End If
DirectoryName = Dir
Loop
End Sub