[RESOLVED] Run-time Error Problem

[manofsteel00]

I'm getting the following runtime error message:

Run-time Error: '-2147467259(80004005)':File Sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

I get this error message on the following line of code:

recordSet.Fields("PICKSL") = Replace(recordSet.Fields("PICKSL"), "'", "")

This line of code is in the following function:

Private Function conform(db As String) As Boolean
    Set connection = New ADODB.connection
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= G:\Finance\Inventory Control\CycleCount\DB\CycleCount.mdb"
    connection.Open
    Set recordSet = New ADODB.recordSet
    recordSet.Open db, connection, adOpenKeyset, adLockPessimistic, adCmdTable
    
    If (recordSet.EOF = False) Then
        recordSet.MoveFirst
        
        Do Until recordSet.EOF
            If (db = "TEST") Then
                [B]recordSet.Fields("PICKSL") = Replace(recordSet.Fields("PICKSL"), "'", "")[/B]
                recordSet.Fields("SLOT") = Replace(recordSet.Fields("SLOT"), "'", "")
                recordSet.Fields("RES") = Replace(recordSet.Fields("RES"), "'", "")
                recordSet.Fields("PROD") = Replace(recordSet.Fields("PROD"), "'", "")
                recordSet.Fields("DESC") = Replace(recordSet.Fields("DESC"), "'", "")
                recordSet.Fields("PACK") = Replace(recordSet.Fields("PACK"), "'", "")
                recordSet.Fields("MANU ID") = Replace(recordSet.Fields("MANU ID"), "'", "")
                recordSet.Fields("HITIX") = Replace(recordSet.Fields("HITIX"), "'", "")
                recordSet.Fields("PALLET ID") = Replace(recordSet.Fields("PALLET ID"), "'", "")
            
                reSizeDesc (recordSet.Fields("DESC"))
                reSizeManUID (recordSet.Fields("MANU ID"))
            ElseIf (db = "PICKSL") Then
                recordSet.Fields("PICKSL") = Replace(recordSet.Fields("PICKSL"), "'", "")
                recordSet.Fields("HITI") = Replace(recordSet.Fields("HITI"), "'", "")
                recordSet.Fields("PACK") = Replace(recordSet.Fields("PACK"), "'", "")
                recordSet.Fields("DESC") = Replace(recordSet.Fields("DESC"), "'", "")
                recordSet.Fields("MANUID") = Replace(recordSet.Fields("MANUID"), "'", "")
                recordSet.Fields("PROD") = Replace(recordSet.Fields("PROD"), "'", "")
                            
                reSizeDesc (recordSet.Fields("DESC"))
                reSizeManUID (recordSet.Fields("MANUID"))
            End If
            
            recordSet.MoveNext
            DoEvents
        Loop
    End If
    
    recordSet.Close
    connection.Close
    Set recordSet = Nothing
    Set connection = Nothing
    
    conform = True
End Function

Any help would be greatly appreciated. Thank you.

[si_the_geek]

The only thing I can see that may be causing issues is the use of "connection" and "recordSet" as variable names - as they are also the data types of those variables.

I would strongly recommend changing the names, it may well solve the problem.

[manofsteel00]

Unfortunately I have been using connection and recordset as variable names throughout my program and it has not yet been problematic. However, I did try to run my program with difference variable names and I am still getting the same error message:

Run-time Error: '-2147467259(80004005)':File Sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

If anyone has any suggestions I would be greatly appreciative. Thank you.

[si_the_geek]

Do you always open a new connection when you open a recordset?

It may be that you several open at the time, and have reached the point where the number of avaiable connections has run out. I'm dubious that is the case, but it is a possibility.

It is perfectly fine to have just one connection object, which stays open throughout the life of the application, and is used by of your recordsets.


What are "reSizeDesc" and "reSizeManUID"? I presume they are subs, in which case you are calling them incorrectly - brackets around the parameters should only be used if you are using the Call keyword.

There is also the possibility that they are causing an issue, but to be able to tell we would need to see their code.



edit: I've just noticed that all this sub does is remove apostrophe characters from data.. are you really sure that is what you want to do?
(I know they can cause issues, but that is only if you are building SQL statements with their values)

[manofsteel00]

When this function is called the connection is the only connection to the database. However, right before I called a function that deleted the contents of a table in the database. Here's the code for that function:

'CLEARDATABASE()***************************************************************
Private Sub clearDatabase(db As String)
    Dim sql As String
    
    Set newConnection = New ADODB.connection
    newConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= G:\Finance\Inventory Control\CycleCount\DB\cycleCount.mdb"
    newConnection.Open
    Set newRecordSet = New ADODB.recordSet
   
    sql = "DELETE * FROM [" & db & "]"
 
    newRecordSet.Open sql, newConnection, adOpenKeyset, adLockPessimistic, adCmdText
        
    Set newRecordSet = Nothing
    Set newConnection = Nothing
End Sub
'ENDOF*CLEARDATABASE()*********************************************************

After this function is called a sub is called:

'WAIT()************************************************************************
Private Function wait() As Boolean
    Dim i As Long
    For i = 0 To 10500
        Dim j As Long
        For j = 0 To 10000
            DoEvents
        Next j
        DoEvents
    Next i
    
    wait = True
End Function
'ENDOF*WAIT()******************************************************************

I do this because the connection to the database in clearDatabase() is not broken soon enough and will generate an error saying that there is still a connection to the database. Therefore, wait() allows time for the connection to the database to break.

Then my function conform() is called to take the "'" out of the various fields so that I can run SQL statements with the data.

However, it is in the first line of the conform() function that it gives me an error: Run-time Error: '-2147467259(80004005)':File Sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

reSizeDesc() and reSizeManUID() are functions that decrease the length of these fields for printing purposes.

Any help would be GREATLY appreciated. Thank you.

[techgnome]

Your clearDatabase sub needs to be rewritten.... it's not a good idea to use Recordset.Open for running action queeries like DELETEs. You should bew using the command object instead and using its .Execute method.

Secondly, you are not closing your connection in the ClearDatabase sub. You're simply setting the connection to nothing. That simply sets the pointer to null, and may or may not actualy close the connection.

-tg

[si_the_geek]

Agreed.

Also the time the Wait function takes will vary dramatically by computer, and by the processes running at the time - if you want a proper wait function use a mixture of DoEvents and Sleep in a Do Loop (set to exit after a certain amount of time). The usefulness in this case is hard to see tho, a single DoEvents should do the job.

Then my function conform() is called to take the "'" out of the various fields so that I can run SQL statements with the data.

There is no need to do that, as you can use ' in SQL statements (you just need to replace any inside values with two ' characters).

All you are doing with this is intentionally damaging the data.

reSizeDesc() and reSizeManUID() are functions that decrease the length of these fields for printing purposes.

Please tell me they aren't actually modifying data too... you should only be doing that as part of the print process, not with the data in the database.

[manofsteel00]

Thanks guys I got it to work. I found some information on Microsoft's website. If anyone else is getting this error here's the link for the webpage: http://support.microsoft.com/default...-US;815281#kb2

Thanks again everyone!