Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 17th, 2004, 10:27 AM
Registered User
 
Join Date: Jan 2004
Location: Toronto, Ontario, Canada.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Attempted to open a database that is already open

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
Reply With Quote
  #2 (permalink)  
Old January 19th, 2004, 10:19 AM
Authorized User
 
Join Date: Dec 2003
Location: Middletown, CT, USA.
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old January 19th, 2004, 12:26 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old January 19th, 2004, 09:33 PM
Registered User
 
Join Date: Jan 2004
Location: Toronto, Ontario, Canada.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old January 19th, 2004, 09:39 PM
Registered User
 
Join Date: Jan 2004
Location: Toronto, Ontario, Canada.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old January 20th, 2004, 09:40 AM
Authorized User
 
Join Date: Dec 2003
Location: Middletown, CT, USA.
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Reply With Quote
  #7 (permalink)  
Old January 20th, 2004, 11:19 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old January 20th, 2004, 01:02 PM
Registered User
 
Join Date: Jan 2004
Location: Toronto, Ontario, Canada.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes database is open ! How it should be otherwise?





Reply With Quote
  #9 (permalink)  
Old January 20th, 2004, 01:22 PM
Registered User
 
Join Date: Jan 2004
Location: Toronto, Ontario, Canada.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old January 20th, 2004, 03:16 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

    .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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open document, Open second doc and copy to first justabeginner Word VBA 1 March 7th, 2007 01:47 AM
Open the "Open File" dialogue box piratelordx Access VBA 4 March 14th, 2006 09:08 PM
Can We open database in different server? por Classic ASP Basics 0 December 18th, 2005 08:25 PM
Can't open .MDB database saxitalis C# 1 November 7th, 2003 03:18 PM
error trying to open a database Boomer ASP.NET 1.0 and 1.1 Basics 2 June 22nd, 2003 01:46 PM



All times are GMT -4. The time now is 01:32 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.