SELECT string with table name of two words problem

[Dave F.]

Hi

I'm writing a VBA routine within AutoCAD to query a Table which has a name of ALL LAYERS.
I believe it's have trouble coping with the space in the name.

Dim SqlStr As String

Set MyDB = OpenDataBase("c:\dwgs\docs\BH-Data.mdb", False, True, "MS Access;PWD=sbpl123")
SqlStr = "SELECT Layer FROM " & "'All Layers'" & " WHERE Layer = 's%'"
Set Myrs = MyDB.OpenRecordset(SqlStr)

I get an error of - Syntax error in query. incomplete query clause.
I've tried various ways inlcuding using double quotes but nothing seems to work.

This is another table query in the same database that works.
SqlStr = "SELECT Type, ID FROM LayerType WHERE Type = 'Structural'"

So where am I going wrong.

Hope you can help

Cheers
Dave F.

 

[Michel Walsh]

If the name is ill formed, or a reserved name, you need to include it inside
[ ], [like this]

Vanderghast, Access MVP

 

[Dave F.]

You should only use letters of the alphabet, numbers and the underscore for names.

I had a feeling that was the case. Unfortunately this is an external DB & I have no control over it.
Thankfully [....] appears to work.

Thanks to both for your prompt replies.


Chris O'C via AccessMonster.com wrote:
.. If you forget that rule, most of the time you can enclose the bad

 

[Dave F.]

Oh Boy! I've ran straight into my second problem. Hope you a solve it as easily as before.

SqlStr = "SELECT Layer, Description FROM [All Layers] WHERE Layer LIKE 's%'"

AFAIK, using this wildcard should return numerous rows of all the Layers that begin with s.
It returns 0.

For some reason I tried * instead of % it returned 1 row - the first one beginning with s.

Looking on the web * isn't even a wildcard.

What am I doing wrong?


Cheers
Dave F.

 

[Michel Walsh]

Jet can use the * (the DOS wildcard) or % (the UNIX wildcard). It
depends on an option setting.


Hoping it may help,
Vanderghast, Access MVP

 

[Jeff Boyce]

Dave

In Access SQL, use "*". In SQL SQL, use "%"

Regards

Jeff Boyce
Microsoft Office/Access MVP