Vista - can't find HKey...soft\Jet in Vista registry; re MaxLocksPerFile

Is there a regedit way to change the file sharing lock count in Vista? The MS
article 815281 must be giving directions for XP. I don't see
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0, or even
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet, in my machines registry. I'm
nervous about using VBA to do this.

TYIA

Marian

Hi Marian,

I just checked a new laptop PC that I have, which has the Vista Ultimate
operating system. This PC also has Office 2007 Ultimate installed on it. This
key is present in the location shown in KB
http://support.microsoft.com/?id=815281.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0

I'm not sure why your machine seems to be missing it. I would just go ahead
and add it. Here is a copy of what this branch looks like, when I exported it
from my registry (I changed the MaxLocksPerFile setting first, from the
default value of 9500 to 200000):
==================================
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0]
"PageTimeout"=dword:00001388
"LockRetry"=dword:00000014
"MaxBufferSize"=dword:00000000
"Threads"=dword:00000003
"ExclusiveAsyncDelay"=dword:000007d0
"SharedAsyncDelay"=dword:00000032
"FlushTransactionTimeout"=dword:000001f4
"MaxLocksPerFile"=dword:00030d40
"LockDelay"=dword:00000064
"RecycleLVs"=dword:00000000
"PagesLockedToTableLock"=dword:00000000
"UserCommitSync"="yes"
"ImplicitCommitSync"="no"
==================================

You can open NotePad, copy the above and paste into a new text file. Save
the file with the .reg file extension. Then double-click it.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"Marian M." wrote:

Would the fact that this is a 64-bit machine make a difference?

"Tom Wickerath" wrote:

That's a distinct possibility.

Tell you what....I also have a new server PC that has the 64 bit version of
Vista installed, but it is disconnected at the moment. I'll fire it up later
on today and have a look. It will be approx. 6 hours or so before I post back
with an answer.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"Marian M." wrote:

"Marian M." <MarianM@xxxxxx> wrote...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

Hi Andrew,
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.
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.
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.
Did you notice the original cross-post to M.P.A?
She is, indeed.
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:

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:BA4EE1C2-9B85-41D3-AF97-05A9CCC1CE22@xxxxxx
Hi Tom,

No worries, I'll happily defer to your wisdom in this matter! I'm not an
Access expert, by any stretch :-) If you say increase MaxLocksPerFile,
that's good enough for me.

I still think many users increment it for the *wrong* reasons. I have
certainly had people come to me in the past with performance problems, after
they tried bumping up this value and it had no effect on their performance.
But it seems like increasing MaxLocksPerFile doesn't absorb too many system
resources, so the trade-offs, whatever they are, are not severe.

I never look at microsoft.public.access ... as I say, I'm not an Access
expert :-) Although when pressed, I can speak SQL Server, DB2, and Oracle,
with the best of them :-))

Best regards,
--
Andrew McLaren
amclar (at) optusnet dot com dot au

Hi Andrew,
Agreed. Bumping this setting up would do nothing for performance. You can
point these folks to my Multiuser Applications paper in the future. It
includes lots of useful information even for single-user Access applications,
whether split or not:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/mu...lications.html
I have not seen any adverse effects. I made this change several years ago,
on my Windows 2000 based PC, a PC that is over 6 years old; it has 512 MB of
RAM memory, considered low by today's standards.

I checked out your MVP profile. What, no pic?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"Andrew McLaren" wrote:

Thank you. I have found Wow6432Node. It turns out that changing
MaxLocksPerFile didn't help any (only bumped it up to 100000), but at least I
now know where the 64-bit stuff is.

I am one person on one lone machine (no servers, no sharing) working with
large datasets (US census data). It's probably the 'large' that is causing
problems. I had set up 'relationships' between tables and then remembered
that some of the tables had no primary key. When I got to the third table
Access pooped out (gave me the file sharing lock count error). When I deleted
all the 'built-in' relationships and set them query by query, I was able to
proceed.

Marian

"Andrew McLaren" wrote:

Hi,

Why should it be necessary to change the maxlocksperfile option in the registry?
You can set it temporarely via code like this:

DBEngine.SetOption dbMaxLocksPerFile, 200000

Ciao, Sascha

"Marian M." <MarianM@xxxxxx> schrieb im Newsbeitrag
news:646F9FA4-48AB-4F7C-A15C-F0C2B680DD84@xxxxxx