Basically what we want to do is to copy several tables from DBDemos to our aboutdelphi.mdb Access database. The structure of the aboutdelphi.mdb is discussed in the first chapter.

Let's do it step by step:
GUI
Start Delphi - this creates a new project with one blank form. Add two Buttons, one ComboBox and one Memo component. Next, add a TTable, TADOTable, TADOConnection and a TADOCommand component. Use the Object Inspector to set the following properties (leave all the other properties as they are - for example the Memo should have the default name: Memo1):

Button1.Caption = 'Construct Create command'
Button2.Caption = 'Create Table and copy data'
ComboBox.Name = cboBDETblNames;
 
//as described in the second chapter
ADOConnection1.ConnectionString = ...
TADOTable.Name = ADOTable
ADOTable.Connection = ADOConnection1
TADOCommand.Name = ADOCommand
ADOCommand.Connection = ADOConnection1
TTable.Name = BDETable
BDETable.DatabaseName = 'DBDEMOS'

Code
To retrieve a list of the tables associated with a given database (DBDEMOS) we use the next code (OnCreate for the form):

procedure TForm1.FormCreate(Sender: TObject);
begin
 Session.GetTableNames('DBDEMOS',
                       '*.db',False, False,
                       cboBDETblNames.Items);
end;

When you start the project the ComboBox has all the (Paradox) table names in the DBDEMOS alias directory. In the code that follows, we'll pick the Country table.

The next task is to create a CREATE TABLE DDL statement. This gets done in the 'Construct Create command' button's OnClick procedure:

procedure TForm1.Button1Click(Sender: TObject);
//'Construct Create command' button
var i:integer;
    s:string;
begin
 BDETable.TableName:=cboBDETblNames.Text;
 BDETable.FieldDefs.Update;

 s:='CREATE TABLE ' + BDETable.TableName + ' (';
 with BDETable.FieldDefs do begin
  for i:=0 to Count-1 do begin
   s:=s + ' ' + Items[i].Name;
   s:=s + ' ' + AccessType(Items[i]);
   s:=s + ',';
  end; //for
  s[Length(s)]:=')';
 end;//with

 Memo1.Clear;
 Memo1.lines.Add (s);
end;

The above code simply parses the field definitions for the selected table (cboBDETblNames) and generates a string that will be used by the CommandText property of the TADOCommand component.

For example, when you select the Country table the Memo gets filled with the next string:

CREATE TABLE country (
  Name TEXT(24),
  Capital TEXT(24),
  Continent TEXT(24),
  Area FLOAT,
  Population FLOAT
)

And finally, the code for the 'Create Table and copy data' button drops a table (DROP..EXECUTE), creates a table (CREATE..EXECUTE), and then copies data into the new table (INSERT...POST). Some error handling code is provided, but the code will fail if, for example, the (new) table does not already exist (since it first gets dropped).

procedure TForm1.Button2Click(Sender: TObject);
//'Create Table and copy data' button
var i:integer;
    tblName:string;
begin
 tblName:=cboBDETblNames.Text;

//refresh
 Button1Click(Sender);

//drop & create table
 ADOCommand.CommandText:='DROP TABLE ' + tblName;
 ADOCommand.Execute;

 ADOCommand.CommandText:=Memo1.Text;
 ADOCommand.Execute;

 ADOTable.TableName:=tblName;

//copy data
 BDETable.Open;
 ADOTable.Open;
 try
  while not BDETable.Eof do begin
   ADOTable.Insert;
   for i:=0 to BDETable.Fields.Count-1 do begin
    ADOTable.FieldByName
   (BDETable.FieldDefs[i].Name).Value :=
      BDETable.Fields[i].Value;
   end;//for
   ADOTable.Post;
   BDETable.Next
  end;//while
 finally
  BDETable.Close;
  ADOTable.Close;
 end;//try
end;

That's it. Check out your Access database now...voila there is a Country table with all the data from DBDEMOS.

Now you can port all your Paradox tables to Access (download code). Few questions, however, stay unanswered. The first one is: how to add index definitions (CREATE INDEX ON ...) to tables. The second one is: how to create an empty Access database. I'll leave those (and others you can think of) for the Forum or for some future article - Chapter 13 precisely.

   To the next chapter
If you need any kind of help so far, please post to the Delphi Programming Forum where all the questions are answered and beginners are treated as experts.

First page > Focusing on the TADOCommand components and using the SQL DDL language. > Page 1, 2