Connection to Access database that has linked tables of SQL server

[Yi_Liu]

Hi, all

I was not able to read the linked tables via MS Accesss connector. Those tables are linked to tables in an SQL server database. On this forum, I have found the below post:

Microsoft Access connector doesn't read linked data?

Hi! I have a Microsoft Access file/db that contains linked data. I’ve successfully used the Connector node to access the file, but when using the DB Table Selector node, I can only view local tables or queries based off local tables. Is there some setting that needs to be changed either on the KNIME side or the Access side to enable the ability to pull in linked data? Thanks

However, I could not find a solution therein. Can anyone help here?

Thanks

[ScottF]

Hi @Yi_Liu and welcome to the forum.

Based on the forum post you found and the limitations of the UCanAccess driver, it looks like this won’t work. (That said, I am not a DB expert and am happy to be corrected by someone who knows better and has a magic bullet solution!)

Is there a reason why you can’t use the Microsoft SQL Server Connector to access your tables directly, and bypass MS Access altogether?

[Snowy]

IIRC when I was researching this issue, it did look like there were a couple of Access drivers that would support, but they cost money, so I stopped researching the issue. My ‘solve’ was to export the table I needed from Access into a server/folder. Access gives you the ability to “Save export steps” and integrate it into Outlook as a Task which makes it a one-click export, without ever having to open Access again. I believe there is also a way to have Windows Task Scheduler kick off the export from Access, completely removing any human element, but for me, I don’t need hourly/daily exports of this, so I stick with the one-click export whenever I have to run the KNIME workflow that needs this table.

If you do find a different solution though, please post!