Connect to Microsoft Access DB and get data

  • 221 Views
  • Last Post 27 April 2019
lakshminarayanan.gururaj@tcs.com posted this 17 April 2019

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. 

Order By: Standard | Newest | Votes
Swathi Muppalla posted this 18 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:

 

 

 Regards,

Swathi Muppalla

RPA Developer.

 

 

  • Liked by
  • lakshminarayanan.gururaj@tcs.com
lakshminarayanan.gururaj@tcs.com posted this 18 April 2019

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

 

Swathi Muppalla posted this 20 April 2019

Hi Lakshmi Narayanan,

 

Can you please check if oledb is installed on  the machine . If not please download and installed the same (32-bit version) from the link below.

Microsoft OLEDB

Thanks & Regards

Swathi Muppalla

lakshminarayanan.gururaj@tcs.com posted this 23 April 2019

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. 

Swathi Muppalla posted this 23 April 2019

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:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Follow the instructions on the screen to complete the installation.

 Once the installation is done, save your work and restart Intellibot Studio.

 

Thanks &Regards,
Swathi Muppalla

 

lakshminarayanan.gururaj@tcs.com posted this 26 April 2019

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 ?

Swathi Muppalla posted this 27 April 2019

 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.

Regards

Swathi Muppalla

Close