topic RE: How to Connect MS Access DataBase in Blueprism in University Forum

How to Connect MS Access DataBase in Blueprism

KarthikGanesh — Sat, 06 Nov 2021 07:17:00 GMT

Hi All,

I tried to connect MS Access DB with Blueprism but it throw an error for your reference I listed the error's below

I tried with Provider ="Microsoft.Jet.OLEDB.4.0" it throw an Error:- Internal : Could not execute code stage because exception thrown by code stage: Unrecognized database format 'C:\ABI Automation\MICS Reporting Automation\Input\MICS_Input.accdb'.

for the Provider="Microsoft.ACE.OLEDB.12.0" it throw an Error:- Internal : Could not execute code stage because exception thrown by code stage: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

for the Provider= "MSDASQL.1" and Connection String ="Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=MS Access Database;DBQ=C:\ABI AUTOMATION\MICS REPORTING AUTOMATION\INPUT\MICS_Input.accdb;DefaultDir=C:\ABI AUTOMATION\MICS REPORTING AUTOMATION\INPUT;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;" it throw an Error;- Internal : Could not execute code stage because exception thrown by code stage: The .Net Framework Data Provider for OLEDB (System.Data.OleDb) does not support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the .Net Framework Data Provider for ODBC (System.Data.Odbc).

Kindly help me with this to resolve the issue.

Thanks in advance.

------------------------------
Karthik Ganesh
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

John__Carter — Mon, 08 Nov 2021 11:08:00 GMT

Hi Karthik - you should speak to your IT to get the Access Database Engine (ACE) installed on the machine. I think the first error you have provided indicates that the older Jet provider does not recognise the newer database file format.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

KarthikGanesh — Tue, 09 Nov 2021 07:51:00 GMT

Hi,

Thanks for your response.

I have already installed Access Database Engine in our Local machine.

Can you suggest alternate solutions please.

Regards,

Karthik Ganesh | IMPACT Training - Business Intelligence

Mobile: +91-8220367233 | Phone: +91-44-6740 4000 | Extn: 13051

Website: www.aspiresys.com | Blog: http://blog.aspiresys.com

RE: How to Connect MS Access DataBase in Blueprism

John__Carter — Tue, 09 Nov 2021 09:36:00 GMT

The error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine" indicates that ACE is not available on the machine.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

KarthikGanesh — Tue, 09 Nov 2021 13:40:00 GMT

Hi John,

Thanks for your response

In my system I installed Microsoft Access Database engine showed below.

but it throws the same error

I hope this Microsoft access database engine and ACE are same if not kindly provide me the details how to install ACE.

Regards,

Karthik Ganesh | IMPACT Training - Business Intelligence

Mobile: +91-8220367233 | Phone: +91-44-6740 4000 | Extn: 13051

Website: www.aspiresys.com | Blog: http://blog.aspiresys.com

RE: How to Connect MS Access DataBase in Blueprism

KarthikGanesh — Wed, 10 Nov 2021 08:56:00 GMT

Hi John,

I checked my control panel Microsoft Access Database Engine(2010 & 2016) both are installed. But I got the same error

And you said " the older Jet provider does not recognise the newer database file format." then what is the newest Jet Provider to recognise the newer database file format?

Regards,
Karthik

------------------------------
Karthik Ganesh
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

AndreiBidirliu — Wed, 10 Nov 2021 10:29:00 GMT

Hi Karthik, i'm using the following to connect. DBpath being the filepath to the db

------------------------------
Andrei Bidirliu
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

KarthikGanesh — Thu, 11 Nov 2021 07:03:00 GMT

Hi Andrei Bidirliu,

Thanks for your reply.

I Can't able to find Data - MS Access VBO in my Blue Prism

Could you please help me to find where it is or guide me how to access that VBO and how to download it.

Thanks in advance.

Regards,
Karthik

------------------------------
Karthik Ganesh
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

John__Carter — Thu, 11 Nov 2021 13:39:00 GMT

Hi Karthik - check this article and look for the 'not registered on the local machine' error online. It could be a 32/64bit issue.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

marco.pogliaghi — Thu, 10 Feb 2022 13:33:00 GMT

Hi, I'm jumping in because I've the same type of issue: I have a " Provider 'Microsoft.ACE.OLEDB.12.0' in not registered...", but trying to access the same database with the same Provider with excel works, and, checking all registered providers in powershell with the command
(New-Object system.data.oledb.oledbenumerator).GetElements()
I obtain it (amongst others):
============================================
PS H:\> (New-Object system.data.oledb.oledbenumerator).GetElements()

