By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Access 97 FileShare Lock Count exceeded

P: 9
I am getting the above message when trying to update 2 fields in a table with 41,000 records. I am using visual Basic to update the records as the IIF statement was too complex for a query. Here is the function I am using.

Any suggestions on how I can get Access to work. I can't touch the registry.
Expand|Select|Wrap|Line Numbers
  1. Dim rsMealMaster As Recordset
  2. Dim Wkcount As Double
  3. Dim restCount As Double
  4. Dim bReturn As Boolean
  5.  
  6. Dim db As Database
  7.  
  8. Dim sSQL As String
  9. Set db = CurrentDb  ' Will need this from now on.
  10. bReturn = True
  11.  
  12.   sSQL = "SELECT ForecastMealsMaster.Flight_Date, ForecastMealsMaster.Customer, ForecastMealsMaster.Flight, "
  13.   sSQL = sSQL & " ForecastMealsMaster.Class, ForecastMealsMaster.Aircraft_Type, ForecastMealsMaster.Origin,  "
  14.   sSQL = sSQL & " ForecastMealsMaster.Destination, ForecastMealsMaster.Forecast_Pax, ForecastMealsMaster.Actual_Pax, "
  15.   sSQL = sSQL & " ForecastMealsMaster.FMeals, ForecastMealsMaster.Fpax_ShortTerm, ForecastMealsMaster.Fpax_LongTerm, "
  16.   sSQL = sSQL & " ForecastMealsMaster.LastWkAvgPax, ForecastMealsMaster.LyPax, ForecastMealsMaster.LastWkPax, "
  17.   sSQL = sSQL & " ForecastMealsMaster.Last2WkPax,ForecastMealsMaster.FConfig,ForecastMealsMaster.ActualConfig "
  18.   sSQL = sSQL & " FROM ForecastMealsMaster"
  19.   sSQL = sSQL & " WHERE ForecastMealsMaster.Flight_Date >= " & StartDate
  20. '  sSQL = sSQL & " AND ForecastMealsMaster.Flight_Date <= " & EndDate
  21.  
  22.   Set rsMealMaster = db.OpenRecordset(sSQL)
  23.   Do Until rsMealMaster.EOF
  24. '   loop through each record
  25.     rsMealMaster.Edit
  26.     If rsMealMaster("LastWkPax") = 0 Then Wkcount = 1
  27.     If rsMealMaster("Last2WkPax") = 0 Then Wkcount = 1
  28.     restCount = 3
  29.     If rsMealMaster("LastWkAvgPax") = 0 Then restCount = restCount - 1
  30.     If rsMealMaster("LyPax") = 0 Then restCount = restCount - 1
  31.     rsMealMaster("Fpax_ShortTerm") = CInt((rsMealMaster("LastWkAvgPax") + rsMealMaster("LyPax") _
  32.                                      + (rsMealMaster("LastWkPax") + rsMealMaster("Last2WkPax")) / Wkcount) / restCount)
  33.     If rsMealMaster("Fpax_ShortTerm") > rsMealMaster("FConfig") And rsMealMaster("FConfig") <> 0 Then
  34.         rsMealMaster("Fpax_ShortTerm") = rsMealMaster("FConfig")
  35.     End If
  36.     rsMealMaster.Update
  37.     rsMealMaster.MoveNext
  38.  
  39.   Loop
  40.   ForecastShortTermPax = bReturn
  41.   DoEvents
  42. End Function
thanks
Jan 5 '07 #1
Share this Question
Share on Google+
7 Replies


Expert 5K+
P: 8,435
Personally, I prefer to leave out the table name from all of the field names, just to shorten the code. But that's a personal preference and many here would probably tell you not to even consider it. :)

I think you need to check out how to set the MaxLocksPerFile parameter. this is in the registry, so from what you said, you may not be able to do it. There miight be some way to set it from within your code. I'll leave this to those more expert in Access. They know who they are... ;) In the meantime, try searching TheScripts for "MaxLocksPerFile".

Oh, one other thing! Could you give us an idea of what you were trying to do with the Iif()? Perhaps we can boil it down to a simpler form and avoid the code altogether.
Jan 5 '07 #2

P: 9
The eqation I am trying to use is a simple forecasting formula. Once this works I hope to make it as complex as is needed.

There are 4 fields
A - last weeks average pax (calculated separately in a similar function which works)
B - Last years pax on the same day eg monday
C - Last week's pax on the same day eg monday
D - Last fortnight's pax on the same day eg monday.

The formula is
CInt((A+B+(C+D)/2)/3)
ie average the last 2 weeks pax and then take the average of the 3 numbers
However the complication is if A,B,C,D are zero I need to ignore that number and average over the number of non zero fields
eg if A =0, B=12, C=0, D=10
the formula becomes CInt(0+12+(0+10)/1)/2) ie there is only 1 weeks data and only 2 non zero fields.
Jan 5 '07 #3

Expert 5K+
P: 8,435
Thanks for that. It does look as though you need to do it as a function, huh.

I was just thinking, couldn't you just begin a transaction, then commit it after every n updates? I do this fairly often, and maybe it will overcome your too-many-locks problem. The general idea is...
Expand|Select|Wrap|Line Numbers
  1. BeginTrans
  2. Do Until rs.Eof
  3.   ' Do your stuff here. Then...
  4.  
  5.   UpdateCount = UpdateCount + 1
  6.   If UpdateCount >= UpdateLimit Then
  7.     CommitTrans
  8.     BeginTrans
  9.     UpdateCount = 0
  10.   End If
  11.   rs.MoveNext
  12. Loop
  13. CommitTrans
  14.  
UpdateLimit, in this case, would most likely be a constant you have set somewhere (I just hate hard-coding this sort of value).
Jan 5 '07 #4

P: 9
Thanks for that. It does look as though you need to do it as a function, huh.

I was just thinking, couldn't you just begin a transaction, then commit it after every n updates? I do this fairly often, and maybe it will overcome your too-many-locks problem. The general idea is...
Expand|Select|Wrap|Line Numbers
  1. BeginTrans
  2. Do Until rs.Eof
  3.   ' Do your stuff here. Then...
  4.  
  5.   UpdateCount = UpdateCount + 1
  6.   If UpdateCount >= UpdateLimit Then
  7.     CommitTrans
  8.     BeginTrans
  9.     UpdateCount = 0
  10.   End If
  11.   rs.MoveNext
  12. Loop
  13. CommitTrans
  14.  
UpdateLimit, in this case, would most likely be a constant you have set somewhere (I just hate hard-coding this sort of value).

Thanks very much This works. Will definately use this in future.
Jan 5 '07 #5

Expert 5K+
P: 8,435
Thanks very much This works. Will definately use this in future.
Glad to hear it's sorted.

I suppose this is the sort of general solution that we should list somewhere for future reference.
Jan 5 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
That's probably not worth a whole article, but it could well feature in a FAQ.
A good solution to a fundamental problem.
Jan 6 '07 #7

Expert 5K+
P: 8,435
That's probably not worth a whole article, but it could well feature in a FAQ.
A good solution to a fundamental problem.
Thanks.

As you said, it wouldn't warrant an article of its own. But should be worth an entry in a Tips & Tricks list somewhere.

I can't take full credit, though. It's a fairly standard technique we use on the mainframe.
Jan 6 '07 #8

Post your reply

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