[AccessD] Basic Questions about Access Locking

Brad Marks BradM at blackforestltd.com
Thu Aug 25 13:35:47 CDT 2011


Darryl and William,

Thanks for the help/insights.

Since I first ran into this problem earlier this week, I have learned
how to change the Registry entry for a permanent change.  I also learned
how to programmatically make a temporary change to "MaxLocksPerFile"
when using ADO.

Either of these approaches will satisfy the need that we currently have.

I still would like to understand MaxLocksPerFile better.

As mentioned, the default is 9500.  Bumping it up to 500,000 is a large
increase.  I am not sure what this affects, I would guess that more
memory would be used.

Also, it appears that there is no way to avoid Locks when doing updates
with a RecordSet in VBA.  I can live with this, as long as I understand
that there are no alternatives.

I appreciate the idea of doing the updates in "Chunks".  I won't need to
use this approach for the current situation, but I will keep this
approach the back of my mind for possible future situations.       

Thanks again for your assistance.

Brad

PS. If someone would like to explain in more detail how Access locking
works, I am all ears  :-)



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl
Collins
Sent: Wednesday, August 24, 2011 6:02 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Basic Questions about Access Locking

Hahaha, me neither. I got this error yesterday when I had a table of
200K+
rows that I wanted to add a primary key to (using autonumber - ok, not
trying to restart the 'what is a real key' debate here - autonumber
would do
the task for what I needed just fine).

I could only add 9500 in a single go, kinda pointless against 200,000+
entries.

Anyway.  Tweaking the registry key to increase the locks did the trick,
although I would question why the hell it is so darn low to start with?
Again, I could probably Google this for more blurb, but soon as I fixed
the
issue I lost considerable interest in further poking around - I was in a
hurry yesterday.

Cheers
Darryl.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
Benson
(VBACreations.Com)
Sent: Thursday, 25 August 2011 5:40 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Basic Questions about Access Locking

Just a WAG ... but if there is a primary key can't you perform queries
in
chunks, when the recordset is sorted on the primary key? Ie, find the
Nth
record in a recordset, do something to records 1 to N ... get the
primary
key value associated with record N, then requery the source where the
primary key > that of record N?

Yep, I don't really know what I am talking about ...

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Wednesday, August 24, 2011 3:20 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Basic Questions about Access Locking

We have an Access 2007 application that pulls data from SQL Server and
creates reports.  

Because of the complexity of some of the reports and because of the
complexity of some of the underlying data, we push all of the necessary
data into an Access table. 

In one of the later steps, we need to update every record in this table
via a Record Set.

Recently, we ran into a problem when we exceeded the File Sharing Lock
Count.

We have now figured out how to programmatically increase the
"MaxLocksPerFile".

This works fine, but for the long run, I would like to better understand
"Access Locking".

Let's say that I have an Access table with 50,000 rows and I want to
update each row with VBA code using a Record Set.  

It is my limited understanding that this will create 50,000 "Locks".

Is there a way to avoid or minimize these locks? 

The table in question is NOT "multi-user".  It does NOT reside in a
"back end" that is shared.   

Thanks for your help with this question.

Brad

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.





More information about the AccessD mailing list