How to create a TDataSet Lookup Field at runtime?

Question

I'm using TADODataSet (D7).
I do not create persistent fields in design-mode with my DataSet.

dsItems.CommandText := 'select ID, ItemName, UserIDCreate from Items';
dsUsers.CommandText := 'select  ID, LoginName from Users'; // lookup dataset

If I double-click on dsItems and "Add all fileds" and then click "New filed" and define my lookup Field, everything is working fine. The resulting dsItems should contain: ID, ItemName, UserIDCreate, LoginName_Lookup

How can I avoid doing all this in design-time and add a Lookup Field after/or before (? not sure) the DataSet is opened.

In other words: How do I emulate "Add all fileds" and then "New filed" to add lookup filed in run-time?

---

NOTE: I get an Exception when running John's code from the IDE. The Exception occurs when trying to open the DataSet: EOleException with message 'An unknown error has occured'.

function TCustomADODataSet.LocateRecord (ADODB.pas) in line if FieldCount = 1 then FLookupCursor.Find...

I'm accepting the answer becouse the complied program is working.
It would be nice if someone could verify getting (or not) an Exception when runing form inside the IDE.

Answer 1

The dataset cannot be opened to add a lookup field at runtime.

You'll also need to add whatever other fields you'll need to access as persistent fields as well, otherwise, they will be inaccessible. The procedures below should work. However, I recommend that if you can, use queries and join your tables - it's a lot less coding, and much cleaner in my opinion.

procedure CreatePersistentFields(ADataset: TDataset);
Var
  i: Integer;
Begin
  ADataset.FieldDefs.Update;
  for I := 0 to ADataset.FieldDefs.Count - 1 do
     if ADataset.FindField(ADataset.FieldDefs[i].Name) = Nil then
       ADataset.FieldDefs.Items[i].CreateField(ADataset);
End;

Procedure CreateLookupField( ATable: TDataSet; AFieldName: String; ALookupDataset:          TDataset; AKeyfields: String; ALookupKeyfields: String; ALookupResultField : String);
Var
  I : Integer;
  NewField : TField;
Begin
  with ATable do begin
    if FieldDefs.Updated = False then
      FieldDefs.Update;
    If FindField(AFieldName) = Nil then
    begin
      NewField := TStringField.Create(ATable);
      NewField.FieldName := AFieldName;
      NewField.KeyFields := AKeyFields;
      NewFIeld.LookupDataSet := ALookupDataset;
      NewField.LookupKeyFields := ALookupKeyFields;
      NewField.LookupResultField := ALookupResultField;
      NewField.FieldKind := fkLookup;
      NewField.Dataset := ATable;
    end;
  end;
End;

procedure TForm1.Button1Click(Sender: TObject);
begin
  AdoDataset1.Close;
  CreatePersistentFields(TDataset(AdoDataset1));
  CreateLookupField(TDataset(AdoDataset1), 'EmployeeNameLook', TDataset(EmployeeTable), 'EmployeeID', 'EmployeeID', 'EmployeeName');
end;