G
Guest
Hi there,
is there a way to programatically create new fields in a table that already exists?
is there a way to programatically create new fields in a table that already exists?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
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
G
Guest
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.
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.
G
Guest
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
TIA,
Djoezz
J
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.