File Sharing Lock Count Exceeded...

[PSUIVERSON]

...I am building a system to replace some old programs files the converts .txt files into dBASE so that those same files are in ACCESS. There is a large amount of code involved to interpret to format and alter the data. Therefore I've written a great deal of VBA to alter recordsets based on recordset values etc.

I am using ADO and ADOX(tables).

The problem that I run into is that I need this program to be fully automated so that on the click of a macro in ACCESS it executes the code I've written plus queries etc. and builds the tables and database completely. With little effort from the end-user each month. That being the case I CANNOT RETURN ANY GLITCHES, obviously, and I continue to run into this error on the .UPDATE method of recordsets.

FILE SHARING LOCK COUNT EXCEEDED. INCREASE MAXLOCKSPERFILE REGISTRY ENTRY.

When I select debug - it highlights SEE BOLDED ex:

Public Function clearZeros()
Dim rst As ADODB.Recordset
Dim ptot As Single
Dim i As Integer

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection

rst.Open "Select * from PAYMAST", CursorType:=adOpenDynamic, LockType:=adLockOptimistic

rst.MoveFirst

Do While Not rst.EOF
For i = 0 To 14
If rst.Fields(i) = 0 Then
rst.Fields(i) = " "
rst.Update
End If
Next i
For i = 20 To 20
If rst.Fields(i) = 0 Then
rst.Fields(i) = " "
rst.Update
End If
Next i
For i = 12 To 12
If rst.Fields(i) = " " Then
rst.Fields(i) = 0
rst.Update
End If
Next i
rst.MoveNext
Loop

QUESTION:

What is this error message returning? It almost seems like I've executed .UPDATE too many times and the system is full or hasn't been refreshed.

When I click CONTINUE in the VB window I continue on without problem. This cannot be this way as I cannot have my end-users encountering this problem.

ANYONE know what this? I find nothing on GOOGLE and the MSN help on this is cryptic AT best...

...

 

[cmmrfrds]

There are a limited number of connections to the access database. If you open up a connection and don't close it and then destory it, it may cause this problem. It may be worth checking. Also, if you don't close it the recordset may continue to hold a lock.

It may be better to open 1 connection and reuse it for the different recordsets.

Close and Destroy.

rs.close
Set rs = Nothing
cn.close
Set cn = Nothing

 

[PSUIVERSON]

Thanks. I'll make a habit out of that and update my existing code and see how it works. Thanks.

 

[Junior1544]

Another note... I don't think .update is part of ado code... I use it in DAO all the time...

I know very little about ado, and am learning more every day, but from what I've seen and done. .update isn't needed in ado code... so try commenting it out and see how your code works

--JAmes
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[PSUIVERSON]

You do need it. What you don't need is .edit . Although I've experimented with .UpdateBatch which I think works on a global scale but I haven't tested it enough.

 

[Junior1544]

ok, my mistake... as I said, I'm just learning ado...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[PSUIVERSON]

Yeah I used DAO for awhile and then just stopped and did everything in ADO as people much more advanced than I recommended this. I remember in DAO I used to have go

.edit
rst!...
.update

all the time...The .edit method doesn't exist in ADO but I do think you're right, I can probably use .update on a global scale rather than after each and every change - or atleast on each recordset pass.

Thanks!