CODEWITHSUNDEEP
ms-access
piokol
piokol

Reputation: 121

Minus/hyphen in table name in SQL query

I just realized that my SQL statements containing minus sign "-" in table names are executed without error. For example: 

SELECT * from MYTAB-10;
SELECT * from MYTAB-9;

also using adodb objects: 

rst.Open "MYTAB-10",conn, adOpenDynamic, adLockOptimistic

...while I don't have table called MYTAB-10, just MYTAB. I'm really surprised, as never heard about any operators in table name written like this. Number of rows in both versions (with -, and without), and regardless number at the end is the same, so what really is happening here? I didn't found any information about it so far. Is there any rationale behind this?

Upvotes: 2

Views: 793

Answers (1)

Erik A
Erik A

Reputation: 32642

Access automatically interprets a word break before -

This means:

SELECT * from MYTAB-10

Is equivalent to the following queries

SELECT * FROM MYTAB -10 -- Space added
SELECT * from [MYTAB] AS [-10] -- Explicit alias to make meaning more clear

This also means you could write the following

SELECT [-10].SomeField FROM MYTAB-10 INNER JOIN MYTAB-11 ON [-10].SomeField = [-11].AnotherField

As ADO automatically adds SELECT * FROM if you just provide a table name, that's equivalent.

However: NEVER use this. This behavior hasn't been standardized, and will make your code nonportable as other RDBMSes will syntax error on it.

Also note that this doesn't work in ADO when directly opening a table instead of using an auto-generated SQL statement:

Set rst = CurrentProject.Connection.Execute("MYTAB-10", Options := adCmdTableDirect)
'Errors: cannot find input table or query MYTAB-10

Also note: the + character exhibits similar behavior. However, \, : and * do not.

Upvotes: 2

Related Questions