Hi all. The code I run below runs perfectly in cases where I have a small amount of records. However, in cases were I have several thousands of records, the code only partially runs before giving me the error message of: - "File sharing lock exceeded. Increase MaxLocksPerFile registry entry."
- "You have exceed the maximum number of locks allowed on a recordset. This limit is specified by the MaxLocksPerFile setting in your system registry. The default value is 9500, and can be changed either by editing the registry or with the SetOption method."
For my case, the "SetOption method" sound like the way I want to go since I do not want to mess with my system registry. Does anyone know how to impliment this in my code below? Or maybe there's some simple refinements that need to be made to the code itself. I'm guess ADezii will probably land a solution. =)
As always, I so appreciate any suggestions. I attached a database with two modules that allow you to see the code working on a small set of records (modGo) and then failing on a large set of records (modNoGo). - Option Compare Database
-
Option Explicit
-
-
Public Function UpdateToMaster()
-
-
Dim updaterec As Boolean
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim rs2 As DAO.Recordset
-
-
Dim Ctr As Integer
-
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset("SELECT " & _
-
"PioCode, MdlYear, SRSER2, SRFMLY, SRDr, SRTRIM, SRTRAN, VMACCE, VMINT, VMEXT, SR, CM, fldupdated " & _
-
"FROM tblNoGo", dbOpenDynaset)
-
Set rs2 = db.OpenRecordset("SELECT " & _
-
"fldPIO, fldMdlYr, fldSeries, fldFamily, fldDr, fldTrim, fldTrans, fldAccGrp, fldIntClr, fldExtClr, fldSR, fldCM " & _
-
"FROM tblPioMaster", dbOpenDynaset)
-
-
rs.MoveFirst
-
rs2.MoveFirst
-
-
With rs2
-
-
Do Until .EOF
-
Do Until rs.EOF
-
-
If rs!fldupdated = 0 Then
-
-
updaterec = True
-
-
For Ctr = 0 To 11 'Update this when adding additional criteria (e.g. 0-11 = 12 fields from MasterTable)
-
If Not IsNull(.Fields(Ctr)) Then
-
If .Fields(Ctr) <> rs.Fields(Ctr) Then
-
updaterec = False
-
Exit For
-
End If
-
End If
-
Next Ctr
-
-
If updaterec = True Then
-
rs.Edit
-
rs!PioCode = IIf(IsNull(!fldPIO), Null, !fldPIO)
-
rs!MdlYear = IIf(IsNull(!fldMdlYr), Null, !fldMdlYr)
-
rs!SRSER2 = IIf(IsNull(!fldSeries), Null, !fldSeries)
-
rs!SRFMLY = IIf(IsNull(!fldFamily), Null, !fldFamily)
-
rs!SRDr = IIf(IsNull(!fldDr), Null, !fldDr)
-
rs!SRTRIM = IIf(IsNull(!fldTrim), Null, !fldTrim)
-
rs!SRTRAN = IIf(IsNull(!fldTrans), Null, !fldTrans)
-
rs!VMACCE = IIf(IsNull(!fldAccGrp), Null, !fldAccGrp)
-
rs!VMINT = IIf(IsNull(!fldIntClr), Null, !fldIntClr)
-
rs!VMEXT = IIf(IsNull(!fldExtClr), Null, !fldExtClr)
-
rs!SR = IIf(IsNull(!fldSR), Null, !fldSR)
-
rs!CM = IIf(IsNull(!fldCM), Null, !fldCM)
-
-
rs!fldupdated = -1
-
rs.Update
-
End If
-
-
End If
-
rs.MoveNext
-
-
Loop
-
.MoveNext
-
rs.MoveFirst
-
Loop
-
-
End With
-
-
rs.Close
-
rs2.Close
-
-
Set db = Nothing
-
Set rs = Nothing
-
Set rs2 = Nothing
-
MsgBox "Worked!"
-
-
End Function
The good news is that the solution to your problem lies below, the bad news is that it only applies to the Current Session. Execute the following code ' prior' to executing your Function. Be sure to set the Reference to the DAO Object Library as indicated in the Comment. The Default Number of Locks Per File as defined by Jet is 9,500, the code will ' temporarily' increase this Value to 15000. This should be enough for your process to complete successfully. Let us know how you make out. - 'Must first set a Reference to the Microsoft DAO X.X Object Library
-
DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
EXAMPLE - DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
-
-
Call UpdateToMaster
P.S. - The SetOption that you are referring to, and Referenced in the code, is the SetOption Method of the DBEngine Object NOT the Access Application Object.
8 24497 NeoPa 32,556
Expert Mod 16PB
There are a few issues to deal with here : - I could find no reference to MaxLocksPerFile in the Help system. I specifically looked in SetOption.
- SetOption seems to be a facility provided to change the options found under the Tools menu. I found nothing in there either related to this.
- If this value were to be changed using this interface, then I expect the change made would change the registry. It would simply be doing it via a different interface.
- Last, but certainly not least, if you have some code that needs this to be extended then there is a very good chance that the code should be looked at. The resources used for each lock are not trivial. If you have code that processes through recordsets without tidying itself up properly, then this is code that you want to change or otherwise lose. I hesitate to say throw away and start again, but I would give careful consideration to maintaining minimum locks during any piece of the code.
I don't suppose this is what you wanted to hear, but I'm afraid I'd be doing you no service if I'd left it unsaid.
The good news is that the solution to your problem lies below, the bad news is that it only applies to the Current Session. Execute the following code ' prior' to executing your Function. Be sure to set the Reference to the DAO Object Library as indicated in the Comment. The Default Number of Locks Per File as defined by Jet is 9,500, the code will ' temporarily' increase this Value to 15000. This should be enough for your process to complete successfully. Let us know how you make out. - 'Must first set a Reference to the Microsoft DAO X.X Object Library
-
DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
EXAMPLE - DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
-
-
Call UpdateToMaster
P.S. - The SetOption that you are referring to, and Referenced in the code, is the SetOption Method of the DBEngine Object NOT the Access Application Object.
NeoPa 32,556
Expert Mod 16PB
On the other hand, if that's your code, then I don't see too many multi-level updates, and the .Edit is always matched by the .Update. It seems that Jet must be doing some kind of transaction management. It (Help) tells me that it doesn't, yet I can see no alternative. Clearly if it's maintaining the lock info it must be maintaing locks even after the .Update has been run.
If that is the case, I would consider handling the error and doing a Close and Reopen of your recordsets before continuing. You could simply increase the value of MaxLocksPerFile, but there will potentially be situations where even an increased one overflows. Closing and reopening does introduce a certain overhead, but not too onerous in such simple cases (I would consider opening the recordsets as Tables mind-you). Your code would need to handle continuing from where it left off though. This is not 100% straightforward when the tables have been closed between records.
NeoPa 32,556
Expert Mod 16PB @ADezii
Nice ADezii.
I suspect this is far more what the OP was after, and doesn't effect the registry (which is ideal for their purposes).
With that tip I was able to find the reference in Help for the method.
I'm curious to understand better why the code posted should have this problem though. It's actually pretty tidy as far as clearing down any resources goes. Perhaps one of the Access options specifies whether or not to treat such code as a massive transaction or not. Any ideas gratefully welcomed (BTW I see this as specifically relevant to this thread hence not reposting separately).
Your answer is clearly the best one for this thread's original question.
This is a strange one NeoPa. This Error usually occurs when 1 or more Users are involved in Multiple Transactions within a Multi-User Environment. To make things stranger, I worked on the Database in work without setting the MaxLocksPerFile to 15000, and it worked flawlessly with the Default Setting of 9500. Same Access Version, comparative CPUs and Memory, etc. The only 'major' difference is that I'm networked in work, while at home I work on a Stand-a-Lone PC. Any ideas?
Thanks so much guys for responding and offering up insight into the dilemma I'm facing. ADezii, once again you knocked it out of the park! This line of code you offered was the key to overcoming the error message I was getting. - DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
I also see what you are saying about running it in a separate session. However, in order for it to work, I had to run it inside of my UpdateToMaster() function. On the downside, I'm unable to call anything else within the UpdateToMaster(), but if I create a seperate module, kind of like you suggested, it will allow me to run as many functions or routines as I need. =)
eg.
Module1 -
Public Function RunCodeProcesses()
-
Call UpdateToMaster
-
Call WorkedMsgBox
-
End Function
-
Module2 - Option Compare Database
-
Option Explicit
-
-
Public Function UpdateToMaster()
-
DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
-
-
Dim updaterec As Boolean
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim rs2 As DAO.Recordset
-
-
Dim Ctr As Integer
-
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset("SELECT " & _
-
"PioCode, MdlYear, SRSER2, SRFMLY, SRDOOR, SRTRIM, SRTRAN, VMACCE, VMINT, VMEXT, Sunroof, ConvMirror, fldupdated " & _
-
"FROM rawPIOapplications", dbOpenDynaset)
-
Set rs2 = db.OpenRecordset("SELECT " & _
-
"fldPIO, fldMdlYr, fldSeries, fldFamily, fldDoor, fldTrim, fldTrans, fldAccGrp, fldIntColor, fldExtColor, fldSunroof, fldConvMirror " & _
-
"FROM tblPioMaster", dbOpenDynaset)
-
-
rs.MoveFirst
-
rs2.MoveFirst
-
-
With rs2
-
-
Do Until .EOF
-
Do Until rs.EOF
-
-
If rs!fldupdated = 0 Then
-
-
updaterec = True
-
-
For Ctr = 0 To 11 'Update this when adding additional criteria (e.g. 0-11 = 12 fields from MasterTable)
-
If Not IsNull(.Fields(Ctr)) Then
-
If .Fields(Ctr) <> rs.Fields(Ctr) Then
-
updaterec = False
-
Exit For
-
End If
-
End If
-
Next Ctr
-
-
If updaterec = True Then
-
rs.Edit
-
rs!PioCode = IIf(IsNull(!fldPIO), Null, !fldPIO)
-
rs!MdlYear = IIf(IsNull(!fldMdlYr), Null, !fldMdlYr)
-
rs!SRSER2 = IIf(IsNull(!fldSeries), Null, !fldSeries)
-
rs!SRFMLY = IIf(IsNull(!fldFamily), Null, !fldFamily)
-
rs!SRDOOR = IIf(IsNull(!fldDoor), Null, !fldDoor)
-
rs!SRTRIM = IIf(IsNull(!fldTrim), Null, !fldTrim)
-
rs!SRTRAN = IIf(IsNull(!fldTrans), Null, !fldTrans)
-
rs!VMACCE = IIf(IsNull(!fldAccGrp), Null, !fldAccGrp)
-
rs!VMINT = IIf(IsNull(!fldIntColor), Null, !fldIntColor)
-
rs!VMEXT = IIf(IsNull(!fldExtColor), Null, !fldExtColor)
-
rs!Sunroof = IIf(IsNull(!fldSunroof), Null, !fldSunroof)
-
rs!ConvMirror = IIf(IsNull(!fldConvMirror), Null, !fldConvMirror)
-
-
rs!fldupdated = -1
-
rs.Update
-
End If
-
-
End If
-
rs.MoveNext
-
-
Loop
-
.MoveNext
-
rs.MoveFirst
-
Loop
-
-
End With
-
-
rs.Close
-
rs2.Close
-
-
Set db = Nothing
-
Set rs = Nothing
-
Set rs2 = Nothing
-
-
-
End Function
Module3 - Public Sub WorkedMsgBox()
-
MsgBox "Worked! Thanks ADezii!!!!", vbExclamation, "Yay!!!"
-
End Sub
ADezii, I read your profile and I see that you were a Fire Fighter. Are you sure you didn't work for NASA, too??? Anyhow, I thank you for your service and I thank you for your solution. ;-)
I think that I can also speak for NeoPa in that we were more than happy to assist you in this matter. Stop by again, should the need arise!
NeoPa 32,556
Expert Mod 16PB @ADezii
'Fraid not my friend. I'm a bit green when it comes to multi-user environments to be fair. I can sometimes think into it and come up with solutions, but it's not something I get into very often.
PS. Of course you speak for me. I was happy to provide what little insight I could on this subject.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bob Parnes |
last post by:
In its default configuration, my version of pylint (0.5.0) sets the
maximum number of class attributes at 7. This seems low to me, but I
can see how an excessive number might make maintenance more...
|
by: Stephen |
last post by:
Can anyone tell me the maximum number of services you can (or are allowed to)
create under the Windows 2000 operatingsystem?
Is this the same for all versions, e.g. AS, EE and DC?
Thanks,
...
|
by: Roger Withnell |
last post by:
How do I find the maximum value of a recordset column? I'd rather do it
this way than open a new recordset with Max(column).
Thanks in anticipation.
Posted Via Usenet.com Premium Usenet...
|
by: bughunter |
last post by:
Description
The maximum number of locks held during this transaction.
It's not true. :-( When I tried found longest transactions I see
next picture:
uow_start_time T1, uow_stop T2,...
|
by: Dominic |
last post by:
I'd like to tune the performance of my application in a web garden.
Our server has dual processors. Is there any guideline to set this
"maximum number of worker processes" for web garden? In my...
|
by: Michael.Suarez |
last post by:
In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:
Maximum number of databases used for each query has been exceeded. The...
|
by: Paul Sijben |
last post by:
I have a server in Python 2.5 that generates a lot of threads. It is
running on a linux server (Fedora Core 6).
The server quickly runs out of threads.
I am seeing the following error.
File...
|
by: linda.chen |
last post by:
Hi all,
We have a webservice (service1), which calls another
webservice(service2) from another orginization. Our end users make
requests throught service1. When service2 receives a quest, it...
|
by: Satheesh V |
last post by:
hi,
I use DAO recordset to query database tables of MSAccess. I open the recordset and miss/forget to close it. Can anyone tell me the maximum number of recordsets that can be open at a time (on...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |