A way around this error? (max lock count exceeded)
?err.Number
-2147217887
?err.Description
File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.
here is the code I'm running, it's just going through and stripping out arrows, something like 100,000 records or so.
Private Sub mnuStripArrows_Click()
Dim db As String
Dim cn As New ADODB.Connection
Dim RS As ADODB.Recordset
Dim sSQL As String
Dim i As Integer
Dim change As Boolean
db = txtDB.text
CheckConnection
DBConnect db, cn
sSQL = "SELECT [field1],[field2] FROM Data"
Set RS = New ADODB.Recordset
With RS
.ActiveConnection = cn
.Source = sSQL
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.Open
.MoveFirst
Do While Not .EOF
change = False
For i = 0 To 1 Step 1
If InStr(1, .Fields(i).Value, "->") Or InStr(1, .Fields(i).Value, "<-") Then
.Fields(i).Value = Replace(.Fields(i).Value, "->", "-")
.Fields(i).Value = Replace(.Fields(i).Value, "<-", "-")
change = True
End If
Next i
If change = True Then
.Update
change = False
' End If
.MoveNext
Loop
End With
MsgBox "Done"
End Sub
why would it give me this error?
first error comes up when i=2, so it doesnt even get to go through many times.
[1646 byte] By [
chupacabra] at [2007-11-11 10:02:09]
# 1 Re: A way around this error? (max lock count exceeded)
well, I just closed it out, and ran it again, and this time it worked.
what would cuase that error, though?
# 2 Re: A way around this error? (max lock count exceeded)
There is an MS KB article that covers this error:
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b815281&Product=acc2000
# 3 Re: A way around this error? (max lock count exceeded)
On the other hand, you might as well use the power of Access to filter the records out with these 'arrows' on forehand (where clause in select statement), unless all records have this arrow of course. In that case even better: make an sql update statement to let Access handle it completely (no loops etc.)
So OK, no problem anymore, but remember that database engines are much more optimized than most of our own vb methods/runtimes etc. Do not overestimate but certainly do not underestimate either the power of Access.
# 4 Re: A way around this error? (max lock count exceeded)
On the other hand, you might as well use the power of Access to filter the records out with these 'arrows' on forehand (where clause in select statement), unless all records have this arrow of course. In that case even better: make an sql update statement to let Access handle it completely (no loops etc.)
So OK, no problem anymore, but remember that database engines are much more optimized than most of our own vb methods/runtimes etc. Do not overestimate but certainly do not underestimate either the power of Access.
how can I do that with SQL?
I have to do a few more things like this (in a text field, have the characters "-_" leading that needs to be stripped out, across x number of records.
i could do the vb update loop, but is there a way to do it w/ sql that would be more efficient?
# 5 Re: A way around this error? (max lock count exceeded)
Some suggested code changes. Replace() can be tempermental when used directly against a field object, so I suggest using a string variable instead. Also this way you only reference the field value once to read it and only once more if it needs updating. Like This: Dim RS As ADODB.Recordset
Dim sSQL As String
Dim i As Integer
Dim change As Boolean
Dim buf As String
db = txtDB.text
CheckConnection
DBConnect db, cn
sSQL = "SELECT [field1],[field2] FROM Data"
Set RS = New ADODB.Recordset
With RS
.ActiveConnection = cn
.Source = sSQL
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.Open
.MoveFirst
Do While Not .EOF
change = False
For i = 0 To 1
buf = .Fields(i).Value
If InStr(buf, "->") Or InStr(buf, "<-") Then
buf = Replace(buf, "->", "-")
buf = Replace(buf, "<-", "-")
.Fields(i).Value = buf
change = True
End If
Next i
If change = True Then .Update
.MoveNext
Loop
End With
MsgBox "Done"
End Sub
# 6 Re: A way around this error? (max lock count exceeded)
MS Jet 4.0 supports a lot of VBA in its SQL parser.
For instance to narrow your recordset to only the records that have this arrow:
SELECT *
FROM table
WHERE fieldx LIKE '%->%' or fieldx LIKE '%<-%'
(% is the * wildcard in ADO)
To do it completely in MS Access use the following:
UPDATE table
SET fieldx = Replace(fieldx, '->', '')
WHERE fieldx LIKE '%->%'
(and the same for the other arrow)
Instead of opening a recordset, you must execute this as a command:
adoConn.Execute strSQL (in which strSQL contains the statement above).
I have heard about this bug that MS Access 2003 might not work with 'replace', but in Acc2k it worked. If so, then you might invent a somewhat more complex statement with InStr, Left, Mid, Right functions (see MS Access Help for more details).
Good luck!
Guido
