Very basic problem: Can't connect to DB

[Alexis]

Hello.

I'm trying to connect to an Access database (.accdb), but I have failed several times. Before everything, I followed this.

I'm using Netbeans 8.1 and tried with both packages: Ucanaccess 3.0.3.1 and Ucanaccess 2.0.9.5.

Code:

//import everything for ucanaccess.. until I found the problem.. I dont want to miss an import
import java.sql.*; //the only one of all that is detected as used by Netbeans
import com.healthmarketscience.jackcess.*;
import com.healthmarketscience.jackcess.util.ImportUtil;
import net.ucanaccess.converters.TypesMap.AccessType;
import net.ucanaccess.ext.FunctionType;
import net.ucanaccess.jdbc.*;

//LOT OF CODE

private ResultSet accessDB(String file, String folder, String select, String from){
        //conection to AccessDB
        try{
            //Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); ->Have tried with and without this line
            String url = "C:/Users/myname/Desktop/my_db.accdb";
            //I have tried also with url= "C:\\Users\\myname\\Desktop\\my_db.accdb"
            Connection conn = DriverManager.getConnection("jdbc:ucanaccess://"+url);
            // NO MORE CODE!!.. I GET AN ERROR BEFORE HERE! .. =(
            return null;
        }
        catch (Exception e){
            JOptionPane.showMessageDialog(null, "Error 08: ".concat(e.toString()), "Error", JOptionPane.ERROR_MESSAGE);
            return null;
        }
    }

And of course I have added all the 5 .jar files (ucanaccess, commons-lang, commons-logging, hsqldb, jackcess).

Please.. I'm getting mad with this problem. I have read a lot, and tried manythings, but without success.

Much thanks for your time and help.

Best regards.

[Marco Amadei]

We need the excepion stack trace, may you post it?

[Alexis]

ERROR:
java.sql.SQLException: No suitable driver found for jdbc:ucanaccess//C:/Users/P_AApablaza/Desktop/Alexis_Entel/DB/OSS13_Export_GSM.accdb

or

java.sql.SQLException: No suitable driver found for jdbc:ucanaccess//C:\Users\P_AApablaza\Desktop\Alexis_Entel\DB\OSS13_Export_GSM.accdb

EXCEPTION TRACE:

java.lang.ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Unknown Source)
    at entel.ge.EntelGE.accessDB(EntelGE.java:439)
    at entel.ge.EntelGE.crearKMZbasico(EntelGE.java:263)
    at entel.ge.EntelGE.KMZbasicoButtonActionPerformed(EntelGE.java:218)
    at entel.ge.EntelGE.access$000(EntelGE.java:37)
    at entel.ge.EntelGE$1.actionPerformed(EntelGE.java:93)
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$500(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

and with the Class.forName line:

ERROR:
java.lang.ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver

TRACE:

java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://C:/Users/P_AApablaza/Desktop/Alexis_Entel/DB/OSS13_Export_GSM.accdb
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at entel.ge.EntelGE.accessDB(EntelGE.java:440)
    at entel.ge.EntelGE.crearKMZbasico(EntelGE.java:263)
    at entel.ge.EntelGE.KMZbasicoButtonActionPerformed(EntelGE.java:218)
    at entel.ge.EntelGE.access$000(EntelGE.java:37)
    at entel.ge.EntelGE$1.actionPerformed(EntelGE.java:93)
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$500(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

Thanks you for your time and help =)

[Gord Thompson]

You are missing the colon before the two slashes. The complete URL should look like

jdbc:ucanaccess://C:/path/to/database.accdb

Also, just omit the Class.forName statement as it is not required.

[Alexis]

sorry, I missed them when I wrote it here, but the original code is this one (and the one who makes the trace):

Connection conn = DriverManager.getConnection("jdbc:ucanaccess://C:/Users/P_AApablaza/Desktop/Alexis_Entel/DB/OSS13_Export_GSM.accdb");

[Marco Amadei]

You have not the needed jars in the classpath. Where did you add them in your net beans project?

[Alexis]

[Alexis]

You were right. Totally my fault.
I was using the generated .jar file, but without copying the "Lib" folder.
Now I can connect. Sorry for my dummy problem. And thanks for all.

SOLVED.

[Marco Amadei]

you're welcome

[Alexis]

Sorry Marco.. But I'm still trapped in the connection. =/

But this time NO ERROR is generated (tried to operate in the Netbeans' project folder).

CODE:

System.out.println("Conectarme a: "+folder+file); //to print folder+file
Connection conn = DriverManager.getConnection("jdbc:ucanaccess://"+folder+file);

the output of the println: "Conectarme a: C:\Users\P_AApablaza\Documents\NetBeansProjects\EntelGE\OSS13_Export_LTE_160119.accdb"

So the Connection conn should be:

 Connection conn = DriverManager.getConnection("jdbc:ucanaccess://C:\Users\P_AApablaza\Documents\NetBeansProjects\EntelGE\OSS13_Export_LTE_160119.accdb");

(By the way.. the db is there)

TRACE:

Exception in thread "AWT-EventQueue-0" java.lang.OutOfMemoryError: Java heap space
    at org.hsqldb.result.Result.newResult(Unknown Source)
    at org.hsqldb.result.Result.newErrorResult(Unknown Source)
    at org.hsqldb.StatementDMQL.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.executeCompiledBatchStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.executeBatch(Unknown Source)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:845)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTablesData(LoadJet.java:1003)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:1047)
    at net.ucanaccess.converters.LoadJet$TablesLoader.access$2900(LoadJet.java:273)
    at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1485)
    at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:245)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:270)
    at entel.ge.EntelGE.connectDB(EntelGE.java:448)
    at entel.ge.EntelGE.crearKMZbasico(EntelGE.java:267)
    at entel.ge.EntelGE.KMZbasicoButtonActionPerformed(EntelGE.java:214)
    at entel.ge.EntelGE.access$000(EntelGE.java:33)
    at entel.ge.EntelGE$1.actionPerformed(EntelGE.java:89)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6535)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
    at java.awt.Component.processEvent(Component.java:6300)
    at java.awt.Container.processEvent(Container.java:2236)
    at java.awt.Component.dispatchEventImpl(Component.java:4891)
    at java.awt.Container.dispatchEventImpl(Container.java:2294)
    at java.awt.Component.dispatchEvent(Component.java:4713)

