CSV File Import - Solution
Well, I tinkered long enough and found my solution. I modified cmdImport_Click() to the following. fileLocation is now the name of the text box where I am passing the file name and full path to. The cmdImport function then parses the name and directory out.
Code:
Private Sub cmdImport_Click()
Dim filesystem As Object
Set filesystem = CreateObject("Scripting.FilesystemObject")
fileDirectory = filesystem.GetParentFolderName(fileLocation) & "\"
Dim fileName As String
fileName = filesystem.GetFileName(fileLocation)
Set rs = CreateObject("ADODB.Recordset")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileDirectory & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
strSQL = "SELECT * FROM [" & fileName & "] "
rs.Open strSQL, strConn, 3, 3
rs.MoveFirst
Do
frmBook.FIRSTNAME.Value = rs("FirstName")
frmBook.LASTNAME.Value = rs("LastName")
frmBook.MIDDLENAME.Value = rs("MiddleName")
End If
rs.MoveNext
Loop Until rs.EOF
Unload Me
End Sub