How do I turn off File Sharing Locks altogether?

[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.

 

[Guest]

Have you tried from the Access Menu Tools->Options and on the Advanced tab
under default record locking selecting No Locks ?

 

[Guest]

Well, you sure had me scrambling to see if I had pulled a bonehead error...
But "No Locks" is selected...

 

[Dirk Goldgar]

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.

Would you care to post your code? Maybe something will suggest itself.

 

[RD]

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.

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

 

[david epsom dot com dot au]

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.

(david)