Copy MS SQL table to Paradox table using ADO?

TBatchMove but it would require the BDE.

Now you could do this with a DTS package if you setup an ODBC connection to your
Paradox data. Do you know how to do DTS using SQL2K? If so, I can show you how
to run the package from Delphi.

I've used the interactive SQL Server Enterprise manager tools to export data to paradox format, but not a saved DTS package.

When finishing building the DTS package, that's what you are doing when using the DTS Wizard,
you have an option to run immediately or save to a file. I just tested this.
I was only able to do if I used a FileDSN but I did not create an ODBC DSN using the ODBC
Administrator, I created the FileDSN in the DTS Wizard when selecting the destination.


uses .., COMObj; {for the CreateOLEObject call}

function ExecDTS(const APackageName: String): Boolean;
var
   pVarHost: OleVariant;
   EmptyStr: OleVariant;
   pkg:      OleVariant;
   i:        Integer;
begin
   pkg := CreateOLEObject('DTS.Package');
   try
     EmptyStr := WideString('');
     pkg.LoadFromSQLServer('yourdbservername',
                           'yourdbuserid',
                           'yourdbpassword',
                           0,
                           EmptyStr,
                           EmptyStr,
                           EmptyStr,
                           WideString(APackageName),
                           pVarHost);
     pkg.FailOnError := True;
     for i := 1 to pkg.Steps.Count do
       pkg.Steps.Item(i).ExecuteInMainThread := True;
     pkg.Execute;
     Result := True;
   finally
     ShowMessage('Done...');
     pkg.UnInitialize;
   end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ExecDTS('Vendors');
end;

I imported the Vendors Data from the vendors table in DBDEMOS into SQL2K. Then I
deleted all the vendor data from the paradox table, needed to delete items and parts, too, BTW.

Then I create the DTS package using the DTS Wizard and saved as SQL Server package named
Vendors.

You can save as a file but you need to use LoadFromFile, I think, to load it.

Good Luck.

Thanks, Eddie.  I created the DTS package using Enterprise Manager.  When I execute it interactively, by selecting the DTS name and selecting Execute, it works fine and creates the new table.  I delete the table is created, then try t using the code you supplied and it gets an error:  Operation not supported on a Paradox table with no primay key.

Any idea why?

I guess your SQL server table has an index and your Paradox one doesn't?
Do you have an autoinc field?

It worked fine with the Vendors table from DBDEMOS.

Hi Eddie,

Very frustrating that the DTS does not run from the delphi program but does if executed from Enterprise manager.  Can you tell me what these
parameters are, and/or where I can find the documentation for them?

                           0,
                           EmptyStr,
                           EmptyStr,
                           EmptyStr,
                           WideString(APackageName),
                           pVarHost);

OK, you have to save a a structured storage file (.DTS) the option is in the types.
It works just like if you ran it from the wizard.

Okay, I used the code below and got the same error regarding a paradox key.  Might be a driver issue?

   pkg.LoadFromStorageFile('\\Laptop\LapTop C\MTC\Public\MTCData\MTSGWebTerms.dts',
                             'password',
                             EmptyStr,
                             EmptyStr,
                             EmptyStr,
                             EmptyStr);

May be. The ODBC only uses Paradox 4 or 5.
If your table is a later version, it may not work.

Maybe you need to update your BDE, which would
update the Paradox ODBC drivers.

http://info.borland.com/devsupport/bde/bdeupdate.html

Thanks for all your help.  I'll have to play with it when I have more time.  I can use it if I export to a CSV, then import it on the Paradox end.  A little bit clumsy, but it works okay.