MS Access DB with SAP Lumira
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!!
Great stuff - I works, but when I have an Access database = 1,9 GB, 6 tables, I get this error message
"GC overhead limit exceeded"
Have you seen that before?
I am able to connect to the MS Access DB but I cannot see any tables.Actually when I connected the first time I could see the tables but while previewing it it threw the error: UACExc:::4.0.2 unexpected token: PUBLIC but now I cant see the table itself.Where could I have possibly gone wrong?
I too am suffering the same error as Rene - GC overhead limit exceeded - with any Access database approaching 2GB in size. Main database I want to use currently has >800k records and grows at approximately 25k per day so this error is a major problem for me 🙁
Can you please let me know the instructions on configuring UCanAccess JDBC driver in the BI server.
Thanks & Regards