• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1453
  • Last Modified:

Access error - Runtime error 3052 - File Sharing Lock exceeded

Hi
 
   I'm trying to run the following code in Access 97 and Access 2000. When I run in Access 2000 everything works without a problem but when I run the same code in Access 97 using the same data I get -  Runtime error 3052 - File sharing lock exceeded (error 3052). When I go into debug it takes me to the function listed below and more specific to the line in the function indicated below.

 I've tried moving the BeginTrans/CommitTrans statements within the Do Loop but all I seem to do  is to create an other problem as the system then does not recognize the  - rsPublicHolidays.Close - statement.

The file Im trying to to process has over 172,000 records.

Has anyone have any ideas why the function works in Access 2000 but falls over in Access 97 AND where is the correct place to put the BeginTrans/CommitTrans so that I can overcome the File Sharing Lock Exceeded problem and for thesystem to recognize the rsPublicHolidays.Close statement.

     ******************************************************************

Public Function msfCheckDateForPublicHoliday(inpDate As Date, inpState As Integer) As Boolean

Dim db                              As Database
Dim rsPublicHolidays                As Recordset
Dim Message                         As String
Dim Newline                         As String
Dim MsgBoxTitle                     As String
Dim iPublicHolidayState             As Integer
Dim dPublicHolidayDate              As Date


Const MB_Info = 64                   ' OK/Info/First Button Default
Const MB_Warning = 48             ' OK/Warning/First Button is the default
                     
Set db = DBEngine(0)(0)
Set rsPublicHolidays = db.OpenRecordset("qry0050_sel_PublicHolidays", dbOpenDynaset) <====== Debug (L O O K)

BeginTrans
   
   If Not rsPublicHolidays.BOF And Not rsPublicHolidays.EOF Then
      rsPublicHolidays.MoveFirst
   Else
      MsgBoxTitle = "Public Holiday Error"
      Message = "Public Holiday table was not found or has zero entries " & Newline & Newline
      Message = Message & "Please check availability of Public Holidays table " & Newline & Newline
      MsgBox Message, MB_Info, MsgBoxTitle
      GoTo ExitProcess
   End If
   
   Do Until rsPublicHolidays.EOF
      iPublicHolidayState = rsPublicHolidays!PublicHolidayState
      If inpState <> iPublicHolidayState Then
          GoTo ProcessNextPHRecord
       End If
     
      dPublicHolidayDate = rsPublicHolidays!PublicHolidayDate
      If inpDate <> dPublicHolidayDate Then
         GoTo ProcessNextPHRecord
      Else
         msfCheckDateForPublicHoliday = True
         GoTo ExitProcess
      End If

ProcessNextPHRecord:
      rsPublicHolidays.MoveNext
     
           
   Loop

msfCheckDateForPublicHoliday = 0                          ' indicates date is not a public holiday

ExitProcess:

CommitTrans

rsPublicHolidays.Close

End Function

  ***********************************************************************

Many thanks in advance

Cheers  
              Michael  
0
Adlerm
Asked:
Adlerm
  • 3
  • 2
1 Solution
 
AdlermAuthor Commented:
Hi niblich

     I already have this artical. Unfortunately it relates to Access 2000 and above - Not Access 97. If my memory serves me right MS 97 didn't use registers (it used something similar by I can't remember what) so I unsure how to increase the maximum number of locks in Access 97. Also I thought the BeginTrans/CommitTrans released the locks - thus my question on where to put the BeginTrans/Commit Trans
0
 
ornicarCommented:
nblick's comment will work. I use A97 with this problem and the registry value change solves it.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AdlermAuthor Commented:
Ok I'll try and do what you suggested. I'll get back to you tomorrow as I need to go to work and test your theory. Many thanks for your suggestions and help. Talk to you tomorrow and let you know how I went and to allocate the points
0
 
ornicarCommented:
And also make sure to change the value everywhere, as A97 may use 3.x Jet instead of 4.0, depending on the librairies used. So check as well:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5
and
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 3.x

You can set the value as high as you like, because on a non-Novell network there is no reason to set a limit to the locks per file. Mine is decimal 200000 for instance.
0
 
AdlermAuthor Commented:
Your suggestions of updating the register Max locks worked just fine. Many thanks.  The suggestion of updating A97 registers for Jet 3.5/4.0 etc was also very helpful so I hope you can share in some of the spoils (points).
   
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now