SOURCES_NAME : SQLOLEDB
SOURCES_PARSENAME : {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}
SOURCES_DESCRIPTION : Microsoft OLE DB Provider for SQL Server
SOURCES_TYPE : 1
SOURCES_ISPARENT : False
SOURCES_CLSID : {0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}

SOURCES_NAME : MSDataShape
SOURCES_PARSENAME : {3449A1C8-C56C-11D0-AD72-00C04FC29863}
SOURCES_DESCRIPTION : MSDataShape
SOURCES_TYPE : 1
SOURCES_ISPARENT : False
SOURCES_CLSID : {3449A1C8-C56C-11D0-AD72-00C04FC29863}

SOURCES_NAME : Microsoft.ACE.OLEDB.12.0
SOURCES_PARSENAME : {3BE786A0-0366-4F5C-9434-25CF162E475E}
SOURCES_DESCRIPTION : Microsoft Office 12.0 Access Database Engine OLE DB Provider
SOURCES_TYPE : 1
SOURCES_ISPARENT : False
SOURCES_CLSID : {3BE786A0-0366-4F5C-9434-25CF162E475E}

SOU

===================================

So I don't understand why BP is not getting it, any suggestion?
(oh, BP6.4, Windows 10 and Windows 7),

Thank you,
marco

------------------------------
Marco Oreste Pogliaghi
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

PabloSarabia — Thu, 10 Feb 2022 13:54:00 GMT

Hi,

Be sure that you have correctly install the AccessDatabaseEngine. Find the official installation here.

The connector of Blue Prism is using the library "System.Data" and "System.Data.OLEDB", so... the real problem here is that the System is not recognize the Provider. Try it installing the correct version

Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

ewilson — Thu, 10 Feb 2022 16:32:00 GMT

@KarthikGanesh,

There's lots of suggestions here. Hopefully this will clarify some of it:

There is no standard Blue Prism VBO specifically for MS Access that I'm aware of. I'm guessing the Data - MS Access VBO shown above is something they developed themselves or found at another location.
There are two VBOs available on the DX, and included in the standard BP install, that could be used to connect to MS Access. They are the Data - OLEDB VBO and the Data - ODBC VBO. The one you use governs which support files you'll need to install from Microsoft.
- For Data - OLEDB, you want to use the Microsoft Access Database Engine 2010 Redistributable (NOTE: There are 32-bit and 64-bit versions of the driver installer in this download. You probably want to install the 32-bit version since Blue Prism is a 32-bit client).
- For Data - ODBC, you've probably already got the necessary driver installed. You can check this by going to Start -> Run and typing ODBC. You should see options for the 32-bit and 64-bit ODBC Data Source Administrator utility. You want the 32-bit version. When that comes up, you should see an entry under the User DSN tab for MS Access Database. Now, if you want to use ODBC you'll have to configure either A User DSN or File DSN.
Beyond the above, you have to have the correct Connection String defined. The Connection String you show in your screenshot suggests you're trying to use the ODBC provider to connect to your Access database. If that is the case, you need to use the Data - ODBC VBO. If you want to use the Data - OLEDB VBO, you need to change your Connection String to reflect that. It will look something like this:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[YOUR DATABASE FILENAME AND PATH];"

There could be more properties depending on how your database file is configured (ex. username and password).

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

marco.pogliaghi — Thu, 10 Feb 2022 17:15:00 GMT

Hi Eric and Pablo, thank you for your answers.
This helped me to understand the problem, I still have to find my way to the solution, but the first step is done.
I explain: I can't install any packet on my machine, nor on the ones that runs the robots.
Since some days ago all worked well, probably - as all machines are remotely administered - a system update drops the 32 bit libraries leaving only the 64 ones.
I still have an environment where the OLEDB is found (and all production machines are working), but I think I have to address the issue.

Thank you,
marco

------------------------------
Marco Oreste Pogliaghi
------------------------------

RE: How to Connect MS Access DataBase in Blueprism

PabloSarabia — Thu, 10 Feb 2022 17:34:00 GMT

Hi,

This is not the first time that any windows update ruins the Access connector. I don't know the KB code of the update that may cause this, but, if you can, try to go back this update just to test.

Bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------