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
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 ?)
Hi Julien, 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. Alex
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.
(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.
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
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.
Status changed to "NEW". Reason: IMHO currently no open questions.
(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.
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/
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 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
(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 LO 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
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.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
I no longer have a setup that allows me to test this. Either someone with the required Windows setup should test, or else this can be closed as RESOLVED INSUFFICIENTDATA
Dear Alex Thurgood, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Repro 7.4+ with 64-bit LO and 64-bit Access Database Engine 2016.
Launching below macro displays False which means there's *privilege stuff* keeping us from editing table. Sub Main dim a,b as object set a = ThisComponent.CurrentController.ActiveConnection set b= ThisComponent.DataSource set c=ThisComponent.CurrentController print (a.IsreadOnly or b.IsreadOnly) End Sub https://github.com/LibreOffice/core/blob/5168d06b1302c43a305d0f670ee65079f21063b5/connectivity/source/drivers/ado/ADatabaseMetaData.cxx#L303-L354 Reference< XResultSet > SAL_CALL ODatabaseMetaData::getTablePrivileges( const Any& catalog, const OUString& schemaPattern, const OUString& tableNamePattern ) { Reference< XResultSet > xRef; HERE -->> if(!ADOS::isJetEngine(m_pConnection->getEngineType())) https://github.com/LibreOffice/core/blob/a2c3ef6d8108355ce5daf6ff72310ac93ae745f0/connectivity/source/drivers/ado/adoimp.cxx#L192 As you can see in git blame. "Access 2007 Database" is not considered as Jet Engine in 2001...... which is false. This issue would be fixed if you add new jet engine type released after 2001.. Thank you for keeping this issue..
Below is a script to patch the affected libreoffice windows amd64 binary. With the patched binary, I can now edit accdb database in libreoffice base. (f='./LibreOffice/program/adolo.dll';export LC_CTYPE=C.iso8859-1;printf '\xeb\x25'|dd of="$f" conv=notrunc,nocreat oflag=seek_bytes seek=$(LC_CTYPE=C.iso8859-1 grep --only-matching --text --byte-offset -P '\x32\xd2\x83' "$f"|grep --text -oP '.*?(?=:)') count=2)