How to select a table name with whitespaces in access 2007 from outside access (1 Viewer)

Jelte · Feb 25, 2010

Hi!
I don’t know for sure this is the correct forum to ask this question but it’s the best I could find.
I use an external program (Bonita Studio, business process modeler) to manipulate may access database (in SQL). But I encountered a problem. When I try to select a table name with a whitespace in it (e.g. SELECT * FROM ‘Test Table’) it gives an error. I posted this problem on the Bonita forum but I was told this is an ODBC Microsoft Access driver issue. I tried “test table”, `test table`, [test table] and ‘test table’. Is there a way to select tables with whitespaces? Or doesn’t the ODBC driver support this?
Greetings Jelte

DCrake · Feb 25, 2010

Are you sure it is white space and not an underscore? try Test_Table

why not use a wzard to create a query and look at the sql once created.

Jelte · Feb 25, 2010

I tried it with the underscores but then access can't find the name (obvious because access thinks it is called test table not test_table)

I first make the query in access. It works there, but when I copy the SQL code to the other program is doesn't work

. So I first thought it was a bug in the program i'm using, but the developers of that program told me it is a microsoft problem... I think the ODBC driver cannot convert it or something

confused:, i'm no programmer so I don't know). Normally in SQL names with white spaces can be used like `test table`. But this doesn't work....

DCrake · Feb 25, 2010

Can you not rename the table?
Or create a query based on the table and save the query as TestTable and link that.

SOS · Feb 25, 2010

All you need is to enclose it in brackets:

SELECT * FROM [Test Table]

DCrake · Feb 25, 2010

According to the initial post the OP has tried [Test Table]

SOS · Feb 25, 2010

I've never had a problem like that but then it may be an Access ODBC issue. I don't know that I've needed the Access ODBC driver (except perhaps in Crystal Reports).

Does the Bonita Studio have the ability to use an OLEDB connection instead?

Dave H · Feb 25, 2010

I don't have an issue with this when I query an access database from excel using microsoft query so I wouldn't think it is a microsoft problem.

gemma-the-husky · Feb 25, 2010

surely square brackets works

[test table]

are yuo sure you got the syntax correct

if not, try the bonita help again. maybe its a different character you need

Jelte · Feb 26, 2010

I will post it at the Bonita Studio forum. But thanks for your help, I decided it is better to change the database (it will cost me a day, but I think it will be more sound without whitespaces).

Jelte

How to select a table name with whitespaces in access 2007 from outside access (1 Viewer)

Jelte

New member

DCrake

Remembered

Jelte

New member

DCrake

Remembered

SOS

Registered Lunatic

DCrake

Remembered

SOS

Registered Lunatic

Dave H

Registered User.

gemma-the-husky

Super Moderator

Jelte

New member

Similar threads

Users who are viewing this thread