[Marco Amadei]

On the way, what's the accdb size?

[Alexis]

The maximum one: 580m
(there are 10 db.. but I open and close each one at a time)
I found two possible solutions (that seems to work): add +";memory=false" to the connection.
Or increasing the heap with -Xmx600m (but I don't know how to do this method with .jar file.. because it's not working for me right now).

But both are really slow. Is there any other method to connect quicker? I don't need the whole db, but to connect to some tables (they have like 50 tables each, and I want to use only 2 and for read only).

[Marco Amadei]

You need a structured answer and it's difficult with my cell. Yes there is a way, but please wait till tomorrow morning.

[Alexis]

Thanks Marco. Of course I will wait. =)

[Marco Amadei]

Firstly, to close the connection hasn't the expected effect if you don't use the singleconnection=true connection parameter(because ucanaccess uses a cache mechanism by default).
A way to load just the tables needed, (which will improve dramatically both the first-connection-time and the memory occupation):
you have just to use another "filter" accdb which only points to the tables you need for your queries, linking them as external resources. In other words, they must be used as linked tables from a main db. Your application will connect to that main db.

[Alexis]

The problem of using a new DB with linked tables, is that I want a program that can be run on any conmputer, in any folder. The linked DB don't use Relative path, but canonical one, so changing the folder path affects the calls for tables.
Where should I write the "singleconnection=true" ? At the end of the connection line, no? (as a parameter)

Much thanks for your help

[Marco Amadei]

yes, of course
Connection conn=DriverManager.getConnection("jdbc:ucanaccess://c:/pippo.mdb;SingleConnection=true");
Like that the memory=false parameter shouldn't be needed.
For the other issue, you may want to try the Remap connection parameter (see the ucanaccess website about it).

[Balaji Srikaanth P]

Hi Gord
I followed your suggestions in the forum. Still it is not working for me.
BTW
I downloaded ucanaccess- 4.0.4.zip. It contained only four jar files
namely
commons-lang-2.6.jar
commons-logging-1.1.3.jar
hsqldb.jar
jackcess-2.1.11.jar
My code goes as follows

import java.sql.*;
class Test{
public static void main(String ar[]){
try{
String url="jdbc:ucanaccess://E:/First.accdb";
Connection c=DriverManager.getConnection(url);
Statement st=c.createStatement();
ResultSet rs=st.executeQuery("select * from first");
while(rs.next()){
System.out.println(rs.getString(1));
}
}catch(Exception ee){ee.printStackTrace();}
}
}

I have set the classpath values as follows
CLASSPATH=.;C:\Program Files\Java\jdk10.0.1\lib;

I go the following exception message
java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://E:/First.accdb
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)

Then I copied all the above mentioned jar files and this Test.java to a folder
e:/ucan
Again I tried the following command
java -cp e:/Ucan;;e:/UCan/commons-lang-2.6.jar;e:/UCan/commons-logging-1.1.3.jar;e:/UCan/hsqldb.jar;e:/UCan/jackcess-2.1.11.jar Test

still I get the following exception
E:\ucan>java -cp e:/Ucan;;e:/UCan/commons-lang-2.6.jar;e:/UCan/commons-logging-1.1.3.jar;e:/UCan/hsqldb.jar;e:/UCan/jackcess-2.1.11.jar Test
java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://E:/First.accdb
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
at Test.main(Test.java:7)

    Can u pl suggest a solution or tell me where I am going wrong.

    Thanks in advance
    PBS

[Gord Thompson]

You've only mentioned the four (4) .jar files in the "lib" folder. You're missing the main driver file "ucanaccess-4.0.4.jar" in the root folder of the zip file.

At a minimum, your "E:\Ucan" folder should contain

ucanaccess-4.0.4.jar
lib\commons-lang-2.6.jar
lib\commons-logging-1.1.3.jar
lib\hsqldb.jar
lib\jackcess-2.1.11.jar

Then you can run your code using

java -cp .;E:\Ucan\ucanaccess-4.0.4.jar;E:\Ucan\lib\* Test