Special character and spaces in table name causing VBA database connection error

Question

I'm having an issue connecting to a Access database table called tbl: Daily Sales because it has both spaces and a colon in the name.

Otherwise the connection to the same database works when I connect to a table with a name containing no spaces or special characters.

I am not allowed to change the table name.

How do I escape the table name?

How are you attempting to connect to the database table? via vba in excel? some other odbc driver? — chollida, Apr 02 '15 at 17:57

CMS · Answer 1 · 2015-04-03T21:10:24.460

1

you should be able to do something like this "[tbl: Daily Sales]"

edited Apr 03 '15 at 21:10

answered Apr 02 '15 at 17:51

CMS

3,657
1
27
46

1

[tbl: Daily Sales], the tbl: is part of the table name. – bp_ Apr 02 '15 at 20:06

score 1 · Answer 2 · edited May 23 '17 at 11:56

Enclosing table names in brackets is good practice and is necessary when special characters(including spaces) are in the table name. Whatever is inside the brackets is the table name. Keep in mind, brackets are not limited to table names, but field names as well.

[tbl: Daily Sales].[fld: Amount]

For more info. Here: [] brackets in sql statements or here: What is the use of the square brackets [] in sql statements? or here: Meaning of square brackets [] in MS-SQL table designer?

Special character and spaces in table name causing VBA database connection error

2 Answers2