asked on

Adding Fields to Existing table

Is it possible to add new fields to an existing table at runtime. I have looked at help files regarding fielddefs.add, but it seems to indicate this is for use only on new table creation.

I have an existing database application, but I want to be able to add new functionality requiring new fields, but I don't want to erase existing data.

ASKER CERTIFIED SOLUTION

BoRiS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dejohn99

BoRiS' answer is incomplete - it will add a new field to the internal representation of the dataset, however it will not actually add it to the tables!

The complete algorithm should be

1. Rename the existing table to something innocuous like "WORKING"

2. Create a new TTable object with the appropriate paths, names, etc.

3. Copy the field structure of the working table structure to the field structure of the new TTable object

4. Call TTable.Create and TTable.Open with the new TTable object

5. Either use BatchMove or a "While not WorkingTable.EOF" loop to read records from the working table and insert them into the new table. Ensure that your field transfer method uses fields by name

6. Close both the new table and the working table

7. Make modifications to the old TTable object as noted by BoRiS

8. Re-open the original Table object (which now sees the new field)

Procedure btnAddFieldClick (Sender);
{
Table1 - the table we are changing
WorkingTable - the internal representation of the table we are using to work with
NewTable - The output of the final (revised) table structure
}
var
WorkingTable, NewTable: TTable;
i: Integer;
begin
Screen.Cursor := crHourGlass;
Table1.Close;
WorkingTable := TTable.Create (Self);
NewTable := TTable.Create (Self);
try
with WorkingTable do begin
DatabaseName := Table1.DatabaseName;
TableName := Table1.TableName;
Open;
RenameTable ('WORKING');
end;

with NewTable do begin
DatabaseName := Table1.DatabaseName;
TableName := Table1.TableName;
for i := 0 to WorkingTable.FieldCount-1 do begin
TFieldDef.Create (FieldDefs,
       WorkingTablel.Fields[i].FieldName,
       WorkingTable.Fields[i].DataType,
       WorkingTable.Fields[i].Size,
       WorkingTable.Fields[i].Required,
i);
end; {for}
TFieldDef.Create (FieldDefs,
'NewField',
dtString,
100,
false,
FieldCount);

CreateTable;
WorkingTable.First;
while not WorkingTable.EOF do begin
Insert;
for i := 0 to WorkingTable.FieldCount-1 do begin
FieldByName (WorkingTable.Fields[i]).FieldName) .Value := WorkingTable.Fields[i].Value;
end;
Post;
WorkingTable.Next;
end;
Close;
end; {with}
WorkingTable.Close;

finally
WorkingTable.Destroy;
NewTable.Destroy;
Table1.Open;
Screen.Cursor := crArrow;
end;
end;

dejohn99

oops! I forgot to add the new field to the existing TTable object.

Use BoRiS's code for that final step.

JohnE

ASKER

Thanks, we dejohn99's comments, it turned out great!