Bug 43187 - EDITING - MSAccess 2007/2010 (.accdb) table data not editable/updatable - read only with 32 bit and 64bit LibreOffice
Summary: EDITING - MSAccess 2007/2010 (.accdb) table data not editable/updatable - rea...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Master old -3.6
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL: http://wiki.openoffice.org/wiki/Conne...
Whiteboard:
Keywords:
: 91189 (view as bug list)
Depends on:
Blocks: Database-Import Database-MS_Access
  Show dependency treegraph
 
Reported: 2011-11-23 01:44 UTC by Alex Thurgood
Modified: 2018-03-19 11:46 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2011-11-23 01:44:31 UTC
Now that the bug for gaining access to MSAccess files has been fixed in master, I can report another bug.

How to reproduce :
1) Start MSAccess 2010
By default in MSAccess 2010, the table wizard always creates a primary key on the first autonumbered field. Accept this default.
2) Add a text field or two.
3) Add a DATE/TIME field
4) Save.

Now start up sbase :
1) In Database wizard, choose "connect to existing db"
2) Select Access 2007 in the list of choices in the dropdown menu.
3) Accept defaults proposed by wizard then save the ODB.
4) Open the ODB file pointing to the MSAccess db (.accdb).
5) Double-click on a table in the list of tables.
6) Note that the data is not writable, but read only. Also note that new records can not be added to the table.

Expected : should be able to add new records, and update/modify existing data.


Note that this does work for MSAccess 2003 files (read/write).


Alex
Comment 1 Julien Nabet 2011-12-03 15:16:05 UTC
Hi Alex ! 
On this link http://msdn.microsoft.com/en-us/office/cc907897, it seems Access 2007 may have some difficulties to manage Access 2010 file. So it could be quite normal LO doesn't manage Access 2010 files. Now it could be enhancement to propose (if it doesn't already exist)

Have you tried read/write operations on Access 2007 file with LO ?
(I haven't any version of Access to test, could you attach a Access 2010 and 2007 file ?)
Comment 2 Alex Thurgood 2012-01-09 06:29:04 UTC Comment hidden (obsolete)
Comment 3 Jochen 2012-08-24 21:42:49 UTC
I have made folowing request on international QA-ML 
for bugreport [1] a tester is required.
The bugreport refers to Base concerning editing Access-files (Access
2007/2010). Who can help? 

Regina Henschel answered:
I had made some tests in Sept.2007
http://wiki.openoffice.org/wiki/Connecting_to_Microsoft_Access
There I found, that ADO gives only read access to the table, you need to setup an ODBC connection.

I don't know, whether "Access 2007" uses ADO and whether it is intended to give write access to the tables. A quick test with LO 3.6 using type "Access 2007" results in "missing SDBC driver". So the database could not be opened at all. A connection with ODBC works in LO 3.6 and the tables are writeable.
Comment 4 Lionel Elie Mamane 2012-08-27 07:12:12 UTC
(In reply to comment #3)

> Regina Henschel answered:
> I had made some tests in Sept.2007
> http://wiki.openoffice.org/wiki/Connecting_to_Microsoft_Access
> There I found, that ADO gives only read access to the table, you need to setup
> an ODBC connection.

If that's still true, it needs to be fixed.

> I don't know, whether "Access 2007" uses ADO

ADO and ODBC are two APIs to use the Microsoft driver to access .accdb files (or .mdb or a few other formats).

> and whether it is intended to give write access to the tables.

AFAIK, it is.


> A quick test with LO 3.6 using type "Access 2007"
> results in "missing SDBC driver". So the database could not be opened at all.

That was bug 52615, which is now fixed.
Comment 5 Jochen 2012-08-27 07:23:54 UTC
Hi Regina,

(In reply to comment #4)
> > Regina Henschel answered:
> > I had made some tests in Sept.2007
> > http://wiki.openoffice.org/wiki/Connecting_to_Microsoft_Access
> > There I found, that ADO gives only read access to the table, you need to setup
> > an ODBC connection.
> If that's still true, it needs to be fixed.

Please: can you test again with the newest LO-version 3.6.0/3.6.1 RC2
Comment 6 Regina Henschel 2012-08-27 20:16:07 UTC
I've got LibO 3.6.1.2 now. I still cannot access the database via the driver 'Microsoft Access 2007'. It has no longer the error "missing SDBC driver", but the wizard hangs and do not react. I have to kill the process.

I can open and work with the same database using an ODBC-connection.

I use file NORDWIND.accdb, the German version of the well known "NORTHWIND" database.
Comment 7 Jochen 2012-08-27 20:44:26 UTC
Status changed to "NEW". Reason: IMHO currently no open questions.
Comment 8 Timur 2012-08-30 09:41:53 UTC
(In reply to comment #6)
> I've got LibO 3.6.1.2 now. I still cannot access the database via the driver
> 'Microsoft Access 2007'. It has no longer the error "missing SDBC driver", but
> the wizard hangs and do not react. I have to kill the process.
> 
> I can open and work with the same database using an ODBC-connection.
> 
> I use file NORDWIND.accdb, the German version of the well known "NORTHWIND"
> database.

The same problem via the driver 'Microsoft Access 2007 with LibO 3.6.1.2 and MS Access 2010.
ODBC works both ways, database can be edited.
Comment 9 Lionel Elie Mamane 2012-11-05 13:29:06 UTC
This may or may not have been fixed by


commit acc0535133c571642a9a1e3025255f34873f1699
Author: Lionel Elie Mamane <lionel@mamane.lu>
Date:   Mon Nov 5 14:06:12 2012 +0100

    ADO getRSConcurr(): translate ADO LockTypeEnum into our css::sdbc::RSConcurr
    
    That code was there since the beginning, but unreachable.
    Consequent cleanup removed it.
    
    Change-Id: I2564038ce58d7aff3860f154acac37266c155146


Could someone please retest with a master daily build from *after* today? Thanks.

http://dev-builds.libreoffice.org/daily/Win-x86@6/master/
http://dev-builds.libreoffice.org/daily/W2008R2@16-minimal_build/master/
Comment 10 Timur 2012-11-08 13:49:26 UTC
Using "Microsoft Access 2007" driver, we can access the database now, but we cannot edit it.
Comment 11 Timur 2013-05-28 09:53:33 UTC
I change Status back from NEEDINFO to NEW since according to my previous comment and new test with LO 4.0.3, the situation is the same: the database cannot be edited.
Comment 12 Lionel Elie Mamane 2013-05-28 15:15:24 UTC
This is specific to the "Microsoft.ACE.OLEDB.12.0" driver; other drivers (such as Microsoft.Jet.OLEDB.4.0) work fine read/write.
Comment 13 Alex Thurgood 2015-01-03 17:39:58 UTC Comment hidden (no-value)
Comment 14 Timur 2015-05-11 13:26:15 UTC
(In reply to Lionel Elie Mamane from comment #12)
> This is specific to the "Microsoft.ACE.OLEDB.12.0" driver; other drivers
> (such as Microsoft.Jet.OLEDB.4.0) work fine read/write.
Microsoft.Jet.OLEDB.4.0 works only for .mdb, not for .accdb. 
Microsoft.ACE.OLEDB.12.0 and Microsoft.ACE.OLEDB.15.0 only open, without editing.
But, simple opening using "Microsoft Access 2007" driver still doesn't permit editing, so this bug still remains. 

(In reply to Alex Thurgood from comment #13)
> Adding self to CC if not already on
Alex, you are the bug submitter. Please test and report.
Comment 15 Alex Thurgood 2015-05-11 14:35:31 UTC Comment hidden (obsolete)
Comment 16 Alex Thurgood 2016-06-03 07:50:51 UTC
*** Bug 91189 has been marked as a duplicate of this bug. ***
Comment 17 Alex Thurgood 2016-06-03 08:32:15 UTC
So, managed to get hold of a Windows 10 PC, with MSOffice 2010 32bit. I have installed the MDAC.

Connecting to and opening a accdb 2007 file works, but is read only. The main ODB database window shows the connection as being "Microsoft Access 2007". 

Tested on LibreOffice 5.1.2.2

I can also connect to and open the same accdb file via ODBC, the difference being that the data is read/write. I can add new data sets to the table via this route. 

Confirming comment 8
Comment 18 Alex Thurgood 2016-06-03 09:45:02 UTC
(In reply to Alex Thurgood from comment #17)
> So, managed to get hold of a Windows 10 PC, with MSOffice 2010 32bit. I have
> installed the MDAC.
> 

I installed the 32bit MDAC 2.8 SP1, for use with LibreOffice 32bit.

If you want to use LO 64bit and try to install the 64 bit Data Access components for Win10, the installation will fail if you only have MSOffice 32bit.

The result of this is that LO64 bit versions do not appear able to either open or read/write to Access MDB/ACCDB files, unless you also have a 64bit version of MSOffice.
Comment 19 Alex Thurgood 2017-10-20 14:16:43 UTC
Closing this report (I opened it) as this is now works for me with an all-64bit setup :

64bit OS : Win10
64bit Data Access Engine (2016)
64bit LO
64bit Access accdb (Northwind) Access >= 2010
Comment 20 Alex Thurgood 2017-10-20 14:42:48 UTC
Sigh, wrote too nsoon, that'll teach me. Although I can open tables from accdb 2010 using the database access engine 2016, I can't actually write any data into the tables...

Re-opening, and updating title
Comment 21 Zineta 2018-03-19 11:46:21 UTC
Not editable MS Access table in:
LO 5.3.7.2; LO both mdb and accdb files via MS Access and MS Access 2007 drivers
respectively.