Utilizing MS Access in Dataiku DSS

Dataiku

Many of our users have shown interest in utilizing MS Access in Dataiku DSS. In the interest of knowledge sharing, we wanted to demonstrate how to do just that.

How to open an MS Access file

1. Download ucanaccess
2. Copy ucanaccess-4.0.4.jar and jackcess-2.1.11.jar (including the ones from lib/) into DATA_DIR/lib/jdbc/ (see Installing database drivers for more details)
3. Restart DSS.
4. Configure a new connection:

• JDBC driver class: net.ucanaccess.jdbc.UcanaccessDriver
• JDBC URL: jdbc:ucanaccess://<filepath> , e.g. jdbc:ucanaccess:///home/johndoe/Documents/fooDB.accdb
• In the “SQL Dialect” field, select "MySQL < 8"

Create a new dataset using this connection to import an MS Access table in DSS.

Connecting to MS Access file with Date/Time data type

Be sure to follow the above steps, but utilize UCanAccess 4.0.4