Error# -2147467259 in ADO for ms access 2000

[mpoincare]

Hi Everyone,
I am having an error message with some module on an access program that I am creating. I am using ADO for data access to the tables in the
database. In each module, I open a connection, associate a recordset to the open connection, close the recordset and finally the connection. But from
time to time ms access display the following message:

"Error# -2147467259. The database has been placed in a state byuser
'Admin' on machine 'SERVER01' that prevents it from being opened locked."

Whenever this error occurs, I have sometimes to totally close the
database and reopen it. Can anyone tell me how I can manage this error
programmatically so that I don't have to close the database every
time? The code is below:

Function CountRecordsI(strTableName As String, strKeyField As String, lngValue As Long) As Integer
Dim rst As New ADODB.Recordset
Dim cnn as new ADODB.Connection
Dim i As Integer
Cnn.Open CurrentProject.Connection 'This is where the code hangs and the Error# -2147467259 is displayed

With rst
.ActiveConnection = Cnn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "Select * From " & strTableName & " where " & strKeyField & " = " & lngValue & ""
.Open
If .RecordCount = 0 Then
i = 0 'No record found
Else
i = .RecordCount ' record found
End If
'.Close
End With
Set rst = Nothing
Set Cnn = Nothing

'Cnn.Close
CountRecordsI = i
End Function

[ITGURU]

Dear mpoincare,

After reviewing the code I have following solutions for your problem:
Solution No.1:
Remove following line of Code
Dim rst As New ADODB.Recordset
Dim cnn as new ADODB.Connection
Dim i As Integer
Cnn.Open CurrentProject.Connection

Add following line of code instead of above line of code:
Dim rst As New ADODB.Recordset
Dim cnn as ADODB.Connection
Dim i As Integer
set cnn = CurrentProject.Connection

For detail refer following links:
1. http://p2p.wrox.com/archive/access/2001-06/19.asp
2. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50584

Solution No.2:
Close your Recordset and Connection object before setting them to nothing.

Solution No. 3:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307640