Issue 29896 - ignores MS Access record locking
Summary: ignores MS Access record locking
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 1.1.1
Hardware: All All
: P3 Trivial with 9 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2004-06-05 03:36 UTC by kelvine
Modified: 2013-02-07 22:39 UTC (History)
2 users (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---

MS Access database with biblio table (408.00 KB, application/octet-stream)
2004-06-05 03:39 UTC, kelvine
no flags Details
Form for use with MS Access database (9.39 KB, application/vnd.sun.xml.writer)
2004-06-05 03:42 UTC, kelvine
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description kelvine 2004-06-05 03:36:54 UTC

To me this is issue is more than a show stopper. It is a fundamental reason to 
stop using 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 
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

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 

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 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 
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.


Comment 1 kelvine 2004-06-05 03:39:54 UTC
Created attachment 15692 [details]
MS Access database with biblio table
Comment 2 kelvine 2004-06-05 03:42:27 UTC
Created attachment 15693 [details]
Form for use with MS Access database
Comment 3 marc.neumann 2004-06-07 08:38:41 UTC

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
Comment 4 kelvine 2004-06-07 10:57:37 UTC

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 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.


Comment 5 kelvine 2004-06-07 11:00:27 UTC
*** Issue 29896 has been confirmed by votes. ***
Comment 6 christoph.lukasiak 2004-06-07 14:46:50 UTC
Comment 7 christoph.lukasiak 2004-06-07 14:56:22 UTC
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
Comment 8 christoph.lukasiak 2004-06-07 15:26:35 UTC
correct setting: os
Comment 9 christoph.lukasiak 2004-06-07 15:27:20 UTC
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
Comment 10 kelvine 2004-06-07 16:14:18 UTC

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 

- In eighteen months I have not yet been able to use 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.

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

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 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 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.

Comment 11 bettina.haberer 2010-05-21 14:51:09 UTC
To grep the issues easier via "requirements" I put the issues currently lying on
my owner to the owner "requirements".