How to use UCanAccess to connect to Microsoft Access Databases using JDBC only

Here is the easiest way to connect Yellowfin to your Microsoft Access database. This method bypasses much of Access' ODBC setup requirements, and is relatively quick to set up. 

  1. Download the latest UCanAccess binary zip file from here: http://sourceforge.net/projects/ucanaccess/files/
  2. Extract the files
  3. Copy files located in the extracted “lib” directory to [YourYellowfinInstallDirectory]/appserver/lib/ These include commons-lang-2.6.jar, commons-logging-1.1.1.jar, hsqldb.jar, and jackcess-2.1.6.jar
  4. Copy the ucanaccess-x.x.x.jar file from the extracted directory to [YourYellowfinInstallDirectory]/appserver/lib/
  5. Navigate to [YourYellowfinInstallDirectory]/appserver/webapps/ROOT/WEB-INF and open the file titled web.xml with your favorite text editor
  6. Locate the JDBCDrivers section, and add the following line to the top of the block. net.ucanaccess.jdbc.UcanaccessDriver, (See screenshot, make sure to include the comma at the end!) 
  7. Save the web.xml file
  8. Restart Yellowfin
  9. Create a new Data source from the admin control panel
  10. Choose “Generic JDBC driver” in the 'Database Type' dropdown
  11. Select “net.ucanaccess.jdbc.UcanaccessDriver” NOTE: Although the option to select Microsoft Access via the 'Database Type' dropdown may now be accessible, it is very important to select the Generic JDBC Driver option instead.
  12. Use the following connection string (See screenshot for example setup that does not require authentication):

For Linux/Mac servers
jdbc:ucanaccess:///path/to/mdbfile.mdb;showSchema=true

For Windows servers
jdbc:ucanaccess://c:/path/to/mdbfile.mdb;showSchema=true

The database should now be accessible to Yellowfin, go and build yourself a view!

Is article helpful?