You can retrieve data using an SQL query connected to an MS Access database by using the Blue Prism Business Object called “Data – OLEDB” found within the Business Object called “BPA Object – Data – OLEDB.xml”. This is one of the processes that comes with the product, and can be imported from the VBO directory beneath the installation folder for Blue Prism (by default ‘C:\Program Files\Blue Prism Limited\Blue Prism Automate\VBO’).
OLEDB support and the JET 4.0 Provider
The OLEDB object uses the Jet 4.0 OLEDB interface to connect to MS Access by default. The OLEDB provider information can be modified to suit whichever provider you have installed and wish to use – it is not restricted to Microsoft Access. The Jet 4.0 provider string is provided by default, and this will support a large number of OLEDB connections, including Access databases up until Access 2007, when the Jet 4 provider was deprecated, and the ACE engine was used instead (.ACCDB files).
The existing connection string looks like this:-
“Provider=” & [Provider] & “; Data Source=” & [Database] & “;” – no password required
“Provider=” & [Provider] & “; Data Source=” & [Database] & “; Jet OLEDB:Database Password=” & [Password] &”;” – where a password is required
In the Calculation Stage where the password is required the JET 4.0 OLEDB provider has been coded in. You are free to change this.
Changing the Connection String
The Connection String can be modified to create whatever connection you need to an OLEDB compliant database. The Initialise page of the “Data – OLEDB” Business Object allows for the setting of the most common Connection String parameters. You may add whichever ones you need to create your custom string.
The Set Connection page is the part of the process which uses the Connection parameters to form the connection string depending n whether a password is required or not. You are free to change these Calculation Stages to set whatever parameters you need.
For a list of the different Connection Strings used by most OLEDB compliant data structures please refer to the web site www.connectionstrings.com.
- Database – supply the full file path to the Access .mdb database file
- User – the name of an authorized Access user
- Password – the password for the Access user account
- SQL – a string containing the SQL query to retrieve data e.g. “SELECT * FROM myAccessTable”
- Results – create a Collection Data Item in your process and use the collection in the ‘Store In’ parameter.
When the standard JET 4.0 OLEDB Provider has been used to try to access a database that was created using Microsoft Access 2007 or later, then the following error message may be seen:
“Internal: Could not execute code stage because exception thrown by code stage: Unrecognized database format ‘< path to database file>’.”
This can be resolved by changing the OLEDB provider used in the Connection String to be one that is suitable for the version oof Access that the database was created in.