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

09-23-2007

Hi Sascha,

That's certainly an option. If you don't want to be bothered with always
bumping up against this limit, then make the change permanently by editing
the registry. I've bumped up against the 9500 limit often enough that I
simply got tired of having to deal with it each time.

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

"Sascha Trowitzsch" wrote:

Quote:

> 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

09-24-2007

Hi Tom,

"Tom Wickerath" <AOS168b AT comcast DOT net> schrieb im Newsbeitrag
news:80D34B19-BE46-4CC7-8C84-3C5ED536B53D@xxxxxx

Quote:

> Hi Sascha,
>
> That's certainly an option. If you don't want to be bothered with always
> bumping up against this limit, then make the change permanently by editing
> the registry. I've bumped up against the 9500 limit often enough that I
> simply got tired of having to deal with it each time.

Yo, maybe YOU are allowed to do that. ;-)
But another point of view: Leaving maxlocksperfile on its default value helps
finding weak coding.
One of the most frequent appearances of error messages dealing with this setting
raises when records of a recordset are modified or written in loops. It possibly
would not raise if the value for maxlocksperfile is set to 200000. But the error
is a kind of warning that large areas of the backend are locked which is a
serious issue on performance degradation in multi user environments. So one
should think about that loop and integrate a DBEngine.Idle in it or recode in a
manner that fewer locks are set. If this is not possible due to any
circumstances that, ok, would be a reason to increase the settings value.
So I'd not recommand in general to increase the value at least during
development.

Ciao, Sascha

10-01-2007

I'm trying to piggyback on this for a single user 2007 access database. I
have 2 tables that I need to link. The one table does not have a primary key
or unique field so I want to add an autonumber, however there are 6.8 million
rows. I tried upping the value to 500000 but I still get the message to up
the number. Any idea what I might need to set the value to in order to work
with such a large # rows (and if that would be ok to do)?

"Tom Wickerath" wrote:

Quote:

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

09-23-2007	#11 (permalink)
Tom Wickerath n/a posts	Re: can't find HKey...soft\Jet in Vista registry; re MaxLocksPerFi Hi Sascha, That's certainly an option. If you don't want to be bothered with always bumping up against this limit, then make the change permanently by editing the registry. I've bumped up against the 9500 limit often enough that I simply got tired of having to deal with it each time. Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/ex...tributors.html __________________________________________ "Sascha Trowitzsch" wrote: Quote: > 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
My System Specs

09-24-2007	#12 (permalink)
Sascha Trowitzsch n/a posts	Re: can't find HKey...soft\Jet in Vista registry; re MaxLocksPerFi Hi Tom, "Tom Wickerath" <AOS168b AT comcast DOT net> schrieb im Newsbeitrag news:80D34B19-BE46-4CC7-8C84-3C5ED536B53D@xxxxxx Quote: > Hi Sascha, > > That's certainly an option. If you don't want to be bothered with always > bumping up against this limit, then make the change permanently by editing > the registry. I've bumped up against the 9500 limit often enough that I > simply got tired of having to deal with it each time. Yo, maybe YOU are allowed to do that. ;-) But another point of view: Leaving maxlocksperfile on its default value helps finding weak coding. One of the most frequent appearances of error messages dealing with this setting raises when records of a recordset are modified or written in loops. It possibly would not raise if the value for maxlocksperfile is set to 200000. But the error is a kind of warning that large areas of the backend are locked which is a serious issue on performance degradation in multi user environments. So one should think about that loop and integrate a DBEngine.Idle in it or recode in a manner that fewer locks are set. If this is not possible due to any circumstances that, ok, would be a reason to increase the settings value. So I'd not recommand in general to increase the value at least during development. Ciao, Sascha
My System Specs

10-01-2007	#13 (permalink)
Laury n/a posts	Re: can't find HKey...soft\Jet in Vista registry; re MaxLocksPerFi I'm trying to piggyback on this for a single user 2007 access database. I have 2 tables that I need to link. The one table does not have a primary key or unique field so I want to add an autonumber, however there are 6.8 million rows. I tried upping the value to 500000 but I still get the message to up the number. Any idea what I might need to set the value to in order to work with such a large # rows (and if that would be ok to do)? "Tom Wickerath" wrote: Quote: > 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 > > > >
My System Specs

Similar Threads
Thread	Forum
vista ultimate 32bit HKEY problems	Vista security
Registry Editor - Find and Find Next	Tutorials
no more auto run can't find the right registry	Vista hardware & devices
Easily find registry locations in Windows Vista	Software
Vista Installation Can't Find Registry, Install Fails	Vista General