How to query for a MS Access column whose name has a question mark, via ODBC?

Question

I have a MS Access database (Access 2002, for the record). It has a column whose name contains a question mark, say, for example table "Users" with columns "uid" and "isAdmin?". I need to connect to this database via ODBC, and query for this column, along the following lines:

select [uid], [isAdmin?] from Users order by [isAdmin?];

How do I escape the question mark in the column name, so that the MS Access ODBC driver doesn't think that it's a query parameter? This query doesn't use any parameters, so it's fine if I disable them entirely.

Some limitations:

• I can't easily change the column names.
• I can't easily use something other than ODBC to connect, though this is probably my fallback plan if I can't get ODBC to behave.
• I can't just say select * from Users -- it'd still choke on the order by (which is complicated in the real query, so really needs to be done in SQL).

Things I've tried that don't work:

• select [uid], '[isAdmin?]' from Users; -- this makes the second column be the string "[isAdmin?]"
• select [uid], ['isAdmin?'] from Users;
• select [uid], [isAdmin\?] from Users;
• select [uid], [isAdmin\?] {escape '\'} from Users; -- nor does any other escape char work.
• select [uid], { [isAdmin?] } from Users;

EDIT: I should have clarified, I can't easily change the database much at all, except via ODBC (or ADO or DAO or whatever, but that'll be a bit tricky, and at that point I can just run the query through those).

Answer 1

I would use pass through queries and rename the fields in the query. Create your pass through something like

 select [uid], [isAdmin?] AS ISADMIN from Users order by [isAdmin?]

(or whatever it is in the native SQL),
then in Access, just reference that query

select Uid, ISADMIN from qpstUsers

Answer 2

Another option would be to create a view of the Users table and rename the offending column in the view.

Answer 3

This might seem like a devious solution, but it sounds like you're not being given any reasonable help on solving the problem.

Do you have write access to the structure of the MDB? And do you have a copy of standalone Access?

If so, create a new blank MDB file (you will discard this when finished). Create a linked table from the new MDB and use that to write a saved QueryDef that aliases the field. Then use DoCmd.TransferDatabase to export it from your temporary MDB into the real one.

This assumes you have SMB file system access to the MDB that you're accessing from your application via ODBC.

The other alternative, since getting a view with appropriate aliasing into the MDB is a one-shot deal, would be to do this with OLEDB, assuming you have OLEDB access to the MDB.

Answer 4

Try this "IsAdmin?"

Double quotes worked in my situation where someone named the column: Lead #

My where clause then became where "Lead #" = '123'

Answer 5

I take that back...

where ([Lead #] = 123)

This is what saved me. Notice the parentheses around the clause and the lack of single quotes around my parameter 123. Hope this helps.