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
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).
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 ?)
Sorry, I don't have Access anymore, it was on a trial period on a machine I was lent, and I don't have that machine now.
I have made folowing request on international QA-ML
for bugreport  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
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.
(In reply to comment #3)
> Regina Henschel answered:
> I had made some tests in Sept.2007
> 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.
(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
I've got LibO 22.214.171.124 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.
Status changed to "NEW". Reason: IMHO currently no open questions.
(In reply to comment #6)
> I've got LibO 126.96.36.199 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"
The same problem via the driver 'Microsoft Access 2007 with LibO 188.8.131.52 and MS Access 2010.
ODBC works both ways, database can be edited.
This may or may not have been fixed by
Author: Lionel Elie Mamane <firstname.lastname@example.org>
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.
Could someone please retest with a master daily build from *after* today? Thanks.
Using "Microsoft Access 2007" driver, we can access the database now, but we cannot edit it.
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.
This is specific to the "Microsoft.ACE.OLEDB.12.0" driver; other drivers (such as Microsoft.Jet.OLEDB.4.0) work fine read/write.
Adding self to CC if not already on
(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.
(In reply to Timur from comment #14)
> (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.
I can no longer test this, see comment 2.
*** Bug 91189 has been marked as a duplicate of this bug. ***
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 184.108.40.206
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
(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.
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 Access accdb (Northwind) Access >= 2010
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