#105 Reading and writing values of calculated fields

[Gord Thompson]

As requested, here are some details on the issues with reading and writing the values of calculated fields. The .accdb files mentioned are included in the zip file attached to the case.

The file "01_original.accdb" contains a table with a calculated field named [LastFirst]

[LastName] & ", " & [FirstName]

and a single record

Opening the file with a hex editor reveals that the calculated value in indeed stored in the record

When we dump the row via Jackcess

String dbFile = "C:/Users/Public/test/CalcFieldTest/01_original.accdb";
try (Database db = DatabaseBuilder.open(new File(dbFile))) {
    Table table = db.getTable("Table1");
    for (Row row : table) {
        System.out.println(row);
    }

we see funny characters for the value of the calculated field

If we try to insert a new row and include the value of the calculated field

String dbFile = "C:/Users/Public/test/CalcFieldTest/02_inserted.accdb";
try (Database db = DatabaseBuilder.open(new File(dbFile))) {
    Table table = db.getTable("Table1");
    table.addRow(Column.AUTO_NUMBER, "Homer", "Simpson", "Simpson, Homer", "Springfield");

the result is #Error for the calculated field in the new row

"02_inserted.accdb" is the database file in this state.

We can fix the calculated field value in the new row by running the following query in Access

UPDATE Table1 SET FirstName=FirstName WHERE ID=2

The file "03_after_fix.accdb" shows the result of running that query.

[Gord Thompson]

Additional information: The above tests were performed using a Java source file whose text file encoding was UTF-8. I ran the tests again after switching the text file encoding to Cp1252 and the results were essentially the same.

At least part of the issue appears to be that

1. the text value of the calculated field is being inserted into the database as 2-byte characters and

2. the FF FE marker that seems to precede most text fields appears to be missing.

[James Ahlborn]

I poked a bit at this last night, and i think i was able to figure out most of the bits around calculated fields. so, i should be able to get this into the next release.

[Gord Thompson]

Great news. Thanks!

[James Ahlborn]

unfortunately, i've been pretty busy and this was a bit harder nut to crack than i originally thought. anyway, i've checked in some initial support for reading and writing calculated values to the trunk. i'll be polishing/testing it a bit more before it gets released, but if you're interested, you can start playing with it now.

[Gord Thompson]

Thanks, James. I've done a few tests using jackcess-2.0.5-SNAPSHOT.jar and things seem to be working well so far.

[James Ahlborn]

Reading and writing calculated fields and creating tables with calculated fields should be functional in the 2.0.5 release.