Hi Andrew,
Quote:
> Second, I get worried when I see folks changing this value. Changing it is
> almost never required.
I disagree. I have had to increase this value many times in the past, even
for *single-user* Access applications. A long time ago, I bumped this value
up to 200000 on my PC at work. This has been fine for most situations,
although about 6 months ago, I even encountered a large update query that
failed because this was still not high enough. If the Use Transactions
property of the query is set to Yes, then you can quickly exceed the default
9500 setting. Note: We are not using a Novell NetWare server, so this is not
an issue in my case.
Quote:
> (with 50 concurrent users, that's 190 locks per user).
Incorrect. A single user can cause many thousands of locks, when running an
update query against a large recordset, if the Use Transactions property =
Yes. There is no reliable method of predicting the number of locks required.
Quote:
> ...when the app and the database file are both on the same machine (the
> most common scenario).
While this may be the most common scenario, it is certainly not the case for
the applications that I manage at my place of employment.
Quote:
> Actually, you'll probably get better results asking in an Access group like
> microsoft.public.access...
Did you notice the original cross-post to M.P.A?
Quote:
> Maybe you're running 64 bit Vista?
She is, indeed.
Quote:
> In which case, the key will actually be at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0
> ... because Access is a32-bit application.
I think you have just provided the answer Marian needed.
Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________
"Andrew McLaren" wrote:
Quote:
>
> Hi Marian,
>
> I don't have a complete answer, but I'd make a couple of observations ...
>
> First, the "file sharing lock count" is a function of Access, and/or the Jet
> Database Engine (which is at the heart of Access). It's not an aspect of
> Vista, or an operating system function, as such.
>
> Second, I get worried when I see folks changing this value. Changing it is
> almost never required. But it often implies the user has some kind of
> performance or stability problem which they are trying to solve, and they've
> discovered this setting as a possible solution. So my questions are:
> - do you have an Access database file sitting on a Novell Netware file
> server? and
> - do you have more than, say, 50 users concurrently accessing that
> database? Not just using the database, but all reading and updating the mdb
> file, at the very same moment?
>
> If the answer to either is "no", it is unlikely you need to change this
> value. The default MaxLocksPerFile is 9500 - which is high enough for most
> scenarios, outside very heavy-duty, shared database applications (with 50
> concurrent users, that's 190 locks per user).
>
> Possibly you're having some kind of performance or stability problem with
> Access, or a Jet-based application? If so, the problem is probably not
> related to MaxLocksPerFile. If the mdb file is sitting on a Vista machine,
> and is being accessed from other Vista machines, you'll want to be aware of
> this known issue:
> http://support.microsoft.com/?kbid=935370
> There's currently no solution. Note that Access generally runs fine, when
> the app and the database file are both on the same machine (the most common
> scenario). The 935370 issue only arises when the database file is shared
> across the network.
>
> If you're trying to adjust this value because you're following the
> instructions in a README or similar doco then, those instructions are
> probably wrong, or out of date. If you're having a problem running Access,
> you'd better describe the "real" problem, so we can suggest the right
> solution (since MaxLocksPerFile probably isn't the right answer). Actually,
> you'll probably get better results asking in an Access group like
> microsoft.public.access, since the Access experts will have more experience
> and ideas, even if the underlying cause is actually a Vista issue.
>
> (If you have analysed a performance problem in your database application and
> determined that you are, in fact, seeing transactions aborted due to
> insufficient locks, then ... my apologies, for being a patronising git! :-)
>
> As to why you don't see that value in your registry ... er, I dunno. I can
> see it on my machine, which has Access 2007 installed. Maybe you're running
> 64 bit Vista? In which case, the key will actually be at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0
> ... because Access is a32-bit application.
>
> Hope this helps!
> --
> Andrew McLaren
> amclar (at) optusnet dot com dot au
>
>