Connecting the Microsoft Access database to IRI Workbench and the back-end SortCL data processing program to exploit the many data manipulation and management capabilities of Voracity platform software is a simple process. The method follows the same paradigm as other databases; see similar sets of instructions indexed here.

To allow Workbench to view the tables and use the metadata in Access, you’ll need a JDBC driver. And since SortCL is written in C, an ODBC driver is required to move the data to and from the engine.

At the time of writing this article, Microsoft’s ODBC driver for Access does not support certain functions SortCL requires, nor does Microsoft have a JDBC driver. We are therefore using partner JDBC and ODBC drivers from CData. A second option for JDBC drivers is Ucanaccess, which is an open-source driver implementation from SourceForge.

Microsoft Access does not support the use of schemas. Using the CData driver, a schema Access is used as a placeholder that contains all the tables. Ucanaccess does the same thing but uses the name Public as the schema name. 

Due to the way that CData is being used with a schema placeholder, it will always state that a connection is successful. To know if a connection is truly successful, check to see if the tables are viewable in the Data Source Explorer within Workbench.

Known Driver Limitations

Using the data types Large Number and Date & Time Extended causes the Access database to no longer be compatible with earlier versions. This causes the drivers from both CData (version 21) and SourceForge (version 5.0.1) to no longer be able to connect to the database. 

There is also a compatibility issue using CData’s ODBC and SourceForge’s JDBC driver due to both having different default schema names that are not configurable. Currently, Workbench is under development to add more support for SourceForge’s JDBC driver.

Setting Up ODBC

Now that the drivers have been downloaded and the limitations are understood, the next step is to set up the ODBC Connection. Open the ODBC Data Source Administrator application (which can be found easily using Windows search). 

There are two versions for this application, one that is 32-bit and the other 64-bit. Select the one that corresponds to the bit version of SortCL, which is found in the Workbench Help menu under Version Information. 

In the ODBC Data Source Administrator, under the User DSN or System DSN tab, click the Add… button and select the “CData ODBC Driver for Access” driver. 

The CDData driver configuration page will open to the Connection tab. Name the Data Source, which can correspond to the name of your Access database to provide context. 

The only thing that is required is the path to where the Access database (.accdb) file is located. Test the connection, and if everything is successful the next step is to set up the JDBC connection inside of Workbench.

Setting Up JDBC

In the lower-left corner of IRI Workbench, the Data Source Explorer view should be open. Click on the gold icon called “New Connection Profile” and a pop-up window will appear with all the RDB sources that IRI Workbench supports “out of the box.”

The following display is where the driver definitions and jar files are configured. Select the driver template relevant to the JDBC driver that will be used. The default driver is Ucanaccess. Once the driver template is selected go to the JAR List tab to input the path to the jars needed.

The image below shows the list of jars that are needed for Ucanaccess. All that is required from the user is to give the path to the jar files, which can be done by selecting the Edit Jar/Zip… button.

The image below shows the same Jar List requirements, but for CData with the path already loaded in.

Once the driver template is selected and the required jar files have their full path, click OK. This returns to the New Connection Profile, and requires the location to the Access database file. 

There are optional parameters like user name and password (if needed to connect to Access). In this case, no credentials were needed. Test your connection to ensure the information is correct.

Setting Up Data Connection Registry

The final step is to set up the connection registry that essentially creates the relationship between ODBC and JDBC drivers. From the Workbench top toolbar, open the IRI menu and Preferences. From there, select IRI > Data Connection Registry. 

Within the Data Connection Registry, the DSN column relates to the ODBC driver, so look for the name of the Access data source you set up earlier. Select the name and click on the Edit … button on the right to add the missing information.

The Data Connection Registry options dialog that appears will contain information on the DSN. Below that, you can select which bit version to use, and at the very bottom is where you select the connection profile that corresponds to your JDBC driver setup (and connection name in  Data Source Explorer). 

Select from the drop-down menu the Access connection profile and add any necessary authentication information. Select OK to save all the information.

Your Access database should now be available for use in IRI Voracity-related structured data management tasks supported in IRI Workbench. If you need assistance, please contact your IRI representative or email support@iri.com.