Insert the records of a dbf file into a table in MS Access database using Delphi

Question

I am trying to insert the records of a dbf file into a table in a MS Access database that I have already created.

The dbf file's name is tab1.dbf and it has three columns: cl1, cl2, cl3.

The MS Access database name is db1 and it has one table tb2 with three columns: cl1, cl2, cl3.

I have connected Delphi to the MS Access database using ADOConnection1.

To insert the dbf file's records, I have to click in a Button1 with OpenDialog1

The code I use is this :

procedure TForm1.Button1Click(Sender: TObject);
var importdir,ipo : string;

begin
if form1.OpenDialog1.Execute then
begin
importdir:= extractfiledir(form1.OpenDialog1.FileName);
ipo:= form1.OpenDialog1.FileName ;
end;
form1.Edit1.Text:= importdir;
ADOConnection1.Execute('insert into tab2 SELECT * FROM [ database = '+ipo+' ].tab1'  );
end;

but when i execut the form1 i have this error message : name of the file is incorrect

can you help me guys ?

Answer 1

Here after is one simple solution. It simple because it assume the Access database structure is the same as the dBASE structure. You'll get started with this example that you'll adapt to your needs.

procedure TDbfToAccessWithAdoForm.DbfToAccessButtonClick(Sender: TObject);
var
    Fld       : Integer;
    FldValue  : Variant;
    InsertSQL : String;
begin
    ADOConnectionAccess.Connected := TRUE;
    ADOConnectionDbf.Connected    := TRUE;
    ADOQueryDbf.SQL.Text          := 'Select * from Clients';
    ADOQueryDbf.Open;

    // Build the parametrized INSERT statement
    InsertSQL := 'insert into Clients(';
    for Fld := 0 to ADOQueryDbf.FieldCount - 1 do
        InsertSQL := InsertSQL + ADOQueryDbf.Fields[Fld].FieldName + ',';
    // Remove extra coma
    Delete(InsertSQL, Length(InsertSQL), 1);
    InsertSQL := InsertSQL + ') values (';
    for Fld := 0 to ADOQueryDbf.FieldCount - 1 do
        InsertSQL := InsertSQL + ':' + ADOQueryDbf.Fields[Fld].FieldName + ',';
    // Remove extra coma
    Delete(InsertSQL, Length(InsertSQL), 1);
    InsertSQL := InsertSQL + ')';

    while not ADOQueryDbf.Eof do begin
        ADOQueryAccess.SQL.Text := InsertSQL;
        for Fld := 0 to ADOQueryDbf.FieldCount - 1 do begin
            FldValue := ADOQueryDbf.Fields[Fld].Value;
            // Here you can do whatever conversion is required
            if FldValue = Null then begin
                if ADOQueryDbf.FieldDefList[Fld].DataType = ftDateTime then
                    FldValue := 0      // My Access table doesn't like empty datetime
                else
                    FldValue := ' ';   // My Access table doesn't like empty string
            end;
            ADOQueryAccess.Parameters.ParamByName(ADOQueryDbf.Fields[Fld].FieldName).Value := FldValue;
        end;
        ADOQueryAccess.ExecSQL;
        ADOQueryDbf.Next;
    end;

    ADOQueryDbf.Close;
    ADOQueryAccess.Close;
end;

You should add error checking and try/finally or try/except. I let you do it as you do usually.