Select from help

My apologies for what mus be a very simple query but I can't find an answer as I'm not sure how to frame the qestion.
But in a program I have:

inputFile="c:\test\test.asc"

I want to put this variable into a select query:

"select * from inputFile"
but I can't get the syntax right.
if I put "select * from test.asc"
there no problem.

Can someone put me straight pleasse?
Thanks

I'm not sure if I understand your problem entirely, but here are my thoughts:

FROM should be the table name

e.g.

SELECT column_name(s)
FROM table_name

SELECT * FROM (table name goes here)
WHERE (column name here)=inputFile

Hope that helps!

what is this? a file or a table?

and what language are you using? php?

Sorry, I should have given more information. I am using vb6 and here is the relevant piece of code

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

This code gives and error at the "set rs...." line because the object inputFile.txt cannot be found.

This code does not give an error

cm.CommandText = "SELECT * FROM C:\test data\test data.asc "


When the code is finished, the user will input the path to inputFile via a textbox on a form so I don't want to have to put the specific filename and path in the SELECT command.

Thanks for your help

thread moved to visual basic forum

->

You may find the following sub routine useful. vKeys contains the SQL command string. The routine returns True if successful, and False if not with vExceptions containing the error. The recordset is opened, updated, and then closed.

J.A. Coutts

If I use
cm.CommandText = "SELECT * FROM " & inputFile

I get a 'Syntax error in FROM clause'

when I try to execute the statement

rs.Open cm, , adOpenKeyset, adLockOptimistic

which follows the

Set rs = New ADODB.Recordset

statement

I have found the cause of the problem I think.
If the variable inputFile contains a path or filename with no spaces in it such as
"C:\testdata\testdata.asc"
everything works fine but if there are embedded spaces such as

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

then I get the Syntax in FROM statement error.

So is there any way of including the inputFile string which contains embedded spaces in the SELECT statement?

Spaces are often considered a separator in windows. Simply look at the links to any of the programs in the "Program Files" directory, and you will see they are enclosed in quotes. You can try single quotes:
cm.CommandText = "SELECT * FROM '" & inputFile & "'"

If that doesn't work, you can try quad quotes """".

J.A. Coutts

I am at a loss here.
I have been trying to get the select statement right by inputting the path directly:
"SELECT * FROM "c:\test data\test data.asc""

and varying the number of double quotes and single quotes around the path.
I either get a "syntax error in FROM clause" or
"Expected end of statement"
depending on the number and combination of quotes and spaces.

You're using the Jet drivers which are for Access databases. In Access SQL (and pretty much all sql) you don't select anything from a file name, you select records from tables or views.

Spend some time with the Jet ADO documentation it's somewhere in the msdn library online. There are numerous example codes that might help you understand how things are supposed to work.