Importing text and getting 'not a valid path' error

[Max Moor]

Hi All,

I'm importing from a text file using an SQL statement:

strSQL = "Insert INTO tblImport (Name, Address) " & _
SELECT F1 As Name, F2 As Address " & _
FROM " & strFilename & "#" & strExtension & " " & _
IN '" & strFullFilename & "' 'Text;'"


where
strFullFilename = "C:\Documents And Settings\Max Moor\My Documents
\test.txt"

strFilename = "test"

strExtension = "txt"


When I execute the statement, I get an error that the path in not
valid. The path in the error message is correct, and the file really is
there. Is there a quirk with the SQL syntax that I'm missing? Any other
thoughts?

Thanks, Max

 

[Joe Fallon]

Not sure, but sometimes Long File Names screw things up.
Try using the short file name for the path.
(6 letters and ~1 are usually correct.)

Documents And ... becomes Docume~1

 

[Max Moor]

Hi Joe,
Thanks for the response. It turned out that in the SQL statement:

strSQL = "Insert INTO tblImport (Name, Address) " & _
SELECT F1 As Name, F2 As Address " & _
FROM " & strFilename & "#" & strExtension & " " & _
IN '" & strFullFilename & "' 'Text;'"

where I was using the fully p[athed filename, as strFullFilename, I
should have only been using the path, without the filename. It works just
fine with that change. FYI

Max