Using reserved keywords within SQL queries

[Sanx]

Hi all,

I'm writing a small program (actually in VB.NET - but it's not relevant) to access a database and pull some info out of it.

It's not my database, so I can't rename any of the tables. The table I'm interested in is called 'USER' - a reserved SQL keyword. The database can either be a file-based JET database or hosted on SQL.

The problem is the exact SQL syntax needed. Within SQL hosted DBs, the query can be made to work by delimiting (quoting) the table names as follows:

SELECT "NAME" FROM "USER" WHERE ID = 1

That query when run against the JET database results in the error Syntax error (missing operator) in query expression .... FYI: 'ID' is an Autonumber (integer) column. I've tried various query formats without success. Others I've tried include:
SELECT 'NAME' FROM 'USER' WHERE ID = 1
SELECT "USER.NAME" WHERE "USER.ID" = 1
SELECT 'USER.NAME' WHERE 'USER.ID' = 1

All of these queries work just fine when run against a SQL-hosted database. I need a query which works on both Access and SQL. Any ideas? Thanks.

[Sanx]

Don't worry, found the solution. Instead of delimiting the reserved keywords in quotes, use square brackets, i.e:

SELECT NAME FROM [USER] WHRE ID = 1

[Morgoth]

This might be a stupid question, but did you try it without any delimiters?

[Sanx]

Of course. Should you try it without any delimitation, you end up with an error along the lines of Error in query near keyword 'USER'.

As I said though - found the solution now.

[fractalvibes]

find the person or persons who created that database and beat them over the head with the largest SQL reference you can find....

fv

[smagnus1]

How about... select Name from [user] where id = 1;

[Morgoth]

smagnus1,

Don't worry, found the solution. Instead of delimiting the reserved keywords in quotes, use square brackets, i.e:

SELECT NAME FROM [USER] WHRE ID = 1
As I said though - found the solution now.