Hello Team,
Just want to ask if KNIME is capable to run existing MS Access Database as we are planning to include the output of MS Access Database in our workflow so we can automate the end-to-end processing of reports. Appreciate if someone can help on this. Thank you so much!
Thanks for your response @ArjenEX!
Just want to ask for the configuration, what needs to be take on for it to work?
Nothing really. The only thing you have to do is point to the file location where the .accdb is located.
Wow! Then may I ask for the DB Query Reader node, will it be populated automatically or we need to update in the configuration too?
And just to add, how can we set in KNIME which button to click in MS Access Database?
Apologies as I am just throwing some random question in my mind prior connecting it and informing the processor with the automation approach. Appreciate your help big time!
The query is based on SQL syntax. If you click on the refresh button it will load the schema of the database and you can subsequently extract the desired data.
Clicking buttons in Access itself is not really what KNIME is made for, there isn’t such a RPA feature. You’re better off with a dedicated RPA tool.
Off course you can also decide that you mirror those two processes in KNIME since it has many file handling capabilities.
Thanks again @ArjenEX! A new learning for me…
But to clarify - Is SQL Statement section is blank and you just put the “SELECT * FROM Products?”
And are you suggesting to create an SQL script inside DB Query Reader instead so we can extract the same data we are getting in manual process by clicking the button? And if successful, we can use KNIME to connect with MS Access Database and complete our end-to-end workflow automation.
@trafalgarlaw KNIME can connect to a MS Access database and use the data inside, either by loading it into KNIME itself or by sending (SQL) commands to Access to execute the operations you want. You will have to decide if MS Access is actually the right basis for your tasks.
If you want yo can build Data Apps to give people an interface to handle predefined operations, also when accessing databases - it would need some planning and preparation:
Thanks for the info @mlauber71, really helps!
May I ask, once we connect the MS Access database to KNIME and able to load the desired data in KNIME, is it also showing in the MS Access database app or only in KNIME?
Reason of asking this is - In manual process, the processor once they run the MS Access database, it will produce the output files and they are saving the updated or newly run MS Access database app in the shared drive and someone is opening and using always the updated database. I am thinking, is it also happening if I run the MS Access database inside KNIME? Will it be updated as well the data inside database?
@trafalgarlaw glad you like the links. Concerning data in KNIME and Access. You have to keep in mind that data being processed in KNIME (the small connectors with the black triangle) is (well) being processed in KNIME and stored locally on your hard drive.
If you do manipulations in a database they will be done in the MS Access file for example. This will depend on which nodes you use.
If changes are made in an MS Access file on a shared drive you will have to make sure that the results are being synced over the network before they will show up on other peoples systems, and you also will have to see what might happen if more people might access the file at the same time.
I remember that some years ago you could ‘share’ an MS Access file over a network drive but is was quite a process to secure individual data entries from damage when several people were accessing them. Which is why traditionally you employ databases in such tasks so they will manage these accesses.
I think you will have to do some planning and see if this format is the right one. Alternatives might be tools like SQLite and H2 though my experiences with shared access on them is very limited.
Maybe also read the passage about: “KNIME and Clouds — OneDrive and DropBox” in this article:
Great info @mlauber71!!
Will just reconvene with the team and not close the discussion yet. Probably will throw some question again this week 
But still hoping we can eliminate the database so it will be automated all in KNIME.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
