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.
JohnEAsked:
Who is Participating?
 
BoRiSCommented:
JohnE

her is one way to do just that, this addes a stringfiled to the dataset...

procedure TForm1.AddFieldButClick(Sender: TObject);

var

  NewField: TStringField;

begin

  Table1.Close;

  NewField := TStringField.Create(Self);

  NewField.FieldName := 'NEW_FIELD';

  NewField.Name := Table1.Name + NewField.FieldName;

  NewField.Index := Table1.FieldCount;

  NewField.DataSet := Table1;

  Table1.FieldDefs.UpDate;

  Table1.Open;

end;

Later
BoRiS
0
 
dejohn99Commented:
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;
0
 
dejohn99Commented:
oops!  I forgot to add the new field to the existing TTable object.

Use BoRiS's code for that final step.
0
 
JohnEAuthor Commented:
Thanks, we dejohn99's comments, it turned out great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.