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

[Anonymous]

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

[Anonymous]

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. 

[Anonymous]

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.

[Anonymous · Accepted solution]

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-...

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

[Anonymous]

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.

[Anonymous]

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.