# MS Access sql to ADD multiple COLUMN



## draceplace (Jun 8, 2001)

Quck and dirty please! What is the syntax for adding multiple columns to a table.

*This works:*
Private Sub cmd_InCareOf_Click()
On Error GoTo Err_cmd_InCareOf_Click
sqlStr = "ALTER TABLE [Sheet in Net]" _
& "ADD COLUMN InCareOf Text(25);"

DoCmd.RunSQL sqlStr

Exit_cmd_InCareOf_Click:
Exit Sub
Err_cmd_InCareOf_Click:
MsgBox Err.Description
Resume Exit_cmd_InCareOf_Click
End Sub
______________________________________________________

*This gets a Syntax in field definition #3292 Error*

Private Sub cmd02_cname_Click()
On Error GoTo Err_cmd02_cname_Click
Dim stDocName As String
stDocName = "sheet_net"
MsgBox "Adding Fields to- " & stDocName, vbOKCancel

sqlStr = "ALTER TABLE " & stDocName _
& " ADD Column Cname Text(30), " _
& "ADD Column Addr1 Text(35), " _
& "ADD Column Addr2 Test(35), " _
& "ADD Column CityStZip Text(50);"
MsgBox sqlStr
DoCmd.RunSQL sqlStr

Exit_cmd02_cname_Click:
Exit Sub

Err_cmd02_cname_Click:
MsgBox Err.Description & CrLf & Err.Number
Resume Exit_cmd02_cname_Click
End Sub

Thanks for your time!


----------



## OBP (Mar 8, 2005)

If you are trying to add Fields to a Table it is normal to use VBA to add them to the TableDef in the Tables Collection rather than SQL.


----------



## draceplace (Jun 8, 2001)

I appreciate the tip OBP!!
I did it with the TableDef (like below). I'm still curious about the correct SQL syntax

If RunThis = vbOK Then
Set fld = tbl.CreateField("InCareOf", dbText, 30)
tbl.Fields.Append fld
fld.Properties("Required").Value = False
fld.Properties("AllowZeroLength").Value = True


----------

