(c0ders) VBA & Access to VBA & MySQL

[Whir]

Okay, this is going to sound stupid, but I'm writing this program in VBA using AccessXP. After I get it done and working, we're going to convert it to MySQL and run the database (not the program) on a Linux box we snuck into the building (just to prove how much faster it is). VBA has a coniption when I try to query using a table name with spaces. Is there a way I can keep the spaces, or do I have to remove all the spaces from my table name? Here's an example.

Public Sub Do_Aerial_Draft()
    Dim Conn As ADODB.Connection
    Dim rsPoles As ADODB.Recordset
    
    Set Conn = CreateObject("ADODB.Connection")
    Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Code Projects\Route.mdb;
     Persist Security Info=False", Admin
    
    Set rsPoles = CreateObject("ADODB.Recordset")
    
    rsPoles.Open "SELECT * FROM AerialFacilities", Conn, adOpenStatic, adLockReadOnly
    
    rsPoles.MoveFirst
    
    sTemp = rsPoles.Fields("Block Name").Value
    
    MsgBox sTemp
    
    
End Sub

Say I want it to read "SELECT * FROM Aerial Facilities", Conn, etc. Is there a way to do that? It doesn't work with " because it thinks the select statement is done right after FROM then. Would single quotes work?

Thanks,

-Whir

Edit: I should read these before I hit post...

[strangerstill]

AFAIK in SQL you can put names in square brackets: "SELECT * FROM [Aerial Facilities]" - this should sort it?

[vass0922]

strangerstill has it right, ya gotta use square brackets
I think that's standard in SQL...

I just prefer (by far!) not to use spaces in table/column names.. makes everything much cleaner.. not to mention its much easier to find bugs when you have spaces in column/table names

[qball]

Quote:

I think that's standard in SQL...

That's news to me, never saw/used that syntax before, nor can I find reference to this syntax, help a brother out? Maybe standard access SQL?

Anyway, syntax does indeed work in Access.

Syntax DOES NOT work in mySQL.

I would have figured:

"SELECT * FROM 'Aerial Facilities'";

Would work. Doesn't in Access, does in mySQL.

Access, sucks.

BTW, mySQL around 10 times faster than Access in some silly benchmarking I did.

[JimG]

Yes, Square Brackets [] are the answer. Use them in several queries I run at work comparing what's in one database file to another (this is such a long story, I won't explain why I do this). Anyway, if you can, it's easier not to use spaces. Even in Excel, when I'm putting titles on my columns, if I know this is going into Access or better yet some custom exe someone built, words get separated with an underscore.

[Whir]

Cool. Thanks a lot guys. I decided to cut the table and field names to no spaces, just to make life easier. Less typing and what not. I just like the spaces personally. Easier to read or something. I'm filing this one away for future reference.

-Whir