[RESOLVED] Syntax Error in field definition.

[saranmc]

Hi,

I am trying to add a column to a table.
It was working fine and now suddenly i get an error message " Syntax Error in field definition"

Dim Sql As String
Dim tbl As String
Dim col As String
Dim rtl As String

tbl = Me.Combo67
col = Me.Combo65

Sql = "ALTER TABLE " & [tbl] & " ADD COLUMN " & [col] & " Text(128) "
rtl = MsgBox("ADD " & col & " TO " & tbl & " ?", vbYesNo + vbExclamation, "PLEASE CONFIRM")
MsgBox Sql
Select Case rtl
        Case 6
        DoCmd.RunSQL Sql
        Case 7
        End
        End Select

End Sub

Im taking the table Name and the column Name from a Combo box.

how can I rectify this error??

Thanks!!

[westconn1]

i guess if the new column name could contain a space, such an error could occur

are you sure the [] are required /allowed, i would think they should be omitted

[saranmc]

i checked westconn1... no difference with or without []...

but you are partly right, the error occurs due to the spaces.
i checked with a column Name without spaces and it works!
but with spaces or Special characters it does not.

so what can be done?? the new Name could contain spaces...

[vbfbryce]

From MS:

Names of fields, controls, and objects in Microsoft Access:

Can be up to 64 characters long.

Can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`), and brackets ([ ]).

Can't begin with leading spaces.

Can't include control characters (ASCII values 0 through 31).

Can't include a double quotation mark (") in table, view, or stored procedure names in a Microsoft Access project.

Although you can include spaces in field, control, and object names, most examples in the Microsoft Access documentation show field and control names without spaces because spaces in names can produce naming conflicts in Microsoft Visual Basic for Applications in some circumstances.

[westconn1]

it is better to eliminate spaces or replace spaces with underscore, but you can try enclosing field names that contain spaces within single quotes

[IkkeEnGij]

try
"ALTER TABLE " & "[" & tbl & "]" & " ADD COLUMN "

[saranmc]

yes i tried to enclose them in single quotes but in VBA Editor it does not allow me to,....
the line turns red...

Sql = "ALTER TABLE" & 'tbl' & "ADD COLUMN" & 'col' & text(10)"

the error is with the single quotes

[saranmc]

It still does not work Ikke......

[vbfbryce]

This adds a column named "new column2" for me into table1:

Sub addCol()
    Dim db As Database
    Dim strSQL As String
    Dim colName As String
    
    Set db = CurrentDb
    colName = "'new column2'"

    strSQL = "ALTER TABLE table1 ADD " & colName & " VARCHAR(5) NULL ;"
    
    DoCmd.RunSQL strSQL
    
    Set db = Nothing
End Sub

(which is what Pete said in post #5)

[techgnome]

try
"ALTER TABLE " & "[" & tbl & "]" & " ADD COLUMN "

yes i tried to enclose them in single quotes but in VBA Editor it does not allow me to,....
the line turns red...

Sql = "ALTER TABLE" & 'tbl' & "ADD COLUMN" & 'col' & text(10)"

the error is with the single quotes

It still does not work Ikke......

1) Um... I think you missed it... the quotes were to go around the field name INSIDE THE STRING... not around the variable..
2) don't use tick marks (single quotes) as that denotes a string... not an object name...
3) Square brackets IS what you want but you need to make sure it goes around the object name in the SQL string, NOT AROUND THE VARIABLE...
Ike almost had it, it just needed to be expanded:

"ALTER TABLE " & "[" & tbl & "]" & " ADD COLUMN [" & col & "] Text(128) "

What you're after is a string that ends up looking like this:
ALTER TABLE [tblSome Table] ADD COLUMN [some col] Text(128)

See how the brackets are around the object (table and col name)? that's what you're after.

-tg

[saranmc]

That #7 post was for westconn1 and not Ikke...

Anyways... now it works perfectly... thanks for clearly things up techgnome....

Thanks guys for your valuable ideas...

i move on in my Project, will be back if i have more Trouble..

cheers!!!
saran