Results 1 to 5 of 5
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    List Folders

    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

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    try: "SELECT """ & DirectoryName & """ AS Directory"

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Another way would be: "SELECT " & Chr(34) & DirectoryName & Chr(34) & " AS Directory"

  4. #4
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Thx RG. Both statements seem to work great.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You're very welcome. Glad I could help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating folders through access
    By cbende2 in forum Access
    Replies: 2
    Last Post: 07-23-2015, 09:40 AM
  2. Replies: 3
    Last Post: 05-13-2015, 08:24 AM
  3. Hyperlink to network folders
    By smannon in forum Forms
    Replies: 1
    Last Post: 10-24-2011, 02:42 PM
  4. Deleting Folders
    By Madmax in forum Access
    Replies: 1
    Last Post: 10-05-2011, 11:02 AM
  5. Import from various folders
    By Statique in forum Import/Export Data
    Replies: 2
    Last Post: 07-02-2009, 06:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums