Connection from LibreOffice Base is readonly

[Jörg Arndt]

Hi, I made my first steps with UCanAccess. I use Xubuntu 18.04
This is what I did:
Downloaded UCanAccess-4.0.4-bin.
in LibreOffice added ClassPath .../UCanAccess-4.0.4-bin/loader/ucanload.jar
connect the Database: jdbc: ucanaccess:///home/path/ to my/ database.mdb
driverclass: net.ucanaccess.jdbc.UcanloadDriver

Now I have connetion to my access.mdb Database. I can see the tables, but there is no way to change the data in it. Then I tried to create a new table and got the error: UCAExc:::4.0.4 connection exception: closed
Nevertheless the new table has been created but it's also readonly.
If I open the database with access, I can write into the new table.
Any suggestions?

~~~

[Gord Thompson]

Try using UCanAccess' console.sh utility to open the database file on your Xubuntu machine and see if it reports any issues as it processes the tables and views.

If no errors or warnings are displayed, try executing the statement

CREATE TABLE foo (bar LONG PRIMARY KEY);

and let us know what errors (if any) are reported.

[Jörg Arndt]

Hi, there a no errors. The table foo has been created without any problems. I could open the database with LO and delete the new table from there. But that's all I could do.

[Gord Thompson]

I just tested under Xubuntu 18.04 with UCanAccess 4.0.4 and LibreOffice Base 6.0.6.2 (from the Ubuntu repositories). I didn't get the read-only problem but I did reproduce the issue with creating a table from within LO Base. I got the "connection exception: closed" error, but if I closed LO Base and re-opened the database the new table was there and I could add rows to it.

To try and work around the table creation problem I experimented with adding a few options to the connection string (e.g., showSchema=true, immediatelyReleaseResources=true, preventReloading=true) but none of them seemed to help.

I'll wait a bit and see if Marco has any suggestions for other possible workarounds. If not, then I'll open a ticket for the table creation issue.

As for the read-only issue, if you can provide a sample database that will reproduce the issue I'll try and replicate it here.

[Marco Amadei]

Are you sure that the bug is OS dependent? Tonight I'll check for it and let you know my findings.

[Gord Thompson]

It does appear that the "Create Table ..." issue may be OS-dependent, or possibly LibreOffice version-dependent. I just tried a quick test with LibreOffice Base 6.1.3.2 under Windows_7 and it seems to be working fine. I could create a table without errors and then immediately open it and add rows to it.

[Gord Thompson]

Update: The issue is apparently not associated with LibreOffice Base 6.0.6.2. I just tried it on Windows_7 and it worked fine as well. So the issue does seem to be related to running on Linux.

[Jörg Arndt]

I think, I can describe the error better now: I created a new access Database (test.mdb) and it works in LO without readonly-problem. Then I created "access-test.mdb" and linked the tabels from "test.mbd" to it (because the original database I want to have access to is divided into frontend and backend).
Now I openend "test.mdb" in LO again and it's read-only.

[Gord Thompson]

How did you create the table links? If you created them in Access on Windows then the file paths in those table links may not be meaningful to Xubuntu (e.g.,

C:\Users\Public\backend.mdb

is fine for the local Windows machine but has no meaning under Linux). If that's the case then you may need to add a remap parameter to your JDBC connection string (details here).

[Jörg Arndt]

yes, I created the links in Access on Windows and tried to open backend.mdb with UcanAccess. If I understand it right, I have to use the remap parameter if I want to open frontend.mdb?

[Gord Thompson]

Perhaps an example might help. Say I have a front-end file in a Windows folder and that file contains linked tables pointing to another Windows-hosted back-end file located at

C:\Users\Public\test\accounts\data\backend.accdb

The front-end file contains that path information and both Access and UCanAccess on that Windows machine can find the back-end file.

However, on my Xubuntu virtual machine the path

C:\Users\Public\

is mounted as

/media/sf_Public/

so the Xubuntu path to the back-end file is actually

/media/sf_Public/test/accounts/data/backend.accdb

If I try to open the front-end file from Xubuntu using the URL

jdbc:ucanaccess:///media/sf_Public/test/accounts/app/frontend.accdb

then UCanAccess can find the native tables in the front-end file but it cannot resolve the links to the back-end file because the path "C:\Users\Public..." is not valid. Instead. I have to use the URL

jdbc:ucanaccess:///media/sf_Public/test/accounts/app/frontend.accdb;remap=C:\Users\Public\test\accounts\data\backend.accdb|/media/sf_Public/test/accounts/data/backend.accdb

[Jörg Arndt]

Thank you for that helpful explanation, but it don't solve my current problem.
I have frontend and backend on the virtual Window-machine. What I want is to open the backend with LibreOffice Base and to modify the Data.
I can open it with ucanaccess (so there is no problem with the path), but the tables are readonly. As I figured out, this problem occurs when the tables are linked. As said above: I created a new access Database (test.mdb) and it works in LO without readonly-problem.
Then I created "access-test.mdb" and linked (in Access on Windows) the tables from "test.mbd" to it.
Now I openend "test.mdb" in LO again and it's read-only.
Using Access there is no problem to link the tables to frontend2.mdb and modify them there

[Jörg Arndt]

can You reproduce the issue?

[Gord Thompson]

Unfortunately, no. I cannot reproduce the read-only issue as you've described it.

Simply creating linked tables in a front-end file does not affect the back-end file in any way. It merely adds DAO.TableDef objects in the front-end file with the linking information that points to the back-end tables (file path and table name). I verified that by comparing copies of the back-end file before and after creating linked tables in the front-end file:

C:\Users\Public\test\accounts\data>dir
 Volume in drive C is OS
 Volume Serial Number is X327-P118

 Directory of C:\Users\Public\test\accounts\data

2018-11-14  08:51    <DIR>          .
2018-11-14  08:51    <DIR>          ..
2018-11-14  08:50           462,848 backend.accdb
2018-11-14  08:50           462,848 backend_old.accdb
               2 File(s)        925,696 bytes
               2 Dir(s)  305,783,603,200 bytes free

C:\Users\Public\test\accounts\data>fc /b backend.accdb backend_old.accdb
Comparing files backend.accdb and BACKEND_OLD.ACCDB
FC: no differences encountered

[Jörg Arndt]

I just upgraded to LO 6.0.7.3. It's the same issue. In the same mdb-file some conneced tables are readonly, some not. It's wired.
Also it's not possible to copy a table. I choose paste and nothing happens.

[Jörg Arndt]

I installed LinuxMint on a virtual machine and tried from there. Is the same problem. May I sent you the access-file to have a look on it?

[Gord Thompson]

Yes, it might be very helpful to have a look at the actual database file that is causing difficulties for you. Feel free to post it as an attachment to a reply on this thread.