Add Fields Programmatically

M

MikeBromley

I have a BE "skeleton" database which, for one table, I have added 3 new
Fields. The "skeleton" database is used to create separate physical
databases. There are hundreds of BE database files which now do not have the
3 new Fields in their table. I know I can detect this situation with
tbl.Fields.Count, but how do I add the 3 new Fields using code? I appreciate
your time and help.

Mike Bromley
 
A

Albert D. Kallal

You can do this a number of ways, but I usually do something like:

' add default fields

' check table defaults..and add default Tour Type field...

Set rst = CurrentDb.OpenRecordset("tblDefaults")
On Error GoTo AddDefaultTourType
Temp = rst!DefaultTourType.Name

rst.Close
Set rst = Nothing


Exit Sub


AddDefaultTourType:

rst.Close
GoSub AddDefaultTourTypes

Resume Next

AddDefaultTourTypes:

strToDB = strBackEnd

Set db = OpenDatabase(strToDB)

Set nT = db.TableDefs("tblDefaults")
nT.Fields.Append nT.CreateField("DefaultTourType", dbLong)
nT.Fields.Refresh
db.Close
Set db = Nothing

Set rst = CurrentDb.OpenRecordset("tblDefaults")
Return
 
O

Ofer Cohen

Using the help file on CreateField, you can use that to add fields to a table
in a remote mdb

Function CreateFieldsInRemoteMDB()
Dim dbsMyDB As Database
Dim tdfNew As TableDef

Set dbsMyDB = OpenDatabase("c:\DBName.mdb")

Set tdfNew = dbsMyDB.TableDefs("TableName")

' Create and append new Field objects for the new
' TableDef object.
With tdfNew
' The CreateField method will set a default Size
' for a new Field object if one is not specified.
.Fields.Append .CreateField("TextField", dbText)
.Fields.Append .CreateField("IntegerField", dbInteger)
.Fields.Append .CreateField("DateField", dbDate)
End With

End Function
 
M

MikeBromley

Thank you very much, Albert for the super quick reply. I have it coded and
it works great. It was the CreateField that I didn't know about, again,
thank you very much.

Mike Bromley
 
M

MikeBromley

Thank you very much Ofer for your super quick reply. I have it coded and it
works great. It was the CreatField taht I didn't know existed. Again,
Thanks a lot for your help.

Mike Bromley
 
A

Aaron Kempf

this method has been depecrated

you should use ALTER TABLE MYTABLE ADD COLNAME DATATYPE
 
A

Aaron Kempf

again, use of DAO is ridiculous in this situation

why would you need to use 2 object libraries to do what I can do in a
DEFAULT BUILT-IN LIBRARY?

Jet hasn't been included with Office, MDAC or Windows for a decade
 
M

magicdds-

Another wonderful explaination. Thanks Albert.

I do have one question though. Microsoft explains in the Access help how to
add fields to a table, immediately after creating the new table:

nT.Fields.Append .CreateField("DefaultTourType", dbLong)

They don't explain, as you have done, how to add fields to an existing table:

nT.Fields.Append nT.CreateField("DefaultTourType", dbLong)

My question is, Where do you learn this stuff from if Microsoft doesn't
publish it in their help section or knowledge base?

Thanks
Mark
 
Top