Devart Forums

I have the following function that transfers the content of a Paradox table to MySQL.
For strange reasons the code doesn't work when switching to MS Access.

Any ideas as what can be wrong?
(Delphi 2009 + latest UniDAC)
I have other inserts to MS Acces that works just fine.

procedure TransferCards_11(aTableName: string);
var
Table: TTable; // Paradox table
Query: TUniQuery;
MaxRecord: integer;
CurrentRecord: integer;
datDate: TDatetime;
begin
CurrentRecord := 0;
MaxRecord := BDECountRecords(aTableName);
MeterLower(MaxRecord, CurrentRecord);
Query := TUniQuery.Create(Nil);
try
Query.Connection := frmDBConn.conDBserver;
Query.SQL.Clear;
Query.SQL.Add('INSERT INTO ' + TableCards);
Query.SQL.Add(' (RecordID, CenterID, TransactionID, PAN, CardNo, Currency, BoxID, Amount, fldPrint, StatusText, ');
Query.SQL.Add('Tidspunkt, fldDate, fldTime, fldHour, fldDay, fldDay_Number, fldWeek, fldMonth, fldYear, fldPrev_Day_Number) ');
Query.SQL.Add('values (:RecordID, :CenterID, :TransactionID, :PAN, :CardNo, :Currency, :BoxID, :Amount, :Print, :StatusText, ');
Query.SQL.Add(':Tidspunkt, :fldDate, :fldTime, :fldHour, :fldDay, :fldDay_Number, :fldWeek, :fldMonth, :fldYear, :fldPrev_Day_Number) ');
Table := TTable.Create(Nil);
try
Table.DatabaseName := Settings.PDX_Alias;
Table.TableName := aTableName;
Table.Open;
try
if not Query.Connection.InTransaction then
Query.Connection.StartTransaction;
while not Table.Eof do
try
begin
CurrentRecord := Table.RecNo;
MeterLower(MaxRecord, CurrentRecord);
datDate := StrToDateTime(FormatDateTime('dd-mm-yyyy hh:mm:00', Table.FieldByName('Tidspunkt').AsDateTime));
Query.ParamByName('RecordID').Assign(Table.FieldByName('RecordID'));
Query.ParamByName('CenterID').Assign(Table.FieldByName('CenterID'));
Query.ParamByName('TransactionID').Assign(Table.FieldByName('TransactionID'));
Query.ParamByName('PAN').Assign(Table.FieldByName('PAN'));
Query.ParamByName('CardNo').Assign(Table.FieldByName('CardNo'));
Query.ParamByName('Currency').Assign(Table.FieldByName('Currency'));
Query.ParamByName('BoxID').Assign(Table.FieldByName('BoxID'));
Query.ParamByName('Amount').Assign(Table.FieldByName('Amount'));
Query.ParamByName('Print').Assign(Table.FieldByName('Print'));
Query.ParamByName('StatusText').Assign(Table.FieldByName('StatusText'));
Query.ParamByName('Tidspunkt').Value := datDate;
Query.ParamByName('fldDate').Value := TimestampPart(datDate, tpDate);
Query.ParamByName('fldTime').Value := TimestampPart(datDate, tpTime);
Query.ParamByName('fldHour').Value := DatePart(datDate, dpHour);
Query.ParamByName('fldDay').Value := DatePart(datDate, dpWeekDay);
Query.ParamByName('fldDay_Number').Value := DateSerial(datDate);
Query.ParamByName('fldWeek').Value := DatePart(datDate, dpWeek);
Query.ParamByName('fldMonth').Value := DatePart(datDate, dpMonth);
Query.ParamByName('fldYear').Value := DatePart(datDate, dpYear);
Query.ParamByName('fldPrev_Day_Number').Value := DateSerial(datDate) - 1;
if TransferThis(datDate, pdxCreditcard) then
Query.Execute;
if CurrentRecord mod Settings.BatchSize = Settings.BatchSize - 1 then
Query.Connection.CommitRetaining;
Table.Next;
Application.ProcessMessages;
end; //Not Table.Eof
except
on E:exception do
begin
Logfile.Error('TransferCards_11 - ' + E.Message);
Table.Next;
Application.ProcessMessages;
end
end;
finally
Query.Connection.Commit;
Table.Close;
end;
finally
Table.Free;
Query.Free;
MeterLower(MaxRecord, CurrentRecord, False);
Logfile.Event('', '', '> Behandlede ' + FormatFloat('#,##0', MaxRecord) + ' records fra filen: ' + aTableName );
Application.ProcessMessages;
SystemUpdateTime;
end;
except
on EAccessViolation do
begin
Logfile.Event(conSourceSystem, conSourceParadox, 'AccessViolation: ' + aTableName);
end;
on EDBEngineError do
begin
Logfile.Event(conSourceSystem, conSourceParadox, 'DB engine fejl: ' + aTableName);
end;
end;
end;

Hello,

I tried to transfer data from a Paradox table to a MS Access table using your code, and all data was transferred completely.
Please tell me what error occurs and where it occurs, and send a small project and structure of the Paradox and MS Acess tables to demonstrate it to alexp*devart*com.

The error message is
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

I can mail yo a copy of a Paradox table and the Access mdb if that would help.

(been away for some days hence the delay in answering)

Hello,

Please, quote the field "Currency" in the INSERT statement, because Currency a reserved word in MS Access

That I hadn't seen coming

But it solved the problem - Thanks