Hello everyone
Could you please help me with :
When I tried open recordset with ADO I got a message :
" You attempted to open a database that is already opened exclusively by user 'Admin' on machined 'mymachine'. Try again when the database is available". OS : Windows 98, Access 2000

But on Windows 2000, Access 2000 I have message :
"The database has been placed in a state by user 'Admin' on machinte 'my machine' that prevents it from being opened or locked"

The only form which is open doesn't have any subforms and doesn't open any other recordsets

Thanks
Serge

It sounds like the security settings within the access table need to be updated. There may be a password and uid that allows "admin" access...if so, you need to include this in the connect statement

Hope this helps

John

Your database may have been closed improperly and it is now opened exclusively by the previous user (maybe you) if so, re-start the computer. Or you are opening the database exclusively by default, in that case, go to tools, options , advanced and make sure that your database default open mode is “shared”. If someone else has it opened exclusive, you will have to wait until that individual closes the database.



Sal

Thanks Sal for your help.

I tried to do that way before , I mean when I close database inside Access, than closed Access itself. And I checked if any ".ldb" file is opened, and I restarted computer as well, but .... no luck.
 And in settings I have Open mode: "Shared", Record locking : " No Locks" , Open databases using record-level locking : "checked",
Enable DDE refresh: "checked", but again I got the same Message. :(
  Form from which I run the code doesn't have any ADO connections
And I am the only one user of this database on stand along computer.
--------------------------------------------------------
Serge

Thanks John for your help
How can I update the security settings within the access table?
There is no password
I'm only one user of this database as Admin.
Any more ideas?
Thanks
Serge

Is the database open on your machine at the same time you are trying to connect to it via ADO??

Show all of your code. You may be locking the database another way. You may be trying to change an object inside the mdb efore you open the recordset.



Sal

Yes database is open ! How it should be otherwise?

Hi Sal

There is my code where I got the problem on opening first recordset
It's clumsy I know (I'm an amateur in databases :) )
First recordset I want to make Disconnected only because I need to open other recordset from the same table

================================================

Private Sub cmdInsert_Click()

Dim AptNum As String
Dim Rent As Currency
Dim Park As Currency
Dim Misc As Currency
Dim SQLPrevMonth As String
Dim SQLInsert As String
Dim PrevMonth As String
Dim MonthOfRent As Date
Dim PayDate As Date


Dim cnn1 As ADODB.Connection
Dim cnn2 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset

On Error GoTo Err_cmdInsert_Click


 '---------------- asign Variables--------------------------

MonthOfRent = DateAdd("m", 1, [Forms]![frm_update_payments_monthly]![cmbMonthToPayFor])

PayDate = Date
PrevMonth = [Forms]![frm_update_payments_monthly]![cmbMonthToPayFor]


SQLPrevMonth = "SELECT tbl_payments.apt_num, " & _
        " Max(tbl_payments.req_apt_rent) AS MaxOfreq_apt_rent, " & _
        " Max(tbl_payments.req_park_rent) AS MaxOfreq_park_rent, " & _
        " Max(tbl_payments.req_miscell) AS MaxOfreq_miscell " & _
              "FROM tbl_payments " & _
              "GROUP BY tbl_payments.apt_num " & _
              "HAVING (((Max(tbl_payments.req_apt_rent))>=700) " & _
              "AND((Max(tbl_payments.month_of_rent)) = " & PrevMonth & ")) " & _
              "ORDER BY tbl_payments.apt_num;"


SQLInsert = "INSERT INTO tbl_payments " & _
            "(apt_num, month_of_rent, pay_date, req_apt_rent, req_park_rent,req_miscell) "
       SQLInsert = SQLInsert & " VALUES ("
       SQLInsert = SQLInsert & "'" & AptNum & "'"
       SQLInsert = SQLInsert & ",'" & MonthOfRent & "'"
       SQLInsert = SQLInsert & ",'" & PayDate & "'"
       SQLInsert = SQLInsert & ",'" & Rent & "'"
       SQLInsert = SQLInsert & ",'" & Park & "'"
       SQLInsert = SQLInsert & ",'" & Misc & "'"
       SQLInsert = SQLInsert & " )"


 '----------------make first disconnected recordset ----------------

On Error GoTo cnn1_Err

Set cnn1 = New ADODB.Connection
With cnn1
    .Mode = adModeShareExclusive
    .IsolationLevel = adXactIsolated
    .Provider = "Microsoft.Jet.OLEDB.4.0;"
    .Open "'C:\WINDOWS\Desktop\Super Office 1.77.mdb", "admin", ""
End With

Set rs1 = New ADODB.Recordset
With rs1
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Open SQLPrevMonth, CurrentProject.Connection
   Set .ActiveConnection = Nothing
End With

cnn1.Close
Set cnn1 = Nothing

cnn1_Err:
            If Not cnn1 Is Nothing Then
                Set cnn1 = Nothing
            End If
            MsgBox Err.Description
            Exit Sub

'---------------------- Looop-----------------------
Do Until rs1.BOF


    AptNum = rs1!apt_num
    Rent = rs1!MaxOfreq_apt_rent
    Park = rs1!MaxOfreq_park_rent
    Misc = rs1!MaxOfreq_miscell

    On Error GoTo cnn2_Err

    Set cnn2 = New ADODB.Connection
    Set rs2 = New ADODB.Recordset


    With cnn2
        .Provider = "Microsoft.Jet.OLEDB.4.0;"
        .Open "C:\WINDOWS\Desktop\Super Office 1.77.mdb", "admin", ""
    End With

    rs2.Open SQLInsert, cnn2, adOpenKeyset, adLockOptimistic

    rs2.Close
    cnn2.Close
    Set rs2 = Nothing
    Set cnn2 = Nothing

cnn2_Err:
                If Not cnn2 Is Nothing Then
                    Set cnn2 = Nothing
                End If
                MsgBox Err.Description
                Exit Sub


    AptNum = ""
    Rent = 0
    Park = 0
    Misc = 0

    rs1.MoveNext

Loop
'--------------------end of the loop--------------------

Exit_cmdInsert_Click:
    Exit Sub

    MsgBox Err.Description
    Exit Sub


Err_cmdInsert_Click:
    MsgBox Err.Description
    Resume Exit_cmdInsert_Click

End Sub

================================================== ======

Actually all that code above is my try to insert a new record in the table which would repeat the same values in "Rent", "Park", "Misc" and insert Next "MonthOfRent".
Could you suggest better idea to overcome such logic
Thanks a lot for your help
Serge

    .Mode = adModeShareExclusive this is opening exclusive
    .IsolationLevel = adXactIsolated

Why are the two connections so different? use the same type of connection string.


IsolationLevel = adXactIsolated this I have never used. What does it do?




Sal