CREATE TABLE where table name consists of multiple words.

[gmaxey]

Hi,

I'm am trying to write some code that uses DAO (and I don' really even know what that means) and use ADODB instead.

In a part of the code a new database table (Access *.mdb format file) is created using the following statement:

Dim strVariableName
Dim tdNewTable As TableDef
strVariableName = "Three Word Title"
Set tdNewTable = dbMyData.CreateTableDef(strVariableName)

I have figured out how to add a similar table using ADODB but it only works if strVariableName is a single word:

strVariableName = "Table1"
m_oConn.Execute "CREATE TABLE " & stVariableName & "(" & _
"fldAlpha VARCHAR(1) NOT NULL, fldBravo VARCHAR(2) NOT NULL)"

If I used strVariableName = "Three Word Title" I get a "Syntax Error in CREATE TABLE statement.

So I know that it is possible to create a table titled with two or more words in a access data base (because the old code does that). My question is how do I use the CREATE TABLE statement to create a table titled with two or more words.

The reason I need to use two or more word table titles is these tables titles are later queried to populate a userform list. I would llike for my listed titles to be meaningful and look clean as they do now:

Bird Migration Habits
Cat Feeding Habits

Not
BirdMigrationHabits
Cat_Feeding_Habits

etc.

Thanks.

[gmaxey]

I figured this one out (or least it appears I did). Enclosing the variable in brakets "[ ]" seems to work.


m_oConn.Execute "CREATE TABLE [" & stVariableName & "](" & _
"fldAlpha VARCHAR(1) NOT NULL, fldBravo VARCHAR(2) NOT NULL)"

[Doug Robbins]

Hi Greg,

An alternative would have been to use the underscores (_) in place of the spaces and then later, use the Replace() function to replace the underscores with spaces/

[gmaxey]

Yes of course that would work also. Thanks.

[HiTechCoach]

Hi Greg,

An alternative would have been to use the underscores (_) in place of the spaces and then later, use the Replace() function to replace the underscores with spaces/
The VBA standard is to wrap object names (tables, field, etc) with [ and ] that have spaces in the name. Your suggestion will not work in many cases.