CREATE INDEX problem

[Tcs]

I have a db with 12 tables, and after I import all the data, I want to create my
indexes. Access keeps telling me:

"Syntax error in CREATE INDEX statement."

on the very first one, but geez, *I* don't see it. I've tried both with and
without a space after the table name. Apparently I'm wrong somewhere else. Can
anyone help?

Dim strSQLstmt As String
Dim strIndxName As String
Dim strFldName As String

If intLoop2 = 1 Then
strTblName = "tblUBBudg_Hdr"
strIndxName = strTblName
strFldName = _
"BUD-ACCOUNT, " & _
"BUD-SERVICE, " & _
"BUD-YEAR"
End If

strSQLstmt = "CREATE INDEX " & _
strIndxName & "_IDX ON " & _
strTblName & " (" & _
strFldName & ");"

DoCmd.RunSQL (strSQLstmt)

Here's my SQL string:

CREATE INDEX tblUBBudg_Hdr_IDX ON tblUBBudg_Hdr (BUD-ACCOUNT, BUD-SERVICE,
BUD-YEAR);

Your help is appreciated. Thanks in advance,

Tom

 

[Douglas J. Steele]

What are the characters in the field names? It looks like a minus sign, not
an underscore.

 

[Tcs]

That's correct. The field names, in this case, all contain "-" (hyphens).
That's how I created the table.

 

[Douglas J Steele]

I'd recommend strongly against using hyphens in your field names, to avoid
Access getting confused about where you want subtraction to be done in
queries.

If you can't (or won't) change the field names, try putting square brackets
around them:

strFldName = _
"[BUD-ACCOUNT], " & _
"[BUD-SERVICE], " & _
"[BUD-YEAR]"

so that your DDL statement looks like

CREATE INDEX tblUBBudg_Hdr_IDX ON tblUBBudg_Hdr ([BUD-ACCOUNT],
[BUD-SERVICE],
[BUD-YEAR]);

I would assume you'd need to do this in any SQL related to those fields as
well.

 

[Tcs]

The square brackets did the trick. Thanks a lot,

Tom