topic Re: Connecting MS Access to Talend in Installing and Upgrading

Connecting MS Access to Talend

AV2022 — Fri, 15 Nov 2024 22:22:29 GMT

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

Re: Connecting MS Access to Talend

Anonymous — Fri, 11 Nov 2022 02:54:02 GMT

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

Re: Connecting MS Access to Talend

AV2022 — Fri, 11 Nov 2022 05:19:09 GMT

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)

Re: Connecting MS Access to Talend

Anonymous — Fri, 11 Nov 2022 07:40:12 GMT

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

Java version
Studio version
Access version

Regards

Shong

Re: Connecting MS Access to Talend

AV2022 — Tue, 15 Nov 2022 04:22:09 GMT

Hello Shong,

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

Re: Connecting MS Access to Talend

Anonymous — Tue, 15 Nov 2022 07:10:27 GMT

Great, thanks for your feedback!

Re: Connecting MS Access to Talend

AV2022 — Thu, 17 Nov 2022 06:17:00 GMT

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

Re: Connecting MS Access to Talend

Anonymous — Sat, 19 Nov 2022 08:47:57 GMT

@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

Re: Connecting MS Access to Talend

AV2022 — Tue, 22 Nov 2022 04:40:04 GMT

Hello Shong,

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

Thanks

Anand V

Re: Connecting MS Access to Talend

AV2022 — Tue, 22 Nov 2022 06:00:08 GMT

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)

Re: Connecting MS Access to Talend

AV2022 — Tue, 22 Nov 2022 11:13:12 GMT

Hello Shong,

I fixed the above issue. Thanks alot for your help, much appreciated.

Thanks

Anand V

Re: Connecting MS Access to Talend

Anonymous — Wed, 23 Nov 2022 02:59:26 GMT

Great, thanks for your feedback!

Re: Connecting MS Access to Talend

AV2022 — Tue, 29 Nov 2022 10:32:35 GMT

Hello Shong,

I need to your help with another issue which i am facing right now.

I was able to do all the data export from Test Access DB (which i created) including images and i am able to successfully import it into Oracle DB even the images in BLOB format.

Now the problem is when tried to read the data from the Actual schema in the Access DB and not the test schema which I created it is failing with the below error.

What I am trying to understand here the privilege issue which I am facing now is it related to the privilege of the Schema which i am trying to read or is it anything related to the talend or JDBC privileges.

Please help me with this issue. Below is the error message

WARNING:Error in the metadata of the table Base de dados_Marcas: table's row count in the metadata is 73561 but 73560 records have been found and loaded by UCanAccess. All will work fine, but it's better to repair your database.

WARNING:Error in the metadata of the table Base de dados_Marcas: table's row count in the metadata is 73561 but 2001 records have been found and loaded by UCanAccess. All will work fine, but it's better to repair your database.

Exception in component tDBInput_1 (Image_Extract)

net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: NULL

at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:247)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)

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

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

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

Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: NULL