Add Field to Existing Table 3

[Telsa]

How do I add, in VBA, a field to an existing Table. Can't seem to find any info on this. Is it through the recordset??

Mary

 

[Gzep]

Hi.

I don't have a total solution, but look at Currentdb.TableDefs.Createfield.

This is used with NEW tables, to create fields before the table is appended to the tabledefs collection.

My approach would be to create a new tabledef, add the fields, setup relationships and copy any old data in. Autonums could be a problem though.

then delete the old table, and rename the new one.

no data, and no relationships - just delete the old table and create the new one in its place.

Gzep.

 

[Gzep]

Hi.

Cancel that last (workable but hard |-I) try these 2 lines!

CurrentDb.TableDefs("A&quot.Fields.Append CurrentDb.TableDefs("A&quot.CreateField("Fred", dbLong)

CurrentDb.TableDefs.Refresh

Gzep.
don't forget to vote, if you like this answer... :

 

[Aivars]

Hi!

This procedure is ready for use. You may copy following codes into any module of your database and run procedure like example.

Example:
call FieldCreateInExistTable(currentdb,currentdb.TableDefs("Test&quot,"NewField",dbLong,,2,"NewField&quot

Sub FieldCreateInExistTableUni(dbs As Database, tdf As TableDef, strFldName As String, Optional lngType As Long = dbText, Optional intSize As Integer = 0, Optional intPosition As Integer = 0, Optional strIdxName As String = "&quot
'This procedure create new field in the existing table,
'create new index if it is need
'and set the position of new field in the table.
'If you'll omit optional parameters (lngType or/and intSize,
'its will be created new field with type "Text" and size=50.
'If you'll omit optional parameter index for this field
'index will be not created
Dim fld As Field
Dim idx As Index
Dim prp As Property
Dim i As Integer

'Create new field
If lngType = dbText Then
If intSize = 0 Then
intSize = 50
End If
Set fld = tdf.CreateField(strFldName, lngType, intSize)
ElseIf lngType <> 0 Then
If intSize = 0 Then
Set fld = tdf.CreateField(strFldName, lngType)
End If
Else
Set fld = tdf.CreateField(strFldName)
End If

tdf.Fields.Append fld
tdf.Fields.Refresh

'Change new field's position in the table
If intPosition >= tdf.Fields.Count Then
intPosition = 0
End If
If intPosition <> 0 Then
Set prp = fld.Properties(&quot;OrdinalPosition&quot
prp.Value = intPosition
End If

'Create index of new field
If strIdxName <> &quot;&quot; Then
Set idx = tdf.CreateIndex(strIdxName)
With idx
.Fields.Append .CreateField(strFldName)
End With
tdf.Indexes.Append idx
tdf.Indexes.Refresh
End If
End Sub

Best regards!
Aivars

 

[Francescina]

I always use the SQL-DDL-procedures to modify the tables of my backends because it is simple, fast and &quot;small&quot;. Have a look at the MS Knowledgebase for details.