Error# -2147467259 in ado for accesss 2000

[Guest]

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?

 

[Rob Oldfield]

http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part2/ch08.mspx

You should use the error handling to ensure that, when your code goes bang,
that connections etc are closed.

 

[Guest]

Dear Rob,
Thanks for you reply. Please see below a listing of the procedure where the
error keeps happening. The error is handled by using vb ways to do so. I need
to know what action to take in order to continue. Thanks


Function CountRecordsI(strTableName As String, strKeyField As String,
lngValue As Long) As Integer
'This procedure returns the number or records in a table based on a criteria
'in the variable lngValue
On error goto err_Handle
Dim rst As New ADODB.Recordset
Dim i As Integer
'Set Cnn = New ADODB.Connection
'If Cnn.State = adStateOpen Then
'Cnn.Close
'End If
Cnn.Open CurrentProject.Connection

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
err_Handle:
Screen.MousePointer = vbDefault

If Cnn.Errors.Count > 0 Then
For Each errLoop In Cnn.Errors
Select Case errLoop.NativeError
Case 170
MsgBox "Error in a word. If the word contains one
apostrophe, type two."
Exit For
Case 4002
MsgBox "Access denied.", vbCritical + vbOKOnly
Exit For

Case 15000
MsgBox "You should logon as an administrator to
complete this task."
Case 15023, 15025
MsgBox "Already existing record."
Case 30001
MsgBox "Unable to delete record since it is linked
to other records", vbCritical + vbOKOnly
Exit For
Case 547
MsgBox "Unable to delete record since it is linked
to other records", vbCritical + vbOKOnly
Exit For
Case 2627

MsgBox "Number attributed already exists!",
vbCritical + vbOKOnly
Exit For

Case Else

MsgBox "Error no: " & errLoop.NativeError & _
vbCr & "Description: " & errLoop.Description
End Select
Next errLoop
'Cnn.Close
End Function

 

[Rob Oldfield]

I think you just need to tidy your code up a bit. You've obviously been
playing with this and it's a bit all over the place at the moment. For
example, you're opening cnn without defining it, and you're not doing a
cnn.close. The correct way to do it would be something like...

On Error GoTo Err_Handler
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
cnn.Open CurrentProject.Connection
With rst
'Set properties
.Open
End With
'Do your work
Err_Handler:
rst.Close
cnn.Close

One other thing, you can avoid the issue with single speech marks needing to
be doubled up by just using replace(whatever,"'","''")