Apache OpenOffice (AOO) Bugzilla – Issue 29896
OpenOffice.org ignores MS Access record locking
Last modified: 2013-02-07 22:39:52 UTC
Hi, To me this is issue is more than a show stopper. It is a fundamental reason to stop using OpenOffice.org completely. I hope there is simple solution, or otherwise the last two yeas of effort assisting the project for me has been largely a waste of time. I develop MS Access applications. An MS Access database is multi-user by default. More fundamental is a single user can access the data via two different applications on their own machine and know the data integrity is looked after. Thus multi-user does not have to mean more than one person, but could mean more than one process. In OO I can connect to the MS Access database using ODBC (and ADO but I don't generally use this so I can't vouch much in this area). Two users (or the same user via two different processes) can then update and change the contents of the MS Access database. The problem is MS Access enforces record level locking, but OpenOffice.org does not. Attached is the Biblio table which has been converted to MS Access database. Also attached is a Form which can be used to update the data from OpenOffice.org. Using MS Access start changing the first record by changing the Type from 1 to 5. (Don't save, stay in the record.) Now using the Form in OO try to make changes and Save the record. Because MS Access has locked the record the change will be rejected as would be expected. Now reverse the process. In OO use the form to start changing the Type field value but don't save. Using MS Access change the Type field and save. The record is not locked and thus the second user can save. Now save the record in OO and the record is save overwriting the first users change. You also do not need MS Access to test this. Simply have two OO computer access the same data. I am an experienced computer professional and it took me almost two years to find out OpenOffice.org is flawed in this area and that is even after I thought I had done sufficient testing. Users will not find out so quickly. This now means there is not a single option available to Windows users that I have been able to find, that does not require Client/Server database software for two or more users or processes accessing the same data. As of right now I have to abandon all work towards moving to OpenOffice.org and treat ALL data sources as read only or single user. I truly hope there is an easy option such as a parameter, macro or registry change which will allow this to work. To put millions of potential MS Access users into the situation where they can no longer have trust in their multi-user applications is a critical flaw. If I appear over the top I apologise. I am simply shocked with this finding. Thanks for your time. Regards Kelvin
Created attachment 15692 [details] MS Access database with biblio table
Created attachment 15693 [details] Form for use with MS Access database
Hi, this is never Priority 1, so I change it to P3. Can you please test this with ADO , because Access ODBC is end of life from MS. ADO is the up-to-date connection method for Access. Bye Marc
Hi, You are right based I on the description of priorities. However I do believe that overwriting data without warning would constitute data loss, and therefore this would indicate the issue is a priority 2. From my point of view the priority doesn't matter. It means OpenOffice.org in this area, at this point in time, no longer has the potential to meet my needs, or the needs of my clients. Each person or organisation has to make their own decision. I have tested this now using shared data on a peer to peer Windows network and a Linux server, using ADO to access the data. The same problem exists with ADO. I think it is now up to a second person to test and confirm the findings. Regards Kelvin
*** Issue 29896 has been confirmed by votes. ***
mine
CLU->kelvine: i can repro and acknowledge the behavior you describe, but record locking is not specificated in oo office and has never worked that way in oo - that means: it is not a bug, but a feature enhancement
correct setting: os
CLU->BH: works with M$Office (Access), so it would be nice if it would work for our oo, too (definetly a nice to have) - on the other hand it is a rather rare used feature and this issue is the first time i have heard from outside about this problem
Hi, If I can make some extra comments. - Firstly the level of usage of database compared to the rest of an office suite I can roughly guess at around 7%. No real figures, just from reading over the years. Perhaps check the user surveys. This means the likelihood of people using the area is smaller and so issue doesn't get noticed as easily. - I assumed that record locking worked and my initial testing showed it worked. Unfortunately I tested only half the record locking and not from both directions. - In eighteen months I have not yet been able to use OpenOffice.org due to limitations in handling MS Access line feeds and memo fields. Anyone else would have found the same thing and stopped using the product without reporting it. - If I am correct there are two settings required for ADO. One at the database level and one at the table level. This is from some reading. No special skills in this area. For the database level I believe the following may work. PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=\\server\public\test\noaccess.mdb;Jet OLEDB:Database Locking Mode=1 However for the table level there is no way that I can enable the additional parameter which I believe is the following. Jet OLEDB:Locking Granularity = 2 I somehow believe it may be possible using Basic to create a data source with these settings but I don't have that knowledge. Alternately is there a way using Basic to set this additional setting such as when a form opens. If this is possible then I could get around the problem in the short term. It would be even better if this was incorporated into the user interface. - Currently there is nothing between single user file access and a client/server database and this IMHO is a very big hole in OpenOffice.org. For Windows users (unfortunately it is not crossplatform) providing MS Access multi-user read/write access is one way to get around this hole. - I did not anticipate record locking to be an issue and so was working towards version 2.0 to start incorporating what I have learned over the last eighteen months. - ODBC and ADO extends areas far beyond what most people think they can do with the OpenOffice.org query writer. This is an exciting way to add significant functionality with very little effort. I have already written a MiniBook for my clients which shows around 100 functions which are available using ODBC and MS Access. The ability to Group, Sort, Count and much more. If should be remembered that Office suites are generally for end users. Thinking end users will set up client/server software for a simple telephone list which can be updated by a couple of staff at the same time, is something that is just not going to happen. It is simply cheaper and more efficient to buy MS Access. Hopefully I have passed on some of my enthusiasm. Record locking is pretty fundamental for small groups of users sharing files, or even a single user using multiple applications with the same data. It would be a pity if this was not part of the next release. I liked the comment about being a "rather rare used feature". Sometimes the best ideas come out of left field (in reality I've been working towards this for eighteen months). I don't know of one other way that could so quickly provide a multi-user file sharing database feature for OpenOffice.org Windows users with a relatively small amount of work. Do you? The real irony is that Microsoft have already provided the database file format. The project simply has to use it. Thanks for listening. Kelvin
To grep the issues easier via "requirements" I put the issues currently lying on my owner to the owner "requirements".