.accdb issues

[jj0610]

i am trying to read from a folder which contains a few .accdb files. These files need to be read and then i need to pick 2 tables from the database and convert them into a csv and store them into a folder again.
I have managed to read the files and pick the tables but manually, i need to automate the looping process to minimise human intervention. pls help!

[ScottF]

Hi @jj0610 and welcome to the forum.

I have a few questions:

1. In your WF screenshot above, is there a reason to have four separate Microsoft Access Connector nodes, or can you just use one and branch off from it multiple times?

2. Do you need to loop over multiple different databases, or multiple tables within the same database? (Or both?)

In the first case, you could use a List Files/Folders node to get a list of your different databases, then a Table Row To Variable Loop Start to begin a loop that generates a flow variable containing the Access file of interest. Inside the body of the loop, do the data wrangling needed, then finish with a Variable Loop End. Something like this (just a quick example):

But answer the questions above and we can try to put you on the right track.

[jj0610]

Hi there scott, thanks for helping out.
I am still facing an error. the Microsoft Access Connector can only read 1 file at a time, so i used a few to read all my files and append it, but for real use its not ethical so i indeed do have to loop, i have tried your said method, but again an error at the same node.

The loop node i included the path [that has been converted to string[ and for the Microsoft db node, i used the variable settings to read the path [for location of files]

[jj0610]

Also to answer the second question,
i have to read multiple database files to extract the tables from each file. but the table names are the same, even through different db files.
for examples;
in my knime folder i have apple.accdb, orange.accdb, kiwi.accdb
from apple.accdb, i need to extract table fruit and table colour
from orange.accdb, i need to extract table fruit and table colour
from kiwi.accdb, i need to extract table fruit and table colour

[ScottF]

What is the text of the error that is produced?

[jj0610]

ERROR Microsoft Access Connector 4:161 Execute failed: UCAExc:::5.0.1

[mlauber71]

@jj0610 if the number of access files is constant why not just use 4 nodes?

[ScottF]

I’m still not sure why your workflow is failing, but I put together a simple example for you that works. Take a look and see if it helps.

AccessLoopingExample.knwf (80.6 KB)