Cannot connect to +300mb size ms access file (*.accdb)

[Gintaras]

Hello,
i am having issues connecting to one of my Access db which is pretty large + 350mb ( lots of forms, macro code )

used standard approach, which worked and still is working with other applications:

//------------------------> Code : Start<----------------------------

public boolean validateQualityDatabase (){
try {
// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
} catch (ClassNotFoundException ex) {
System.err.println("!Class Database: " + ex);
}

    try (Connection conn = DriverManager.getConnection(getDBFullPath(), "username", strPassword)) {
        System.out.println("Connected to DB..");
        DatabaseMetaData md = conn.getMetaData();
        ResultSet rs = md.getTables(null, null, "%", null);
        String temp;

        while (rs.next()) {
            temp = rs.getString(3);
            if (!temp.contains("MSys")) {
                System.out.println("Found:" + temp);

            }
        }

    } catch (SQLException e) {
        System.out.println("Error: " + e);

    }

    return isDatabaseCorrect;
}

//------------------------> Code : END <----------------------------

getDBFullPath() returns string ["jdbc:ucanaccess://C:\SRFS-Database.accdb;Showschema=true"]

After running for a long time it generates folowing Error:

-------------------------> Error: Start <--------------------------
Exception in thread "JavaFX Application Thread" java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1762)
at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1645)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Node.fireEvent(Node.java:8216)
at javafx.scene.control.Button.fire(Button.java:185)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3724)
at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3452)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1728)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2461)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:348)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:273)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:382)
at com.sun.glass.ui.View.handleMouseEvent(View.java:553)
at com.sun.glass.ui.View.notifyMouse(View.java:925)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$141(WinApplication.java:102)
at com.sun.glass.ui.win.WinApplication$$Lambda$37/29531133.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1757)
... 47 more
Caused by: java.lang.OutOfMemoryError: Java heap space
at java.util.ArrayList.iterator(ArrayList.java:834)
at com.healthmarketscience.jackcess.impl.TableImpl.getColumn(TableImpl.java:424)
at com.healthmarketscience.jackcess.impl.TableImpl.getColumn(TableImpl.java:72)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:595)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTable(LoadJet.java:564)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTable(LoadJet.java:551)
at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:665)
at net.ucanaccess.converters.LoadJet$TablesLoader.access$3(LoadJet.java:655)
at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1060)
at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:193)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at srfs.model.daoobjects.Database.validateQualityDatabase(Database.java:77)
at srfs.view.SRFSController.handleTbuttonClick(SRFSController.java:41)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1757)
at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1645)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)

-------------------------> Error: End<--------------------------

Used different parameters like memory=true, which makes application run forewer without trowing an error. did run fro 30 minutes and nothing

In the same locatione where db is located folder "Ucanaccess_net.ucanaccess.jdbc.DBReference@100503e" size 1.5 GB

used Jackcess library to connect to the same db and it worked fine.
//------------------------> Code : Start <----------------------------
// Jackcess
try (com.healthmarketscience.jackcess.Database db = DatabaseBuilder.open(new File(strDatabasePath))) {
Table tbl = db.getTable(tblName_SRFSReportData);
System.out.println("Column names as reported by Jackcess:" + tbl.getName());
for (Column col : tbl.getColumns()) {
System.out.println(col.getName());
}
} catch (IOException e) {
e.printStackTrace(System.out);
}
String url = "jdbc:odbc:Driver={Microsoft Access Driver (.mdb, .accdb)};DBQ=" + strPath;

//------------------------> Code : End<----------------------------

Please Help me to resolve is as I would like to use UcanAccess library for current project?

[Marco Amadei]

Macro code and forms haven't effect on memory occupation if you don't use the Sysschema=true setting.
Only the data size should affect the memory occupation without this advanced setting.
Did you change the -Xmx parameter (e.g., -Xmx1G)?

[Gintaras]

Thank you Marco fro quick reply,

Db do not have any Ole Objects, tried with -Xmx1g as my system fails allocating 2 gb of memory.

with 1 GB i am getting different error message:

WARNING:Out of Memory in statement [ALTER TABLE TBL_DATARESULTS_SORTEDBYCRITERIA ADD PRIMARY KEY (ID) ]
WARNING:Out of Memory in statement [CREATE INDEX TBL_DATARESULTS_SORTEDBYCRITERIA_AGE_ID ON TBL_DATARESULTS_SORTEDBYCRITERIA (AGE_ID) ]
Exception in thread "JavaFX Application Thread" java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1762)
at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1645)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Node.fireEvent(Node.java:8216)
at javafx.scene.control.Button.fire(Button.java:185)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3724)
at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3452)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1728)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2461)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:348)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:273)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:382)
at com.sun.glass.ui.View.handleMouseEvent(View.java:553)
at com.sun.glass.ui.View.notifyMouse(View.java:925)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$141(WinApplication.java:102)
at com.sun.glass.ui.win.WinApplication$$Lambda$37/29531133.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1757)
... 47 more
Caused by: java.lang.OutOfMemoryError: Java heap space

[Marco Amadei]

Ok, I need some more informations.
What's exactly the accdb dimension?( What does + 350mb exactly mean? )
Could you share it someway?
What are the total and free memory values at the moment you try to connect with the database?

...
System.out.println("totalMemory="+Runtime.getRuntime().totalMemory());
System.out.println("freeMemory ="+Runtime.getRuntime().freeMemory());
Connection conn =....


[Gintaras]

Database size is 397MB ( Meaning of + 300 MB)

Memmory usage results are below:
...
totalMemory=16252928
freeMemory =7438664
...

I tried to run code with Memory=false and after 94 minutes running process was still unfinished. Cached DB Folder "Ucanaccess_net.ucanaccess.jdbc.DBReference@682715" did grow to 4.27GB. Hope this helps.

Let me know if you need any more details to find a solution to this problem

[Marco Amadei]

With just these elements I can't figure out why a -Xmx1G isn't enough(it should be enough -Xmx512M).
In all my tests, with memory=false and a 400M sized database, a folder of about 500M-700M is generated. And the very first connection in the vm life should just take 1-2 minutes.
Also, with a proper parameters combination, I've successfully tested the connection to several 2G sized databases.
So there should be somewhat buggy(and maybe looping), no doubt about it.
I would be great for me to have your database: I'll do optimizations about memory usage in the next versions but your specific case is totally unclear to me and totally out of line.
-Is it impossible to have the database for my analysis?
There should be private data, still a copy without data(and obviously without macros and forms) would be useful. If so, you can send it(without data) to my private email amadei.mar@gmail.com.

If you open the Ucanaccess_net.ucanaccess.jdbc.DBReference@682715 folder, you should see a number of files.
-Could you let me know which of those files has a large size?(filename-size).
-If there aren't OLE objects, are there attachments?
-Or tables linked to other mdb/accdb files?

Thank you in advance.

[Gintaras]

Database sent, hope this will help you to find the problem.
Let me know if any more information or data is required locate/fix the issue.

[Marco Amadei]

Okey, thank you so much, I'll let you know my findings asap.