Phillip_Knapp
asked on
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
with Jdettman's help got this fixed, and moving foward in the application developement again!! thanks JD!
Phill
Phill
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
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
<<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
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
Sure does. Thanks!
It should look like:
Sub SetOption()
DBEngine.SetOption dbMaxLocksPerFile, 8000
End Sub