Updating from Paradox – Handling field names containing # symbol.

I was asked to upgrade an old database system that used the BDE to interface to Paradox tables to FireDAC and MS Access.

The first job was to use FireDAC to read the Paradox tables and then move the code across to equivalent MS Access tables.

I ran into a problem because some of the paradox tables had the # symbol as part of their field names (for example “House#” for house numbers in an address). Another problem was use of the field name “Date” which caused problems as it was interpreted as an SQL type name.

FireDAC and the ODBC bridge to the paradox tables didn’t seem to handle these names very well. In particular it was impossible to update the field contents with new values.

My working solution was to use the TFDBatchMove, TFDBatchMoveDataSetReader and TFDBatchMoveDataSetWriter components to move the contents of the paradox tables into the equivalent tables in MS Access.

The TFDQuery used as the datasource for the TFDBatchMoveDataSetReader contained the following SQL statement:

SELECT “House#” as HouseNum, StreetName, Town, City, “Date” as DateEntered FROM ADDRESS

Notice the “House#” and the “Date” field names in the paradox table being in inverted commas and being renamed as part of the query, ready for the batch data move.