473,322 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

VBA to change maximum number of locks allowed on a recordset

23
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:
Expand|Select|Wrap|Line Numbers
  1. "File sharing lock exceeded. Increase MaxLocksPerFile registry entry."
Expand|Select|Wrap|Line Numbers
  1. "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).
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function UpdateToMaster()
  5.  
  6. Dim updaterec As Boolean
  7. Dim db As DAO.Database
  8. Dim rs As DAO.Recordset
  9. Dim rs2 As DAO.Recordset
  10.  
  11. Dim Ctr As Integer
  12.  
  13. Set db = CurrentDb
  14. Set rs = db.OpenRecordset("SELECT " & _
  15.                           "PioCode, MdlYear, SRSER2, SRFMLY, SRDr, SRTRIM, SRTRAN, VMACCE, VMINT, VMEXT, SR, CM, fldupdated " & _
  16.                           "FROM tblNoGo", dbOpenDynaset)
  17. Set rs2 = db.OpenRecordset("SELECT " & _
  18.                            "fldPIO, fldMdlYr, fldSeries, fldFamily, fldDr, fldTrim, fldTrans, fldAccGrp, fldIntClr, fldExtClr, fldSR, fldCM " & _
  19.                            "FROM tblPioMaster", dbOpenDynaset)
  20.  
  21. rs.MoveFirst
  22. rs2.MoveFirst
  23.  
  24. With rs2
  25.  
  26.    Do Until .EOF
  27.       Do Until rs.EOF
  28.  
  29.       If rs!fldupdated = 0 Then
  30.  
  31.          updaterec = True
  32.  
  33.             For Ctr = 0 To 11  'Update this when adding additional criteria (e.g. 0-11 = 12 fields from MasterTable)
  34.                If Not IsNull(.Fields(Ctr)) Then
  35.                   If .Fields(Ctr) <> rs.Fields(Ctr) Then
  36.                      updaterec = False
  37.                         Exit For
  38.                   End If
  39.                End If
  40.             Next Ctr
  41.  
  42.          If updaterec = True Then
  43.             rs.Edit
  44.                rs!PioCode = IIf(IsNull(!fldPIO), Null, !fldPIO)
  45.                rs!MdlYear = IIf(IsNull(!fldMdlYr), Null, !fldMdlYr)
  46.                rs!SRSER2 = IIf(IsNull(!fldSeries), Null, !fldSeries)
  47.                rs!SRFMLY = IIf(IsNull(!fldFamily), Null, !fldFamily)
  48.                rs!SRDr = IIf(IsNull(!fldDr), Null, !fldDr)
  49.                rs!SRTRIM = IIf(IsNull(!fldTrim), Null, !fldTrim)
  50.                rs!SRTRAN = IIf(IsNull(!fldTrans), Null, !fldTrans)
  51.                rs!VMACCE = IIf(IsNull(!fldAccGrp), Null, !fldAccGrp)
  52.                rs!VMINT = IIf(IsNull(!fldIntClr), Null, !fldIntClr)
  53.                rs!VMEXT = IIf(IsNull(!fldExtClr), Null, !fldExtClr)
  54.                rs!SR = IIf(IsNull(!fldSR), Null, !fldSR)
  55.                rs!CM = IIf(IsNull(!fldCM), Null, !fldCM)
  56.  
  57.                rs!fldupdated = -1
  58.             rs.Update
  59.          End If
  60.  
  61.       End If
  62.          rs.MoveNext
  63.  
  64.       Loop
  65.            .MoveNext
  66.          rs.MoveFirst
  67.    Loop
  68.  
  69. End With
  70.  
  71. rs.Close
  72. rs2.Close
  73.  
  74. Set db = Nothing
  75. Set rs = Nothing
  76. Set rs2 = Nothing
  77. MsgBox "Worked!"
  78.  
  79. End Function
Attached Files
File Type: zip TestDb.zip (402.5 KB, 321 views)
Mar 20 '10 #1

✓ answered by ADezii

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.
Expand|Select|Wrap|Line Numbers
  1. 'Must first set a Reference to the Microsoft DAO X.X Object Library
  2. DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
EXAMPLE
Expand|Select|Wrap|Line Numbers
  1. DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
  2.  
  3. 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 :
  1. I could find no reference to MaxLocksPerFile in the Help system. I specifically looked in SetOption.
  2. 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.
  3. 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.
  4. 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.
Mar 20 '10 #2
ADezii
8,834 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. 'Must first set a Reference to the Microsoft DAO X.X Object Library
  2. DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
EXAMPLE
Expand|Select|Wrap|Line Numbers
  1. DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
  2.  
  3. 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.
