Darryl Collins
darryl at whittleconsulting.com.au
Wed Aug 24 17:57:30 CDT 2011
Ha, I had this exact problem yesterday, I permanently upped the max lock value from the default 9500 to 500000. Seemed to fix the problem without any performance hit. Maybe I just got lucky as I don't really understand the nuts and bolts of it all and did bugger all research to check the impact of such a change. Just found out how to tweak the reg key and up the value. You can also do it just for a single session as well, but I thought a more, ummm, permanent solution, maybe the go. For once the MS page detailing the options was accurate, useful and got a good result - IMH experience that is not always a given. <<http://support.microsoft.com/kb/815281>> Cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks Sent: Thursday, 25 August 2011 5:20 AM 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