• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1072
  • Last Modified:

File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

Hi,

I am updating the sortordering of over 10,000 records in a recordset.  
I get the following error...

Microsoft JET Database Engine error '80040e21'
File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

I have viewed an article from Microsoft on the problem, but it involves changing registry values to change the MaxLocksPerFile, something which my hosting provider is unwilling to do.

Is there anyway I can split the recordset to update in chunks to stop over 10,000 connections? Or will anything to do with the cursor type help?
.
Set adoCon = Server.CreateObject("ADODB.Connection")
 
adoCon.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("../Data/database.mdb"))
SQL3= "SELECT parttable.shortdesc, parttable.longdesc, parttable.Category, vehiclepart.vehicleCategory, vehiclepart.Partsort FROM parttable LEFT JOIN vehiclepart ON parttable.Partno = vehiclepart.partno WHERE parttable.Category = '" & vcategory & "' and vehiclepart.vehicleCategory = '" & vcategory & "';"
 
Set recordset3 = Server.CreateObject("ADODB.Recordset")
recordset3.Open SQL3, adoCon, 3, 3
 
response.write(SQL3)
 
If Not recordset3.eof then
	do while not recordset3.eof
	
	shortcode = recordset3.fields("shortdesc")
	
	recordset3("Partsort") = cLng(Request.Form("link"& shortcode &""))
 
	recordset3.Update
		
	recordset3.MoveNext
		
	Loop 
 
	End If
	Set Recordset3 = nothing

Open in new window

0
emzi19
Asked:
emzi19
  • 2
1 Solution
 
sybeCommented:
Remark 1.
Why do you pull lots of fields from the database with which you do nothing?

Remark 2
Why don't you use a direct SQL statement?

'Remark 1.
SQL3= "SELECT parttable.shortdesc, vehiclepart.Partsort FROM parttable LEFT JOIN vehiclepart ON parttable.Partno = vehiclepart.partno WHERE parttable.Category = '" & vcategory & "' and vehiclepart.vehicleCategory = '" & vcategory & "';"
 
'Remark 2
'something like
sSQL = "UPDATE vehiclepart SET Partsort = " & Request.Form("link") & (SELECT shortdesc FROM parttable WHERE parttable.partno = vehiclepart.partno) WHERE parttable.Category = '" & vcategory & "' and vehiclepart.vehicleCategory = '" & vcategory & "';"
Connexction.Execute(sSQL)

Open in new window

0
 
sybeCommented:
Oh, and consider migrating to a real database. 10,000 records in MS Access is maybe not the best solution.
0
 
emzi19Author Commented:
Hi, thanks for the comments sybe. I have worked around this to update the database in chunks seperated by ID number - e.g. 0-1000 1000-2000 etc.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now