Micrrcosoft Access Connector doesn't stop loading

[rastasanu]

Hi everyone,

I have some Access databases to read on KNIME locally, but the node just keeps on loading without even beginning to execute for an indefinite amount of time.
I tried changing the location of the .accdb and also converting it to a .mdb but there’s the same issue. I also tried creating a new empty .accdb but still got the same issue.
I am using the KNIME AP 5.3.0, is there any bug related to that or maybe I am missing something for the configuration. I have everything on default.
Also nothing interesting pops out in the logs even at debug level, here are the only logs I am getting :

2024-10-18 15:43:31,824 : DEBUG : comm-pool-thread-11 :  : NodeContainer :  :  : Microsoft Access Connector 4:1 has new state: CONFIGURED_MARKEDFOREXEC
2024-10-18 15:43:31,825 : DEBUG : comm-pool-thread-11 :  : NodeContainer :  :  : Microsoft Access Connector 4:1 has new state: CONFIGURED_QUEUED
2024-10-18 15:43:31,825 : DEBUG : comm-pool-thread-11 :  : NodeContainer :  :  : 04 - Effets MR 4 has new state: EXECUTING
2024-10-18 15:43:31,829 : DEBUG : KNIME-Worker-2-Microsoft Access Connector 4:1 :  : WorkflowManager : Microsoft Access Connector : 4:1 : Microsoft Access Connector 4:1 doBeforePreExecution
2024-10-18 15:43:31,830 : DEBUG : KNIME-Worker-2-Microsoft Access Connector 4:1 :  : NodeContainer : Microsoft Access Connector : 4:1 : Microsoft Access Connector 4:1 has new state: PREEXECUTE
2024-10-18 15:43:31,831 : DEBUG : KNIME-Worker-2-Microsoft Access Connector 4:1 :  : WorkflowDataRepository : Microsoft Access Connector : 4:1 : Adding handler 5a8b0f2b-a034-4b2a-8cf1-dd61a12446c7 (Microsoft Access Connector 4:1: <no directory>) - 1 in total
2024-10-18 15:43:31,831 : DEBUG : KNIME-Worker-2-Microsoft Access Connector 4:1 :  : WorkflowManager : Microsoft Access Connector : 4:1 : Microsoft Access Connector 4:1 doBeforeExecution
2024-10-18 15:43:31,831 : DEBUG : KNIME-Worker-2-Microsoft Access Connector 4:1 :  : NodeContainer : Microsoft Access Connector : 4:1 : Microsoft Access Connector 4:1 has new state: EXECUTING
2024-10-18 15:43:31,831 : DEBUG : KNIME-Worker-2-Microsoft Access Connector 4:1 :  : LocalNodeExecutionJob : Microsoft Access Connector : 4:1 : Microsoft Access Connector 4:1 Start execute
2024-10-18 15:43:31,832 : DEBUG : KNIME-Worker-2-Microsoft Access Connector 4:1 :  : DBSessionCache : Microsoft Access Connector : 4:1 : New database session: DefaultDBSessionInformation(id=7f7b64a5-c386-4c20-bcad-8a8ec3c066e6, dbType=DBType(id=msaccess, name=Microsoft Access, description=null), driverDefinition=DBDriverDefinition(id=built-in-msaccess-5.0.1, name=Driver for Microsoft Access v.5.0.1, version=0.0.0, driverClass=net.ucanaccess.jdbc.UcanaccessDriver, dbType=DBType(id=msaccess, name=Microsoft Access, description=null), description=, origin=EXTENSION, deprecated=false), connectionController=org.knime.database.extension.msaccess.node.connector.MSAccessDBConnectionController=(url=jdbc:ucanaccess://C:\Users\9942991\Downloads\04 - Analyse_PCH.mdb, authenticationType=NONE), dialectId=msaccess, attributeValues={knime.db.connection.metadata.configure.enabled=false})

Thanks for anyone willing to help!

Cheers,
Cristian

[rastasanu]

So I have so far tried using different drivers and use the UCanAccess JDBC manually through the DB Connector and I have the same problem:

• The node keeps loading
• The resources are being used (RAM usage increases but doesn’t reach the limit)
• My PC slows down during the execution

It works however with smaller and simpler .accdb files but this one which is around 1GB is a tough cookie.

Have to mention however that I tried it in Alteryx and it works extremely fast, perhaps someone knows if they use a different driver which I could try?

Thanks to anyone willing to help!

[llepome]

Hi Christian!
Have you considered using DB Transaction Start and End nodes or DB Connection Closer?

[izaychik63]

See if this can help you

Microsoft Access Connector Java Heap Space

Hello @mpsz, can you increase memory to 6GB and then give it a try? Additionally check this topic where @mlauber71 shared his experience with big MS Access databases: Br, Ivan

[rastasanu]

Hi @llepome
I cannot use these nodes as I can’t even get the connection to work so I can’t therefore close it or start a DB Transaction or End it. But thanks for the reply!

@izaychik63 I have seen the posts and tried the proposed solution, but I have come to no result for my problem.

The only thing maybe that could help to narrow down the issue could be that I am getting this warning when opening the file in MS Access:

But in any case, I can still access the file and it works well in Alteryx so I doubt it that the file might be the cause.

I have tried to increase the RAM allocated to KNIME and to run a Garbage Collector.
I have tried also compressing the .accdb file and nothing.

An additional info is that after I try running it and waiting and stopping the execution the node in general would stop working properly, as in it would execute (still without end) even if I put a random address for the file.

Thanks for the reply! Perhaps it is a bug when it comes to bigger files.

Welcoming any other thing that I could try.

Cheers,
Cristian

[izaychik63]

It seems you have windows/Access issue. Every time you place file in new location on opening it asks if you trust the location and allow access. Alterix may ignore this security warning. Try to open your file from local Access installation and resolve trusted center issue.

[rastasanu]

I tried it but still it doesn’t work even if the file doesn’t have that warning anymore.
Moreover after I try once to run the node it kind of bugs and if I reset it and put a random address and execute it it still goes on like that:

I have to restart the whole KNIME to make it give an error if I put a random address.

[mlauber71]

@rastasanu could you do these steps

Check if you can run this sample workflow with two types of MS Access databases

Insert large text/string data into an MS Access file with the use of the MEMO...

Also can you try and compact and repair your database under database tools. Make sure to have a copy saved somewhere though.

I the screenshot the path looks strange - is that because you removed it from the screenshot. Are you sure this is the path to your databse and not your attempt to load the driver?

[rastasanu]

Hi @mlauber71 and thanks for the reply.

I tried to run the sample workflow and it works fine for both types of MS Access databases.

For the path, I put the screenshot indeed to show that even with a weird path which should not be working it loads on anyways. This behavior happens though only after I try loading the problematic database file and stopping the execution. If I restart KNIME AP then it fails at it should.

For the path I tried many options just to check but it still doesn’t work:


And tried also putting it in the workflow data area but it shows the same behavior:

I tried also the compact and repair and still no change

[tobias.koetter]

Hello @rastasanu ,

the driver KNIME uses requires a lot of memory and time to open large Access files (e.g. 25 min for a 900MB file) since it copies all the data into memory during startup as described here.

Can you please try to increase the memory of your Analytics Platform and move the file to a local hard drive instead of a shared drive and give the node some time to execute. Another alternative would be to reduce the size of the database file or export the data you need to a file.

Regarding the stopping problem, KNIME aborts the connection process but it seems that the driver still loads the database in the background which is why the node just accepts any path and runs forever.

Bye
Tobias

[mlauber71]

@rastasanu in general MS Access is not the best way to store large amounts of data on a local machine. You might want to check SQLite or H2. H2 would also support the storage of large files in split database files:

DB Writer to Access DB Hangs at 99%

@qdmt one idea could be to test H2/SQL database: http://h2database.com/html/advanced.html#limits_limitations You could create a new database connection with a current H2 driver (from Maven) and add the “split” parameter to the connection string: jdbc:h2:split:[location=file?<file>][location=in-memory?mem:<database>] [image] You will have to select this new “h2_split” database driver in the H2 Connector instead of the built-in one: [image] The data would then be stored in a local H2/S…

Another option would be to store data as split parquet files if you would not need database operations on the file.

[rastasanu]

Thank you @tobias.koetter for your inference, it is indeed the reason you mentioned.
I tried to reset everything and make sure there’s no process running in the background and indeed after waiting for about an hour it finished the execution (relief).
Thanks again for the information, it’s very educational!

[rastasanu]

Thanks for the suggestions @mlauber71 !
Seeing that with Access the loading time takes forever I will follow your suggestion to store the data in a Parquet.
I’ll do that after loading it from Access for further processes and re-creating the Parquet every month to account for changes which occur to the Access which is stored on a shared drive and updated by other people.

Will see if they are open to implement a H2 solution as well, but that can be tough hehe
Thanks a lot, and have a great day!

Cristian

[mlauber71]

@rastasanu

Seeing that with Access the loading time takes forever I will follow your suggestion to store the data in a Parquet.

Parquet is very useful. It doe a good job at compressing data and you can also use it with chunks to handle separately and they will then form a single file.

knime assign failed, request status: data overflow incoming data too big

@RVC2023 one way to get data from and to R is to use Parquet files. You can use them in one go or you can use chunks which is especially useful if you have large data. [image] The large file can be split into smaller parts. If they have the same structure you could also write them in a loop and later import them back as one file: [image]

More on handling large large datasets you can find here:

KNIME Snippets (1): Collect and Restore — or how to handle many large files...

[izaychik63]

MS Sql Server Express edition may also works.