MaxLocksPerFile Error with Workdays Function

[sandykj3]

Hello,

Thank you in advance for your assistance.

Issue MS Access:
I have been using Workdays Function I found in Microsoft help with no problems, but I started getting the error "Error 3052: File sharing lock count exceeded. Increase MaxLocksPerFile registry entry" with a header of "Workdays" at the top. I had previously fixed this with the following:

Private Sub Form_Load()
DAO.DBEngine.SetOption dbMaxLocksPerFile, 3000000
End Sub

Now it has started again when I am running a query using Workdays([Date1],[Date2]).

Troubleshooting (none of which have worked):
I made sure that the Microsoft Office 14.0 Objects Library was selected.
This database is 257,312 KB, therefore I split it
I did a Compact & Repair


I opened a blank database and imported in the information

P.S. I do not know SQL; everything I am using is either copied or I had help with.

[June7]

Post the code or the link where you found it.

[sandykj3]

Here is where I found the code below: http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String

' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

nWeekdays = Weekdays(startDate, endDate)
If nWeekdays = -1 Then
Workdays = -1
GoTo Workdays_Exit
End If

strWhere = "[Holiday] >= #" & startDate _
& "# AND [Holiday] <= #" & endDate & "#"

' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _
Criteria:=strWhere)

Workdays = nWeekdays - nHolidays

Workdays_Exit:
Exit Function

Workdays_Error:
Workdays = -1
MsgBox "Error " & err.Number & ": " & err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit

End Function

Public Function Weekdays(ByRef startDate As Date, _
ByRef endDate As Date _
) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Sunday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error

' The number of weekend days per week.
Const ncNumberOfWeekendDays As Integer = 2

' The number of days inclusive.
Dim varDays As Variant

' The number of weekend days.
Dim varWeekendDays As Variant

' Temporary storage for datetime.
Dim dtmX As Date

' If the end date is earlier, swap the dates.
If endDate < startDate Then
dtmX = startDate
startDate = endDate
endDate = dtmX
End If

' Calculate the number of days inclusive (+ 1 is to add back startDate).
varDays = DateDiff(Interval:="d", _
date1:=startDate, _
date2:=endDate) + 1

' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=startDate, _
date2:=endDate) _
* ncNumberOfWeekendDays) _
+ IIf(DatePart(Interval:="w", _
Date:=startDate) = vbSunday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=endDate) = vbSaturday, 1, 0)

' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
Exit Function

Weekdays_Error:
Weekdays = -1
MsgBox "Error " & err.Number & ": " & err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function

Private Sub Form_Load()
DAO.DBEngine.SetOption dbMaxLocksPerFile, 3000000
End Sub