SetOption.MaxLocksPerFile =8000 help config registry settings

hey List,

I'm trying to use the dbengine.setoption "dbMaxLocksperFile",8000 to adjust the registry settings at runtime in acces 2000, however I cant get the code to work. Do I have to add the "dbMaxLocksPerFile" and "Threads" to the application properties? I get a variety of errors from "variable not defiend" to command not optional?? Ahhhhh

Phill
Phillip_KnappAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Phill,

It should look like:

Sub SetOption()

  DBEngine.SetOption dbMaxLocksPerFile, 8000
 
End Sub
0
Phillip_KnappAuthor Commented:
Hey JDettman,
thanks for the responce. I've tried the code below in a "on click" event procedure and I still get a "variable not defiend" error refering to the dbMaxLocksPerFile variable.

Private Sub Command10_Click()
DBEngine.setoption dbMaxLocksPerfFile, 8000
End Sub

I've also noticed that when I move to the next line of code the "SetOption" changes the cap's to "setoptions". (removing the capital S and O. Can I run this code from an "on click" procedure, or do I have to use it in a module only?

Phill
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Phillip,

  Should work fine in any module.  Your problem is most likly that you don't have a reference to DAO.  Open a module in design view, click tools/references.  Make sure your using a DAO lib (for A2000, it should be DAO 3.6).

JimD.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Phillip_KnappAuthor Commented:
with Jdettman's help got this fixed, and moving foward in the application developement again!! thanks JD!
Phill
0
hartjo1Commented:
It's great to knwo that if I have a question that it's been asekd, more inportantly, answered before!   THough this thread is nearly 4 years old, it looks as if the good Mr. Dettman is still active, so here it goes...

I used the above solution tor esolve a similar problem, btu have a question regarding the affects of increasing the MaxLocks parameter has.  My current db is about 150,000 records, so I assume that I would set dbMaxLocksPerfFile at around the total number of records.  But as I need for my application to be scalable, I actually enterd a value of 1000000 for dbMaxLocksPerfFile.  Granted, it's overkill, but I figure better safe than sorry.  The question is,  does this slow down processing?  If so, is there any way to set the dbMaxLocksPerfFile value as a dynamic funciton, like setting the actual value of dbMaxLocksPerfFile to correspond to a variable which, in turn, is set equal to the number of records in the db?  Just a though!


-JDH
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The question is,  does this slow down processing?>>

  The answer is yes and no.  Certainly placing a 100,000 locks on a MDB will be slow, but if you mean setting for 100,000 locks and if you don't always use them, will that slow things down? No.  I think your mis-understanding the use of the setting (see below)

<<  If so, is there any way to set the dbMaxLocksPerfFile value as a dynamic funciton, like setting the actual value of dbMaxLocksPerfFile to correspond to a variable which, in turn, is set equal to the number of records in the db? >>

  Sure, but your missing the point of the setting.  The idea is that JET will issue no more then this number of locks.  If it hits that, it will breakup whatever it's doing into multiple transactions internally.  So there are two things to consider:

1. Do you do use transactions?  If so, then you certainly want the setting high enough so that JET will always complete in a single transaction (if it's not, you might find that you really can't roll back).  If not, then it really doesn't matter what it does internally (well for the most part anyway).

2. Does the system support that number of locks?  For example, under Novell, there are absolute limits on the number of locks that a single user can place.  You can't set MaxLocksPerFile higher then this no matter what.  Otherwise Novell will complain and Access will lock up.  This BTW is why the setting was created in the first place; to make sure that Access/JET doesn't consume more locks then what the server will allow.

Hope that makes it a bit clearer

 
0
hartjo1Commented:
Sure does.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.