By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,804 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,804 IT Pros & Developers. It's quick & easy.

Add new field in the table using macro

P: 2
Hello All,
How to add a new field in the table using vba or macro. Pls. help.
Dec 4 '07 #1
Share this Question
Share on Google+
2 Replies

P: 30
I normally use VBA and DAO to do this.

CreateField Method

Creates a new Field object (Microsoft Jet workspaces only).


Set field = object.CreateField (name, type, size)

The CreateField method syntax has these parts.

Part Description
field An object variable that represents the Field object you want to create.
object An object variable that represents the Index, Relation, or TableDef object for which you want to create the new Field object.
name Optional. A Variant (String subtype) that uniquely names the new Field object. See the Name property for details on valid Field names.
type Optional. A constant that determines the data type of the new Field object. See the Type property for valid data types.
size Optional. A Variant (Integer subtype) that indicates the maximum size, in bytes, of a Field object that contains text. See the Size property for valid size values. This argument is ignored for numeric and fixed-width fields.


You can use the CreateField method to create a new field, as well as specify the name, data type, and size of the field. If you omit one or more of the optional parts when you use CreateField, you can use an appropriate assignment statement to set or reset the corresponding property before you append the new object to a collection. After you append the new object, you can alter some but not all of its property settings. See the individual property topics for more details.

The type and size arguments apply only to Field objects in a TableDef object. These arguments are ignored when a Field object is associated with an Index or Relation object.

If name refers to an object that is already a member of the collection, a run-time error occurs when you use the Append method.

To remove a Field object from a Fields collection, use the Delete method on the collection. You can't delete a Field object from a TableDef object's Fields collection after you create an index that references the field.
Example code from Access VBA Helpfile

Expand|Select|Wrap|Line Numbers
  1. CreateField Method Example
  3. This example uses the CreateField method to create three Fields for a new TableDef. It then displays the properties of those Field objects that are automatically set by the CreateField method. (Properties whose values are empty at the time of Field creation are not shown.)
  5. Sub CreateFieldX()
  7.     Dim dbsNorthwind As Database
  8.     Dim tdfNew As TableDef
  9.     Dim fldLoop As Field
  10.     Dim prpLoop As Property
  12.     Set dbsNorthwind = OpenDatabase("Northwind.mdb")
  14.     Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")
  16.     ' Create and append new Field objects for the new 
  17.     ' TableDef object.
  18.     With tdfNew
  19.         ' The CreateField method will set a default Size
  20.         ' for a new Field object if one is not specified.
  21.         .Fields.Append .CreateField("TextField", dbText)
  22.         .Fields.Append .CreateField("IntegerField", dbInteger)
  23.         .Fields.Append .CreateField("DateField", dbDate)
  24.     End With
  26.     dbsNorthwind.TableDefs.Append tdfNew
  28.     Debug.Print "Properties of new Fields in " & tdfNew.Name
  30.     ' Enumerate Fields collection to show the properties of 
  31.     ' the new Field objects.
  32.     For Each fldLoop In tdfNew.Fields
  33.         Debug.Print "  " & fldLoop.Name
  35.         For Each prpLoop In fldLoop.Properties
  36.             ' Properties that are invalid in the context of
  37.             ' TableDefs will trigger an error if an attempt
  38.             ' is made to read their values.
  39.             On Error Resume Next
  40.             Debug.Print "    " & prpLoop.Name & " - " & _
  41.                 IIf(prpLoop = "", "[empty]", prpLoop)
  42.             On Error GoTo 0
  43.         Next prpLoop
  45.     Next fldLoop
  47.     ' Delete new TableDef because this is a demonstration.
  48.     dbsNorthwind.TableDefs.Delete tdfNew.Name
  49.     dbsNorthwind.Close
  51. End Sub
Dec 4 '07 #2

Expert 2.5K+
P: 3,532
As evidenced by Coach's answer, it can be done, but that doesn't necessarily mean it should be done! Why do you want to do this?

Welcome to TheScripts!

Linq ;0)>
Dec 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.