DAO QueryDef - access thinks my column name is a parameter

[noirs]

I'm not sure if this is the right place or not... I'm using an Access
database from VB6 via DAO.

I'm doing something like this:

sSQL = "select * from table1 where [column name] = 2"
set objQuery = db.CreateQueryDef( "", sSQL )

in the resulting temporary QueryDef, there is 1 parameter, named "colum
name". I expected there to be 0 parameters.

It looks to me like access parses out the SQL finding [foo] to make them
parameters, but I have column names with strange names (spaces, numbers) that
require square brackets, so how do I tell Access that these are column names
and not parameters ?

I am using a QueryDef because I am trying to add a where clause which
compares a field's value to a string, and the string has lots of strange
characeters, which prevents me (so far) from using using it in straight SQL.

- Alex

 

[Sylvain Lafontaine]

I don't really know the answer to your question but you may try adding the
name of the table before the column:

sSQL = "select * from table1 where table1.[column name] = 2"

or:

sSQL = "select * from table1 as T1 where T1.[column name] = 2"

 

[noirs]

Thanks, I tried that. I found the problem!!

I had mistyped the name of the column, so access then thought it was a
parameter, not a colum name. It actually had nothing to do with the square
brackets.
--
Alex Black

I don't really know the answer to your question but you may try adding the
name of the table before the column:

sSQL = "select * from table1 where table1.[column name] = 2"

or:

sSQL = "select * from table1 as T1 where T1.[column name] = 2"

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I'm not sure if this is the right place or not... I'm using an Access
database from VB6 via DAO.

I'm doing something like this:

sSQL = "select * from table1 where [column name] = 2"
set objQuery = db.CreateQueryDef( "", sSQL )

in the resulting temporary QueryDef, there is 1 parameter, named "colum
name". I expected there to be 0 parameters.

It looks to me like access parses out the SQL finding [foo] to make them
parameters, but I have column names with strange names (spaces, numbers)
that
require square brackets, so how do I tell Access that these are column
names
and not parameters ?

I am using a QueryDef because I am trying to add a where clause which
compares a field's value to a string, and the string has lots of strange
characeters, which prevents me (so far) from using using it in straight
SQL.

- Alex