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.
- Download the latest UCanAccess binary zip file from here: http://sourceforge.net/projects/ucanaccess/files/
- Extract the files
- 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 - Copy the ucanaccess-x.x.x.jar file from the extracted directory to [YourYellowfinInstallDirectory]
/appserver/lib/ - Navigate to [YourYellowfinInstallDirectory]
/appserver/webapps/ROOT/WEB-INF and open the file titled web.xml with your favorite text editor - 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!)
- Save the web.xml file
- Restart Yellowfin
- Create a new Data source from the admin control panel
- Choose “Generic JDBC driver” in the 'Database Type' dropdown
- 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.
- 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!