write to msaccess database

[sanderlenselink]

Hi,

since sometime I’m confused because I cannot write data from KNIME to my msaccess database.

The table is created by msaccess but no data is populated.

Attached a simple flow

Is there anyone who sees my mistake or sees a solution

THNX

msaccess_test1.knwf (17.2 KB)

[mlauber71]

@sanderlenselink you could try the standard driver

Access Databases - [lnet.ucanaccess.Complex.SingleValue]

This is because these are multivalue fields and the driver seems to struggle with extracting them. You although can use UCanAccess driver and in this case Python to extract the values. I wan not able to integrate the driver directly into KNIME so I went with Python instead. Should run on macOS and Windows machines. [image] You will have to modify the code though to extract your fields and databases but it should show you how this can be done.

You sample file does not include any data. You could try and use DB Insert instead of Write and see if that makes a difference.

demonstrates the use of DB Update and DB Merge with MS Access DB – mlauber71

Also you can try this example based on your case:

msaccess_test1.knwf (127.6 KB)

[sanderlenselink]

@mlauber71

it looks that your example works . . . the DB Reader shows the content of the Table Creator (see screenshot).

However, the msaccess database with table1 is still empty.

Because I didn’t trust myself I created a little flow directly from the msaccess database. In KNIME it shows the contect of the Table Creator.

I also created another table (Table2) but the same result. No content if I look in msaccess but the DB Reader shows the expected content of the Table Creator.

It seems like KNIME is fooling me . . . a result within KNIME and no results in msaccess.

Could it be that either KNIME or msaccess requires a specific setting.
But I don’t believe that either because previously they didn’t have to.

Or could it be that my path to msaccess is the problem (C:\Users\xxxxx\Desktop\knime_msaccess.accdb )

Hopefully you have another suggestion cq solution

[msaccess_test1_from_KNIME_team1.knwf|attachment]

(upload://ps4Ndx7qePIZUEp9VTEpgV4nqUn.knwf) (128.5 KB)

[mlauber71]

@sanderlenselink the data is also there if you open the MS access file. Maybe you have to make sure you really open the right one?

Maybe you can try my example and deliberately delete the table in the MS Access file and try again to see if the data will appear?

The files you wanted to upload are not actually there.

[sanderlenselink]

Hi @mlauber71

I appreciated your last suggestions. I tried them earlier and did try them again but no improvement.

Therefore I dived deeper and discovered that other processes of msaccess were running and probably caused the problem.

I tried to kill all the processes but that wasn’t sufficient. I restarted my machine and now it all works again

The big question is of course “what causes the running of multiple processes”. I don’t know. Sometimes there’s a cause and many times not.

Or has someone from the KNIME-team an idea and coulc the problem be caused by the DB Writer node?

THNX for your help