Solved

Access error - Runtime error 3052 - File Sharing Lock exceeded

Posted on 2004-10-28
1,426 Views
Last Modified: 2009-12-16
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
Question by:Adlerm
    6 Comments
     
    LVL 5

    Accepted Solution

    by:
    0
     

    Author Comment

    by:Adlerm
    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
     
    LVL 9

    Expert Comment

    by:ornicar
    nblick's comment will work. I use A97 with this problem and the registry value change solves it.
    0
     

    Author Comment

    by:Adlerm
    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
     
    LVL 9

    Expert Comment

    by:ornicar
    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
     

    Author Comment

    by:Adlerm
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    The Complete Ruby on Rails Developer Course

    Ruby on Rails is one of the most popular web development frameworks, and a useful tool used by both startups and more established companies to build strong graphic user interfaces, and responsive websites and apps.

    In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now