Mar 20 '10 #3
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.
Mar 20 '10 #4
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.
Mar 20 '10 #5
ADezii
8,834 Expert 8TB
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?
Mar 20 '10 #6
KPR1977
23
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.
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. Public Function RunCodeProcesses()
  2. Call UpdateToMaster
  3. Call WorkedMsgBox
  4. End Function
  5.  

Module2
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function UpdateToMaster()
  5. DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000
  6.  
  7. Dim updaterec As Boolean
  8. Dim db As DAO.Database
  9. Dim rs As DAO.Recordset
  10. Dim rs2 As DAO.Recordset
  11.  
  12. Dim Ctr As Integer
  13.  
  14. Set db = CurrentDb
  15. Set rs = db.OpenRecordset("SELECT " & _
  16.                           "PioCode, MdlYear, SRSER2, SRFMLY, SRDOOR, SRTRIM, SRTRAN, VMACCE, VMINT, VMEXT, Sunroof, ConvMirror, fldupdated " & _
  17.                           "FROM rawPIOapplications", dbOpenDynaset)
  18. Set rs2 = db.OpenRecordset("SELECT " & _
  19.                            "fldPIO, fldMdlYr, fldSeries, fldFamily, fldDoor, fldTrim, fldTrans, fldAccGrp, fldIntColor, fldExtColor, fldSunroof, fldConvMirror " & _
  20.                            "FROM tblPioMaster", dbOpenDynaset)
  21.  
  22. rs.MoveFirst
  23. rs2.MoveFirst
  24.  
  25. With rs2
  26.  
  27.    Do Until .EOF
  28.       Do Until rs.EOF
  29.  
  30.       If rs!fldupdated = 0 Then
  31.  
  32.          updaterec = True
  33.  
  34.             For Ctr = 0 To 11  'Update this when adding additional criteria (e.g. 0-11 = 12 fields from MasterTable)
  35.                If Not IsNull(.Fields(Ctr)) Then
  36.                   If .Fields(Ctr) <> rs.Fields(Ctr) Then
  37.                      updaterec = False
  38.                         Exit For
  39.                   End If
  40.                End If
  41.             Next Ctr
  42.  
  43.          If updaterec = True Then
  44.             rs.Edit
  45.                rs!PioCode = IIf(IsNull(!fldPIO), Null, !fldPIO)
  46.                rs!MdlYear = IIf(IsNull(!fldMdlYr), Null, !fldMdlYr)
  47.                rs!SRSER2 = IIf(IsNull(!fldSeries), Null, !fldSeries)
  48.                rs!SRFMLY = IIf(IsNull(!fldFamily), Null, !fldFamily)
  49.                rs!SRDOOR = IIf(IsNull(!fldDoor), Null, !fldDoor)
  50.                rs!SRTRIM = IIf(IsNull(!fldTrim), Null, !fldTrim)
  51.                rs!SRTRAN = IIf(IsNull(!fldTrans), Null, !fldTrans)
  52.                rs!VMACCE = IIf(IsNull(!fldAccGrp), Null, !fldAccGrp)
  53.                rs!VMINT = IIf(IsNull(!fldIntColor), Null, !fldIntColor)
  54.                rs!VMEXT = IIf(IsNull(!fldExtColor), Null, !fldExtColor)
  55.                rs!Sunroof = IIf(IsNull(!fldSunroof), Null, !fldSunroof)
  56.                rs!ConvMirror = IIf(IsNull(!fldConvMirror), Null, !fldConvMirror)
  57.  
  58.                rs!fldupdated = -1
  59.             rs.Update
  60.          End If
  61.  
  62.       End If
  63.          rs.MoveNext
  64.  
  65.       Loop
  66.            .MoveNext
  67.          rs.MoveFirst
  68.    Loop
  69.  
  70. End With
  71.  
  72. rs.Close
  73. rs2.Close
  74.  
  75. Set db = Nothing
  76. Set rs = Nothing
  77. Set rs2 = Nothing
  78.  
  79.  
  80. End Function
Module3
Expand|Select|Wrap|Line Numbers
  1. Public Sub WorkedMsgBox()
  2. MsgBox "Worked!  Thanks ADezii!!!!", vbExclamation, "Yay!!!"
  3. 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. ;-)
Mar 20 '10 #7
ADezii
8,834 Expert 8TB
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!
Mar 20 '10 #8
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.
Mar 20 '10 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

2
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...
1
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, ...
2
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...
3
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,...
1
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...
2
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...
12
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...
5
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...
2
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...
0
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...
1
isladogs
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...
0
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...
0
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...
0
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...
1
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...
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.