Link to home
Start Free TrialLog in
Avatar of Tammi
Tammi

asked on

Help with Databases and multiple tables

I haven't done much development with databases so I need some help.

I have two Access databases.   I need to look in the registry for a certain value
sLocale := jciRegsUtil.GetRegStringValue(TOOL, 'AimVer', 'LocaleName', '');
and search my Logistics table(Warehouse database) for records with sLocale in the Area field and add these records to my Contact table (Address database).
                   
I was going to try using the BatchMove component with the batAppend, but I believe that would append the entire second table.  Is there a way to use a select statement in a query and then the BatchMove to add the records from the select statement?

 Any help/code  would be appreciated :)

Avatar of trex_fire
trex_fire

No but you can cycle through your query and insert your rows in your other table by putting another query with parameters.  Like:

MyQuery.Open;
While not MyQuery.EOf do begin
  InsertQuery.ParamByName('Param1').AsSomething := ...;
  InsertQuery.ParamByName('Param2').AsSomething := ...;
  ...
  InsertQuery.ExecSQL;
  MyQuery.Next;
end;

In InsertQuery, your SQL will look like this:

Insert into TableName
  (Field1,Field2,...)
  values (:Param1,:param2,...)

Hope it helps  ;)

T-Rex
Avatar of Tammi

ASKER

I actually got the BatchMove function to work by specifying my query as the source.  I'm still having problems getting the variable (Param) to work.  When I run the code below, I get a error "Parameter Param1 not found".  Any ideas??

begin
      qryArea := TQuery.Create(nil);
      qryArea.DatabaseName := 'dbnWarehouse';
      Param1 := TParam.Create(nil);
      qryArea.Params.AddParam(Param1);
      qryArea.ParamByName('Param1').Value := sLocale;
      qryArea.SQL.Text := 'SELECT * FROM Logistics WHERE Area = :Param1';
      qryArea.ExecSQL;
      BtchMvBradford.Source := qryArea;
      BtchMvBradford.Execute;
      qryArea.Free;
    end;

Thanks!!

Tammi
ASKER CERTIFIED SOLUTION
Avatar of trex_fire
trex_fire

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Tammi

ASKER

I ended up using the TQuery component and putting the SQL in the properties.  I had tried this previously but kept getting errors.  Discovered that I should have defined the DataSource, just the Database.  I used the following code:

begin
      qryArea.ParamByName('Param1').Value := sLocale;
      qryArea.Open;
      BtchMvBradford.Source := qryArea;
      BtchMvBradford.Execute;
      qryArea.Close;
      BtchMvBradford.Free;
    end;

Thanks for your help!!

Tammi