Accessing Tables in MSAccess with Table Selector Node

[ncannistra]

I am using the MSAccess Connection Node to successfully connect to an MSAccess database. I am then linking this node to the Table Selector Node and am unable to display any tables from this database. I also tried querying the table directly in this node with the custom SQLStatement … I only get: “WARN DB Table Selector 0:10 net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 user lacks privilege or object not found: PMATTBASE”

I can access the table just fine since Access does not require credentials (outside of KNIME).

Under the Database Metadata Browser I am only seeing: Schema → Public

Any suggestions of what I am doing wrong to display these tables?

[bruno29a]

Hi @ncannistra and welcome to the Knime Community.

I’m not sure what’s causing your issue, but I tried with both a .mdb and a .accdb file, and they both work for me with no complaints.

Can you tell me what you get if you click on the Select a table button?

Do you any tables in the popup window (Database Metadata Browser)? If not, you can click on the Fetch/Refresh button:

Alternatively, you can also click it in the DB Table Selector:

In the Custom Query, the #table# refers to the table that’s selected:

Alternatively, even if you don’t select a table, you can query a table directly like this:

As you can see, it still showed me the Preview results.

Just make sure that your table name is correct. In your case, it seems like you are trying to access a table called “pmattbase”. Does this table exist?

Question though… why do you need to use the DB Table Selector? I would usually use it only if I need to do a Parameterized DB Query Reader, otherwise I’d use a DB Query Reader, like this:

or like this:

[ncannistra]

Thank you for your reply. When I click on Select a Table I only get this:

The refresh button works (Fetched 1 minute ago), but only displays the same as above.

The reason I am even attempting to use this node is because I cannot connect a Microsoft Access Connector node directly to a DB Query node. Hence, I thought that I would need to have to connect the connector node to this DB Table Selector node to be able to then connect to a DB Query node. If there is a way around this, I’m all ears!

[ncannistra]

My Microsoft Access Connector node before this DB Table Selector node connects the the Access DB file via UNC path just fine without error. So, it looks like it is correctly opening the Access DB (.accdb) before this Table Selector node.

[ncannistra]

I just figured out my problem!!

My connector node was referencing the .accdb file without the .accdb extension in the path. It executed just fine without it … but was a problem with the Table Selector node. Adding the .accdb extension to the UNC path and filename corrected the problem. I can now see all the tables in the Table Selector node!

Thank you again for your reply and help. It is greatly appreciated.