VB6 inserting file path into SQL FROM clause

Question

I am working in VB6 on a Windows 7 desktop to read and work on text files and have run into a problem passing variable to a SELECT statement. The code I have is :

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cm As ADODB.Command

Set conn = New ADODB.Connection
conn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & App.Path & ";" & _
    "Extended Properties=""text;HDR=No;FMT=Delimited( )"""

inputFile = "C:\test data\test data.asc"

Set cm = New ADODB.Command
cm.ActiveConnection = conn
cm.CommandType = adCmdText

cm.CommandText = "SELECT * FROM " & inputFile 
Set rs = New ADODB.Recordset
rs.Open cm, , adOpenKeyset, adLockOptimistic

The problem is that the path name to the input file contains spaces and when I run the code above, it fails at the rs.OPen line with an error saying Syntax error in FROM clause. If the path in inputFile does not contain spaces, everything works without a problem.

I have tried many combinations of " ' [] etc around the inputFile but always get the syntax error or another error saying that inputFile.txt can not be found.

Can anyone give me the correct method for handling path/files names with spaces in an SQL statement please?

Answer 1

The value supplied for Data Source should always be quoted to avoid such problems. You can use either the quotation mark " or apostrophe ' to do this, much as you did for the Extended properties value.

Then to use a file name within the Data Source directory as a table name at least "quote" it using brackets [ ], and preferably replace the . for the file extension by the # character.

There is no "OLEDB Provider for text files" but your example shows use of the Jet 4.0 OLEDB Provider with its Text Installable ISAM, which is just fine.

Processing Text Databases offers a lot of information on this topic in general, using VBScript for most examples but it pretty much all applies to VB6 as well.

Answer 2

Apparently spaces in the filename should work if you use square brackets [ ].

cm.CommandText = "SELECT * FROM [" & inputFile & "]" 

You say in the question you've tried square brackets - could you double check? I don't know about spaces in the directory name.