Using UCanAccess in Talend TOS DI doesn't work for midbrain files

[Massimiliano Rizzato]

I am trying to connect to the data files of a third party application which are basically mdb files.
Using DBeaver, which uses UCanAccess, I can open these files winthout any issue, which tells me three things: the files are neither password protected nor corrupt and furthermore ucanaccess can work without issues with these files.
I am developing my solution with Talend TOS DI 32 bits, which incorporates UCanAccess 2.0.9.5, but I cannot get it to connect to my mdb files. I need to say that with accdb files there are no issues in building a connection.
Anny ideas what the issue could be?

Thank you for your help.

[Gord Thompson]

with accdb files there are no issues in building a connection

 
So if you have an .mdb file that won't work and you save it as an .accdb file does the .accdb file work correctly?

[Massimiliano Rizzato]

1. I don't know, because I don't have an old enpugh installation of Office to try it.
2. In any case, as I said, the file format cannot be changed, because it is the standard output format of a 3rd party software.

[Gord Thompson]

I don't have an old enpugh installation of Office

 
So presumably the .mdb file is an old format, most likely Access_97 format. If that is the case then UCanAccess will open it as read-only because Jackcess can only read (not write) Access_97 files. Perhaps TOS doesn't like that

the file format cannot be changed

 
The point was to find out if the particular .mdb file format was the source of the problem. If so, then you may need to convert them to a newer format before TOS can use them.

[Massimiliano Rizzato]

Using the same version of Ucanaccess (2.0.9.5) which is used by TOS, but downloaded from Sourceforge, because the one in TOS doesn't have the console, I accessed the mdb files without problems by means of ucanaccess' console, in RW mode, so I think they are in post-Access 97 format. Probably 2003, but I can't be sure.

[Marco Amadei]

Hi Massimiliano, on the way, does TOS(which version) already incorporate ucanaccess or your solution? Can you connect to those mdb files using the ucanaccess console?

[Massimiliano Rizzato]

I am using TOS DI 6.0.1, which uses Java JDK 1.8+ and has ucanaccess 2.0.9.5 as one of the components automatically downloaded from the Talend repository to support the Access components.

[Marco Amadei]

I can just confirm and check why TOS doesn't support the mdb files, and, if this issue doesn't depend on ucanaccess, I would like to collaborate with them... I let you know my findings...

[Massimiliano Rizzato]

Thank you very much, I would appreciate a lot to be able to use ucanaccess for mdb files from TOS.
It would make my project much easier.

[Marco Amadei]

Hi Massimiliano, unfortunately I can't reproduce the issue. In the cases I tested, there may be some usability issues and "improvement opportunities" but it works for both accdb and mdb. If you want, you can send me for review, one of the problematic db files(without sensitive data!!!) to my private email amadei.mar@gmail.com. What if you use the generic jdbc solution?

[Massimiliano Rizzato]

I had the same issue with the generic jdbc solution, by the way, as the issue was caused as I already mentioned, by the file encodingparameter of the jvm.

[Massimiliano Rizzato]

I posted the issue also on Talend's support forum and it was solved by their support.
The issue was with the jvm -Dfile.encoding parameter, which for me was set to UTF-8, but should have been -Dfile.encoding=ISO-8859-1, in my case.
You can find the full discussion at the following link:

https://jira.talendforge.org/browse/TDI-35370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Ciao Marco, grazie comunque per l'offerta di aiuto, e per questo bellissimo driver che ci libera da ODBC! :)

P.S.: Maybe an interesting option for ucanaccess driver would be a parameter for encoding? Does it make any sense?

[Marco Amadei]

Hi Max, thank you!
Yes, a driver parameter for the encoding would make sense, I'll evaluate this option. For some reason the post above was automatically put under moderation by sourceforge (I don't know what's the rule about it), but the previous one is the most recent one. Is your issue finally closed? Fammi sapere (se passi ancora per questo forum)!
Ciao, Marco

[Gord Thompson]

For future reference, the file ZH419.mdb was in fact in Access_97 format (as reported by Jackcess), and that file format was based on Jet_3.x. Unicode support was introduced to the Access database file format with Jet 4.0 (Access 2000).

The issue with that file appears to be related to the table named [T_Gebäudehülle].