Clever Geek Handbook

How do I query an MS Access column whose name has a question mark via ODBC?

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

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

How can I avoid the question mark in the column name so that the MS Access ODBC driver doesn't think it's a query parameter? This query doesn't use any parameters, so it works great if I turn them off completely.

Some restrictions:

  • I cannot easily change the column names.
  • I can't easily use anything other than ODBC to connect, although this is probably my fallback plan if I can't get ODBC to behave.
  • I can't just say select * from Users

    - it would stifle the order anyway (which is tricky in a real query, so it 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 the string "[isAdmin?]"
  • select [uid], ['isAdmin?'] from Users;

  • select [uid], [isAdmin\?] from Users;

  • select [uid], [isAdmin\?] {escape '\'} from Users;

    - and no other char output works.
  • select [uid], { [isAdmin?] } from Users;

EDIT : I had to clarify, I cannot easily change the database at all except via ODBC (or ADO or DAO or whatever, but it will be a little tricky, at which point I can just run the query through those).

+2


source to share


5 answers


Another option is to create a table view Users

and rename the offending column in the view.



+2


source


This may seem like a tricky solution, but it looks like you don't need reasonable help solving the problem.

Do you have write access to the MDB structure? Do you have a copy of offline access?

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



It assumes you have SMB file system access to the MDB that you access from your application via ODBC.

Another alternative, since getting a view with a matching alias in MDB is a one-shot deal, would be to do it with OLEDB if you have OLEDB access to MDB.

+3


source


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

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

(or whatever in the original SQL),
then in Access, just reference this query

select Uid, ISADMIN from qpstUsers
+2


source


I faced the same problems. (using MS Access driver from SQLdeveloper).

ACCESS SQL don't like '' 'as in DON'T LIKE "* val *" then I replaced NOT LIKE' * val * '

+1


source


Try this "IsAdmin?"

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

My where clause came when "Lead #" = '123'

I'm taking it back ...

where ([Lead #] = 123)

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

0


source






More articles:


All Articles