HOW TO Append a new field in an existing Table ???

[marco02]

Hi I'm trying to append a NEW FIELD on an existing table.
My table has 2 fields and I want to append a 3rd one.
With the code below my field DOES NOT get added to my table.

Dim fld As DAO.Field
Dim tbl As DAO.TableDef

Set tbl = CurrentDb.CreateTableDef("Table1&quot
Set fld = tbl.CreateField("FieldName", dbText, 30)
tbl.Fields.Append fld

... and what next ???
(CurrentDb.TableDefs.Append tbl does not work)
... how do i get my field REALLY appended to my table ?
... is it an update issue ?
(Refresh does not change anything though)

thanks a lot!

marco

[message to CClint : first congrats for the TipMaster price. Then, you sent me to a thread about this pb 2 weeks ago but ... i couldn't find the answer in it, could you write me what your idea was?]

 

[NickISD]

Marco,
Try this as an execute query to the database,

Dim cSql As String
cSql = "ALTER TABLE Table1 ADD COLUMN FieldName Text(20)"
CurrentDB.Execute(cSql)

The execute may not be quite right but the SQL is correct.

Hope this helps

Regards, Nick

 

[Schroeder]

NickISD's solution is probably best but as for what you were trying to do... you don't want to Set tbl = CurrentDb.CreateTableDef("Table1&quot. That says to create a new table. You want to Set tbl = CurrentDb.TableDefs("Table1&quot. This sets tbl to an existing table. Also, don't try to append the table to CurrentDb. It's already there.

 

[HermanvLimbeek]

Create tables, fields and set attributes, see faq222-885.