Is there a plugin or way I can get data from a Microsoft access db. We currently have lot of excel which has macros connected to access db. Wanted to get these into intellibot.
Connect to Microsoft Access DB and get data
- 240 Views
- Last Post 27 April 2019
Hi Lakshmi Narayanan,
Thank you for your interest in INTELLIBOT.
Data can be extracted from Microsoft Access DB using SQL component. This component returns the data in Data Table format which can be saved to a file.
Below mentioned steps can be followed to extract the data from Access DB:
1. In the Toolbox, expand “General” category.
2. Drag the SQL component and drop it on the Design Surface.
3. Double click on Connection Provider textbox to view the Properties.
4. Select the Connection Provider as OLEDB from “Static Value” dropdown box. Click OK.
5. Double click on Connection String textbox and write the Connection String based on the Connection Provider selected. (In this case, the following connection string for OLEDB can be used: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Access file path;Persist Security Info=False;).
6.Double click on the title bar of the SQL component. The SQL Editor dialog box is displayed.
Enter the required query under “Script” section. Click OK to save the changes.
7. In the toolbar, Click Run.
In this automation, SQL Component returns the data in Data Table format. Right click on the data Out port to view the extracted data.
To save the data into Excel file, below mentioned steps can be followed:
1. In the Toolbox, expand Utilities > Data Writer.
2. Drag the WriteToExcel component and drop it on the Design surface.
3. Specify the file location, by double-clicking on the file path.
4. Double click on Sheet Name textbox and enter the Sheet Name.
5. Connect all the control ports and data ports in the activity.
6. In the toolbar, Click Run.
Data is transferred to Excel file.
Screenshot for reference:
Thanks a lot. I was able to connect and get data, just when I tried to extract into the excel getting this error.
Component "WritetoExcel" failed to execute,
Exception has been thrown by the target of an invocation
Could not find installable ISAM
Thanks for the message. As all my MS office softwares are in 64 bit, the 32 bit is not getting installed.
Is there an work around of using ODBC ? 32 bit OLEDB installation fails.
Hi Lakshmi Narayanan,
If your system type is a 64-bit Operating System, you can download AccessDatabaseEngine_X64. exe file by accessing the URL below:
Follow the instructions on the screen to complete the installation.
Once the installation is done, save your work and restart Intellibot Studio.
Thanks a lot. I was able to connect and also generate excel. But in the script SQL block, I can execute only one command. Can I execute multiple command ? How can I do it ? For loop ?
Thank for your Interest in Intellibot!
One SQL block we can execute only one command at a time if you want use multiple commands then use multiple SQL Components.