I want to identify the tables in my MSACCESS database. I modified the settings in the MSACCESS configuration (Systemobjects show). See screenshot 1.
If I run this query in MSACCESS I get an overview of the tables:
SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1));
If I run the abovementioned query in de “DB Query Reader” node I get an error (UCAExc:::4.0.4 user lacks privilege or object not found: MSYSOBJECTS) indicating that the “hidden” table MSYSOBJECTS cannot be found although it is really there in MSACCESS.
It’s been quite a while since I last used MS Access and have currently no access to the application so I had to google it. It seems that since the MSysObjects is a system table it is not accessible for access via SQL or it has to addressed differently.
What you could try mentioned as solutions in my findings for SQL requests:
a) Address the table as sys.MSysObjects or use
b) “SELECT * FROM information_schema.tables”
After downloading an Access sample db I checked both, while a) gave me an error that “SYS” is an invalid schema name, even after setting sysSchema=true in jdbc parameters b) succeeded and provided useful details.
I succeeded to realise what I wanted. Below the relevant part of my final flow:
However, there are some strange things:
(1) somewhere else on this forum I learned to apply a Rule-based Row Filter with a bit strange rule.
However, if I skip this filter it doesn’t work. The rule is a bit confusing for me
(2) Microsoft Access or the DB Query Reader node adds the prefix “Z_” to the table names. Maybe because the table names begin with a number (the other table names don’t get such a prefix)
DB Query Reader:
MSACCESS
(3) regarding the loop End I have to check the last box (“Allow changing table specifications”). If I limit the loop to 2 tables it runs smooth but with in my case 8 tables I got an error
Anyhow . . . it works and once again thnx for your feedback
(1) The Rule-based Row Filter will stop once a condition is met. So if $TABLE_NAME$ LIKE “Z_20*” the node will end with the result TRUE. Line 6 will otherwise checked and used as a default, as the statement “TRUE” is fulfilled in any case, causing the node to end with result FALSE. It is indeed a bit confusing first, but once you get used to it the last line just represents the default (it is similar to switch case in other programming languages).
(2) I second you experience, this seems to be how the information_schema.tables reveals the tables.
(3) The documentation of the node specifies for this option the following:
Allow changing table specifications
If checked, the table specifications between iterations can differ. If column have been added or removed between iterations, missing values are inserted accordingly in the result table. If not checked and the table specifications differ, the node will fail.
I assume that the result table differs in the contained columns due to missing values.