Identify tables in MSACCESS database . . . msysobjects

Hi all,

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));

I discovered this example: Identify tables in you database(s) and loop over them and store the data in a CSV file – KNIME Hub
. . . and tweaked it a bit for Microsort Access to get a connection to the database.

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.

So, hopefully someone can help . . . THNX

KNIME_MSACCESS.knwf (30.2 KB)

SCREENSHOT 1

SCREENSHOT 2

Hi @sanderlenselink,

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.

Hope this helps you,
Best,
Michael

4 Likes

It does work and seems a good way to go forward. I added an example to my existing workflow about MS Access Updated and Merge:

3 Likes

Great, thank you Markus (@mlauber71) for sharing these sample workflows. Highly appreciated.

Best,
Michael

1 Like

Hi Michael / Markus . . . thnx for your help both

I succeeded to realise what I wanted. Below the relevant part of my final flow:
image

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:
image
MSACCESS
image

(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
image

Anyhow . . . it works and once again thnx for your feedback

-Sander

2 Likes

Hi @sanderlenselink,

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

Best,
Michael

1 Like