如果用VBA在原有表A1中创建新的字段?

wsylqy

如果用VBA在原有表A1中创建新的字段?

大熊

dao或者ADO，查旧贴。

Roadbeg

请参考:

CreateField Method

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

Syntax

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.


Remarks

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.

大熊

或可查alter table.SQL语言

Roadbeg

另请参考:

Append Method
      

Adds a new DAO object to a collection.

Syntax

VOIDcollection.Append( class& object );

The Append method syntax has these parts.

Part Description
collection An object variable that represents any collection that can accept new objects (for limitations, see the table at the end of this topic).
class& Reference to an object variable to be appended, which must be of the same class as the elements of collection.


Remarks

You can use the Append method to add a new table to a database, add a field to a table, and add a field to an index.

The appended object becomes a persistent object, stored on disk, until you delete it by using the Delete method. If collection is a CdbWorkspaces collection (which is stored only in memory), the object is active until you remove it by using the Close method.

The addition of a new object occurs immediately, but you should use the Refresh method on any other collections that may be affected by changes to the database structure.

If the object you are appending isn’t complete (such as when you haven’t appended any CdbField objects to a Fields collection of an CdbIndex object before it’s appended to an Indexes collection) or if the properties set in one or more subordinate objects are incorrect, using the Append method causes an error. For example, if you haven’t specified a field type and then try to append the CdbField object to the Fields collection in a CdbTableDef object, using the Append method triggers a run-time error.

The following table lists some limitations of the Append method. The object in the first column is an object containing the collection in the second column. The third column indicates whether you can append an object to that collection (for example, you can never append a CdbContainer object to the Containers collection of a CdbDatabase object).

Object Collection Can you append new objects?
CdbDBEngine Workspaces Yes
CdbDBEngine Errors No. New Error objects are automatically appended when they occur.
CdbWorkspace Connections No. Using the OpenConnection method automatically appends new objects.
CdbWorkspace Databases No. Using the OpenDatabase method automatically appends new objects.
CdbWorkspace Groups Yes
CdbWorkspace Users Yes
CdbConnection QueryDefs No. Using the CreateQueryDef method automatically appends new objects.
CdbConnection Recordsets No. Using the OpenRecordset method automatically appends new objects.
CdbDatabase Containers No
CdbDatabase QueryDefs Only when the CdbQueryDef object is a new, unappended object created with no name. See the CreateQueryDef method for details.
CdbDatabase Recordsets No. Using the OpenRecordset method automatically appends new objects.
CdbDatabase Relations Yes  
CdbDatabase TableDefs Yes
CdbGroup Users Yes
CdbUser Groups Yes
CdbContainer Documents No
CdbQueryDef Fields No
CdbQueryDef Parameters No
CdbRecordset Fields No
CdbRelation Fields Yes  
CdbTableDef Fields Only when the Updatable property of the CdbTableDef object is set to True, or when the CdbTableDef object is unappended.
CdbTableDef Indexes Only when the Updatable property of the CdbTableDef is set to True, or when the CdbTableDef object is unappended.
CdbIndex Fields Only when the CdbIndex object is a new, unappended object.
CdbDatabase, CdbField, CdbIndex, CdbQueryDef, CdbTableDef Properties Only when the CdbDatabase, CdbField, CdbIndex, CdbQueryDef, or CdbTableDef object is in a Microsoft Jet workspace.
CdbDBEngine, CdbParameter, CdbRecordset, CdbWorkspace Properties No

Roadbeg

举个实例:
此例在 表 buffer 中加入一日期时间型字段 new

private sub AddFiled()
Dim tb As DAO.TableDef
Set tb = DBEngine.Workspaces(0).Databases(0).TableDefs("buffer")
tb.Fields.Append tb.CreateField("new", dbDate)
end sub

fengyeyouni

看不懂