creating fields in a table

[Carlee]

Hi there,

is there a way to programatically create new fields in a table that already exists?

 

[Tim Ferguson]

is there a way to programatically create new fields in a table that
already exists?

Look up help for ALTER TABLE and in particular ADD COLUMN

Hope that helps


Tim F

 

[Steve M]

Here is a an example of adding a field to an existing table using DAO

Public Function AddMyField()
Dim dbs As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field

'set a reference to the database you want to change
Set dbs = CurrentDB
'you could use DBEngine.OpenDatabase(PathToFile) to connect to a
'database other than the one you are running your code in.

'set a reference to the table you want to change
Set tbl = dbs.TableDefs("MyTable")

'create a new field object
Set fld = tbl.CreateField("MyNewField", dbLong)
'dbLong = Long Integer
'dbText = Text
'dbMemo = Memo
'dbDate = Date/Time
' look up DataTypeEnum in the Object Browser for a complete list

'add the field to the table
tbl.Fields.Append fld

'now change other properties of your new field
fld.Required = True

'clean up
Set fld = Nothing
Set tbl = Nothing
Set dbs = Nothing
End Function

Steve M.

 

[Djoezz]

I 've got similar problem with Carlee. I'm using a crosstab query that miss one field because there is no data in it that's why I have to add it from VBA.
TIA,
Djoezz

 

[John Vinson]

I 've got similar problem with Carlee. I'm using a crosstab query that miss one field because there is no data in it that's why I have to add it from VBA.

You can use the "Header" property of the crosstab query to force
inclusion of all columns you want, whether or not they have data. No
VBA required.