topic Re: Linked tables in MS Access Database not available for select with tAccessInput component in Talend Studio

Linked tables in MS Access Database not available for select with tAccessInput component

Anonymous — Sat, 16 Nov 2024 08:09:32 GMT

We are using a tAccessInput component to try to select from linked tables in our MS Access Database. The tAccessConnection component it references points to a file based MS Access DB that has been put on both a local drive and a mapped drive. In either case, when we select from local tables in the Access DB, all is well, but when trying to select from linked tables (Linked via an ODBC connection to a Pervasive database), we get the following errors:

Exception in component tAccessInput_1
net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: IMITMFIL
   at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:202)
   at valogixdatainterface.msaccessdataimportv862_8_6.MSAccessDataImportV862.tJava_1Process(MSAccessDataImportV862.java:1347)
   at valogixdatainterface.msaccessdataimportv862_8_6.MSAccessDataImportV862.tAccessConnection_1Process(MSAccessDataImportV862.java:755)
   at valogixdatainterface.msaccessdataimportv862_8_6.MSAccessDataImportV862.tFileInputXML_1Process(MSAccessDataImportV862.java:5166)
   at valogixdatainterface.msaccessdataimportv862_8_6.MSAccessDataImportV862.runJobInTOS(MSAccessDataImportV862.java:6023)
   at valogixdatainterface.msaccessdataimportv862_8_6.MSAccessDataImportV862.main(MSAccessDataImportV862.java:5863)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: IMITMFIL
   at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
   at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
[statistics] disconnected
   at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
   at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
   at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:199)
   ... 5 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: IMITMFIL
   at org.hsqldb.error.Error.error(Unknown Source)
   at org.hsqldb.error.Error.error(Unknown Source)
   at org.hsqldb.SchemaManager.getTable(Unknown Source)
   at org.hsqldb.ParserDQL.readTableName(Unknown Source)
   at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
   at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
   at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
   at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
   at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
   at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
   at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
   at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
   at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
   at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
   at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
   at org.hsqldb.ParserCommand.compilePart(Unknown Source)
   at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
   at org.hsqldb.Session.executeDirectStatement(Unknown Source)
   at org.hsqldb.Session.execute(Unknown Source)
   ... 8 more
Job MSAccessDataImportV862 ended at 13:03 04/06/2018. [exit code=1]

I am told this was working in Talend 5.5 with the same components. Currently, we have upgraded to Big Data 6.2.1.20160704_1411

Re: Linked tables in MS Access Database not available for select with tAccessInput component

Anonymous — Mon, 04 Jun 2018 19:51:37 GMT

It looks like you're querying a table that does not exist. Make sure your job is connecting to the DB you expect it to connect to. Dont forget the connection params/table/column names are case sensitive by default.

Re: Linked tables in MS Access Database not available for select with tAccessInput component

Anonymous — Mon, 04 Jun 2018 21:49:40 GMT

Thanks for your response JGM. We have checked that already, by switching the query to local tables in the Access database. When query local tables, it works. The linked table we are querying is definitely there, it just does not seem to be found when running the query through the tAccessInput component.

Re: Linked tables in MS Access Database not available for select with tAccessInput component

Anonymous — Mon, 04 Jun 2018 22:06:59 GMT

I was curious so I did a few googles and unfortunately, it looks like this may just not work with the driver the components are using:

for the access components, it looks like Talend is using this driver: net.ucanaccess.jdbc.UcanaccessDriver

when I checked in on linked table support for that driver I found this as an answer to a stackoverflow question:

Currently, UCanAccess is only able to work with "native" Access tables, i.e., tables that are actually stored in an .accdb or .mdb file (or related variants like .accde, .mde, etc.). UCanAccess can operate on native linked tables (links to Access tables that are stored in a separate .accdb/.mdb file), but it cannot work with ODBC linked tables (links to tables stored in SQL Server, MySQL, ...)

https://stackoverflow.com/questions/41387193/jdbc-jackcess-ucanaccess-not-able-to-connect-to-access-tables-linked-to-sharep

Edit:

After replying, it occurred to me that if you are using ODBC linked tables, you may be able to work around the driver limitations by querying the linked database directly using the appropriate database components or by using the generic tJDBCInput component

Re: Linked tables in MS Access Database not available for select with tAccessInput component

Anonymous — Tue, 05 Jun 2018 13:07:58 GMT

Thank you for the info. I should have checked that out myself. Much appreciated.

We will need to check into other solutions, as you mention below.

Re: Linked tables in MS Access Database not available for select with tAccessInput component

Anonymous — Fri, 11 Jan 2019 05:59:06 GMT

Hi,

Currently I am working on Linked Table Manager tables. I m facing this issue : "net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.1 user lacks privilege or object not found: CHANGE_CHANGEREQUEST" . Have you found any solution to this. Please let me know if any.