Darryl Collins
darryl at whittleconsulting.com.au
Wed Aug 24 18:01:59 CDT 2011
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