Connecting MS Access to Talend

[AV2022]

Hello Team,

My Requirement: I want to export data from MS Access DB and transfer the data to Oracle DB using talend.

My Problem: I am not understanding clearly how to establish Access DB connection in talend.

Can someone help with this problem. I cant start my data migration activities without understanding how i can establish this connection. Please help me with this problem.

Thanks

Anand

[Anonymous]

Hi

Talend Studio offers specialized components for integrating with MS Access, please take a look at the component documentation and learn these components, such as tAccessinput, tAccessOutput.

Regards

Shong

[AV2022]

Hello Shong,

Thanks for the response, thanks sharing the component documentation.

My issue right now is when i try to establish a meta data connection for MS Access i am getting the below errors.

Connection failure. You must change the Database Settings.

 org.talend.utils.exceptions.MissingDriverException: can not find class :net.ucanaccess.jdbc.UcanaccessDriver

 missing JDBC driver :

 jackcess-2.1.0.jar

 ucanaccess-2.0.9.5.jar

 bcprov-jdk15on-1.51.jar

 talend-ucanaccess-utils-1.0.0.jar

 commons-logging-1.1.1.jar

 hsqldb.jar

 commons-lang-2.6.jar

 jackcess-encrypt-2.1.0.jar

   at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1206)

   at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:317)

   at org.talend.metadata.managment.repository.ManagerConnection.check(ManagerConnection.java:290)

   at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$75.runWithCancel(DatabaseForm.java:4556)

   at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$75.runWithCancel(DatabaseForm.java:1)

   at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)

   at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)

   at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)

   at java.base/java.lang.Thread.run(Thread.java:834)

[Anonymous]

Can you provide us more extra information for us to be more better evaluate the issue:?

• Java version
• Studio version
• Access version

Regards

Shong

[AV2022]

Hello Shong,

I have resolved the above issue by installing the missing modules.

[Anonymous]

Great, thanks for your feedback!

[AV2022]

Hello Shong,

I have another Query.

Problem: As part of Migration i am trying to import data from Access DB to oracle DB. In access DB there is a column which has images as attachments. I am not able to read those Image files through Talend.

My requirement: Need to read those image files through Talend and extract the output in Oracle DB in BLOB format.

Thanks

Anand V

[Anonymous · Accepted solution]

@Anand V​ , here is the Java code I used to extract the attachment from Access and convert it to byte[] and write them to local files.

 Object obj = rs_tDBInput_1.getObject(2);

 net.ucanaccess.complex.Attachment att = (((net.ucanaccess.complex.Attachment[])obj)[0]);

 System.err.println("Attachment Name: " + att.getName());

 row2.bytea = att.getData();

but I didn't tried to insert it Oracle Blob field, please refer to the below scenario and try to insert the byte[] to Blob field in Oracle.

 

Regards

Shong

[AV2022]

Hello Shong,

Thank you so much for sharing this details, i will try this and let you know the update.

Thanks

Anand V

[AV2022]

Hello Shong,

I tried with the above code which u shared, but I am getting the below error when I run the job. Can you please help me with this error.

 Exception in component tJavaFlex_1 (Image_Extract)

java.lang.ClassCastException: class java.lang.String cannot be cast to class [Lnet.ucanaccess.complex.Attachment; (java.lang.String is in module java.base of loader 'bootstrap'; [Lnet.ucanaccess.complex.Attachment; is in unnamed module of loader 'app')

at angola_migrate.image_extract_0_1.Image_Extract.tDBInput_1Process(Image_Extract.java:1445)

at angola_migrate.image_extract_0_1.Image_Extract.runJobInTOS(Image_Extract.java:2104)

at angola_migrate.image_extract_0_1.Image_Extract.main(Image_Extract.java:1942)