Creating Tables MS ACCESS with FIREDAC

Question

I am getting an error when creating tables in MS ACCESS when using FireDAC connection (Delphi Xe6).
The A new database file is created and 3 tables are created either using TFDCommand or TFDQuery, the tables do get created, but I always get an error 'Table already exists'. Any suggestions to stop the error being raised.
I do encompass the code in a try...Except routine.

Code:

        FDCommand1.CommandText.Text := 'CREATE Table [SampleData] ' +
                  '([SampleID] INTEGER Primary Key NOT NULL, ' +
                  ' [RiskIDX] INTEGER NULL,' +
                  ' [RefSampleID] Char(10) NULL,' +
                  ' [SerialNumber] Char(60) NULL,' +
                  ' [TestDate] DateTime NULL,' +
                  ' [ResampleDate] DateTime NULL,' +
                  ' [SampleDate] DateTime NULL,'  +
                  ' [SamplingPoint] Char(60) NULL,' +
                  ' [LabTech] Char(60) NULL, SampledBy Char(60) NULL,' +
                  ' [Status] Char(60) NULL,' +
                  ' [Source] Char(60) NULL,' +
                  ' [Condition] Char(60) NULL,' +
                  ' [PlotPnt] YESNO,' +
                  ' [Comments] Memo'  +
                  ' CONSTRAINT FKSampleId Foreign Key SerialNumber
                    References AssetInfo SerialNo);';

OR

FDQuery1.SQL.Text := Memo1.text; //Create table SQL command
FDQuery1.Prepare;
FDQuery1.Execute(200,0);

With some experimentation I have found that the FDCommand and FDQuery components do not work in this instance; not even if you check before hand that the tables don't exist. (I not too sure why - but it seems to me that the SQL command is sent twice hence the table exists error). However, the FDConnection.ExecSQL does work. Tried and tested (creates 3 tables with the CONSTRAINTS as I needed it to).

Regards TomD

Related http://stackoverflow.com/q/909371/960757. – TLama Jul 28 '15 at 08:58 — TLama, Jul 28 '15 at 08:58
@kobik yes, of course, Today is not my day:) Thank you. – Val Marinov Jul 28 '15 at 13:31 — Val Marinov, Jul 28 '15 at 13:31

Gianluca Colombo · Answer 1 · 2015-07-28T12:14:45.360

0

I didn't test this code, but it should works in your porpouse.

function tableExists(TableName:String;Connection:TFDConnection):Boolean;
var
  str: TStringList;
begin
  str := TStringList.Create;
  try
    Connection.GetTableNames('','','',str);
    result := str.IndexOf(TableName) <> -1;
  finally
    str.Free;
  end;
end;

usage:

procedure TForm2.Button1Click(Sender: TObject);
begin
  if not tableExists('SampleData',FDConnection1) then
    begin
      //create table here
    end;
end;

edited Jul 28 '15 at 12:14

answered Jul 28 '15 at 10:08

Gianluca Colombo

717
17
38

1

Check the [`GetTableNames`](http://docwiki.embarcadero.com/Libraries/XE8/en/FireDAC.Comp.Client.TFDCustomConnection.GetTableNames) method parameters. – TLama Jul 28 '15 at 12:00
@TLama I'm sorry, you are right. I had time to test it just now. ;) – Gianluca Colombo Jul 28 '15 at 12:17
Hi All, The FDCommand process does not work in creating tables; even if you check that tables does not exist! For some reason is seems to me the component sends the SQL twice, thus the error returned that the table exists. – Tom Dalton Aug 01 '15 at 17:36
Hello, I just getting used to this site; so oops if you hit the return key is adds the comment. However, what does work is the FDConnection.ExecSQL (Create Table MyTable (Fielddef1, Fielddef2...). This does the job and without the error that the table exists. – Tom Dalton Aug 01 '15 at 17:42

score 0 · Answer 2 · answered Jul 16 '17 at 23:03

You are calling Execute with ATimes parameter set to 200. That means that you want execute the command in array DML mode and you have 200 rows of parameter values. And since you have no parameters, it simply executes the command that number of times which fails on second attempt when DBMS rejects to create already created table.

You can either call Execute this way (there's no need for explicit call of Prepare):

FDQuery1.SQL.Text := Memo1.Text; // Create table SQL command
FDQuery1.Execute;

Or ExecSQL which internally calls Execute the way shown above:

FDQuery1.SQL.Text := Memo1.Text; // Create table SQL command
FDQuery1.ExecSQL;

Creating Tables MS ACCESS with FIREDAC

2 Answers2