How do I turn off File Sharing Locks altogether?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Increasing MaxLocksPerFile to 2,000,000 didn't work...

I am using Assess 2003 and have a major update to over 1,000,000 records
that has to occur for 45 time for 45 accounting quarters... If I try going
file to file then rename the result, the database bloats and I run out of
room. So I wrote a simple match and update routine in VBA but it keeps
blowing on "Run-time error 3052" File sharing lock exceeded.
 
Have you tried from the Access Menu Tools->Options and on the Advanced tab
under default record locking selecting No Locks ?
 
Well, you sure had me scrambling to see if I had pulled a bonehead error...
But "No Locks" is selected...
 
Wayne said:
Increasing MaxLocksPerFile to 2,000,000 didn't work...

I am using Assess 2003 and have a major update to over 1,000,000
records that has to occur for 45 time for 45 accounting quarters...
If I try going file to file then rename the result, the database
bloats and I run out of room. So I wrote a simple match and update
routine in VBA but it keeps blowing on "Run-time error 3052" File
sharing lock exceeded.
Click to expand...

Would you care to post your code? Maybe something will suggest itself.
 
Increasing MaxLocksPerFile to 2,000,000 didn't work...

I am using Assess 2003 and have a major update to over 1,000,000 records
that has to occur for 45 time for 45 accounting quarters... If I try going
file to file then rename the result, the database bloats and I run out of
room. So I wrote a simple match and update routine in VBA but it keeps
blowing on "Run-time error 3052" File sharing lock exceeded.
Click to expand...

Hi Wayne,

I answered you in the other thread but I'll go ahead and post my solution here,
as well.

I ended up closing and re-opening the recordset to clear the MaxLocksPerFile
cache.

In the error handler:
If Err.Number = 3052 Then 'Trap for MaxLocksPerFile error
Set rs = Nothing 'Close recordset
Resume WorkAround 'Clear error and redirect to re-open recordset
End If

Where "WorkAround" is a label just before you set the recordset. There should
be logic in your code so it either picks up where it stopped or ignores the
records already altered.

HTH,
RD
 
You may get fewer locks if you use Open Exclusive.
I am altering over 800,000 records and each record
that gets altered generates a file lock and each file
lock takes up 2 bytes.
Click to expand...

(david)
 
Back
Top