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  
AdlermAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.