Add Fields Programmatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
this method has been depecrated

you should use ALTER TABLE MYTABLE ADD COLNAME DATATYPE
 
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
 
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
 
Back
Top