Cant connect to MS Access mdb file using DBeaver and the default UCanAccess-5.0.0 driver

Question

Get the following error: UCAExc:::5.0.0-SNAPSHOT unexpected token: DOUBLE required: FOR unexpected token: DOUBLE required: FOR

My connection string is: jdbc:ucanaccess:///mnt/toolboss-plt11/sps.mdb everything else contains default values. I believe the mdb file is in Access 2000 format. I am able to connect to this file with Microsoft SSIS jet 4.0 driver.

Any help would be appreciated. Thank you.

Answer 1

You have encountered a bug in UCanAccess/HSQLDB. When I try to open your test file using UCanAccess' console.bat I see

C:\Users\Public\Downloads\UCanAccess>console.bat
java version "1.8.0_241"
Java(TM) SE Runtime Environment (build 1.8.0_241-b07)
Java HotSpot(TM) 64-Bit Server VM (build 25.241-b07, mixed mode)

Please, enter the full path to the access file (.mdb or .accdb): /home/gord/Desktop/test.mdb
Cannot execute:CREATE  CACHED TABLE RATIONING(ITEMTYPE INTEGER,ITEMKEY VARCHAR(50),ISSUETARGETTYPE INTEGER,ISSUETARGETKEY VARCHAR(50),MAXQTY INTEGER,PERIOD DOUBLE,PERIODTYPE INTEGER) unexpected token:
 DOUBLE required: FOR

It appears that PERIOD is a reserved word in HSQLDB 2.5.0 (the version that ships with UCanAccess 5.0.x). However after upgrading to HSQLDB 2.5.1 that same CREATE statement doesn't fail.

You should post a help request on SourceForge asking Marco to update the version of HSQLDB in the UCanAccess distribution. In the meantime you can work around the issue.

Download UCanAccess-5.0.1.bin.zip and unpack it. I put my copy in "/home/gord/Downloads/JDBC/"

Go into the "lib" folder and delete "hsqldb-2.5.0.jar". Download hsqldb-2.5.1.jar to replace it.

In DBeaver, right-click your connection and choose "Edit Connection". Click the "Edit Driver Settings" button and in the Libraries tab delete the "net.sf.ucanaccess.ucanaccess:RELEASE" entry that DBeaver created. Then use the "Add File" button to add the five (5) jars from your download folder

Now you should be able to open your .mdb file.