Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access error - Runtime error 3052 - File Sharing Lock exceeded

Posted on 2004-10-28
6
Medium Priority
?
1,442 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
Comment
Question by:Adlerm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
niblick earned 500 total points
ID: 12433304
0
 

Author Comment

by:Adlerm
ID: 12433570
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
ID: 12433963
nblick's comment will work. I use A97 with this problem and the registry value change solves it.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:Adlerm
ID: 12434085
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
ID: 12434261
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
ID: 12442648
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

610 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