Run time Error

Hi,

While using the below code in am getting a run time error 3075 because of a special character '(Apostrophe) in the FileItem.Name and FileItem.Path.

Your help on this is highly appreciated.

Sub List_Of_Files_In_Folder()
    ListFilesInFolder "I:\", True
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.Files
          CurrentDb.Execute "INSERT INTO MyTable (CurrentFilePath,DestinationFilePath,ParentFolder,FileName,FileSize,FileType,DateCreated,DateLastAccessed,DateLastModified,Attributes,ShortFileName) " & _
                "VALUES (" & _
                "'" & FileItem.Path & "'," & _
                "'" & "D:\Archiving " & Left(FileItem.Path, 1) & " Drive" & Mid(FileItem.Path, 3, (Len(FileItem.Path))) & "'," & _
                "'" & Mid(FileItem.Path, 4, (InStr(4, FileItem.Path, "\") - 4)) & "'," & _
                "'" & FileItem.Name & "'," & _
                "'" & FileItem.Size & "'," & _
                "'" & FileItem.Type & "'," & _
                "'" & FileItem.DateCreated & "'," & _
                "'" & FileItem.DateLastAccessed & "'," & _
                "'" & FileItem.DateLastModified & "'," & _
                "'" & FileItem.Attributes & "'," & _
                "'" & FileItem.ShortPath & FileItem.ShortName & "'" & _
                ")"
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub