MS Access DB with SAP Lumira

[former_member374259]

A lot of enterprises have business units that store data in MS Access Databases. I have been looking for a way to leverage SAP Lumira to analyze the data stores in these Access DBs. Unfortunately, I was unable to find an eay way to connect from Lumira to MS Access until I ran into a video by Jason White- https://www.youtube.com/watch?v=g1-ykgDYCBc. The video is pretty good, however, I still had a few challenges getting it to work all the way. So I decided to document all the steps that I did. Hopefully, you will find this useful as well.

This solution relies on leveraging the generic JDBC driver option in Lumira. This option combined with a freely available JDBC driver for MS Access works like a charm. Here are the steps to follow.

Go to https://sourceforge.net/projects/ucanaccess/ and download the latest available driver version. It will download as a zip file.



 

Unzip to any folder of your choice. Go to the unzipped folder and move the jar file to the lib folder. This way you have all of your necessary driver files in one location.



 

Launch SAP Lumira Desktop and go to File > Preferences > SQL Drivers. Then select the option for "Generic JDBC Datasource - JDBC drivers" and click "Install Drivers".



Navigate to the "lib" folder and select all the files in it. Then click on "Open"



You will now be back to the "Preferences' pop up and the "Generic JDBC Datasource - JDBC drivers" option would have turned Green. Click on "Done'. Restart SAP Lumira.



 

You are now ready to connect to your Access DB using Lumira Desktop.

Go to "New Document" and select the option "Query with SQL".



 

Select "Generic JDBC datasource - JDBC Drivers" and click Next.



Enter your credentials to the Access DB and fill the JDBC URL and JDBC Class and then click "Connect". Make sure that you update the JDBC URL with the path to your Access DB.



Make sure you use the following:

JDBC URL: jdbc:ucanaccess://<Path to your MS Access DB>;showschema=true
JDBC Class: net.ucanaccess.jdbc.UcanaccessDriver

Select the table that you want to query or enter your query directly. Make sure you remove the quotations from the SQL Query and click "Preview".





 

You can now visualize and analyze your Access data in Lumira!!!!

If you have Lumira Server for BI Platform available as well, you can Publish this analysis to Lumira Server. You will need to ensure that you update the server with this driver. Also, make sure that your server can access the MS Access DB.

Hopefully, using this guide will make connecting your Lumira Desktop to MS Access DB seamless. Happy Analyzing!!