Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't Open Any More Databases? (Access 97)

    Hope this post hasn't gone to the wrong forum, it may be more of a VB issue than Access?

    I have a database that does a number of things. Most importantly it uses VB to access another application as an object and to get data from it. No problems.

    I've recently been asked to get a lot of data from this other application. No problem says I. I have a database that can do this.

    However, before there was always an operator telling Access what field to look for from this other application (For example telephone. You plug in the telephone number and Access will go off to the in house application and get some other details that match the telephone number)
    Now I have a big list of telephone numbers and I'm automating the client bit. Access now has an extra table full of telephone numbers that it plods through and gets the details. (I'm using recordsets to go through this table)
    Only after a couple of hundred records I get a message saying "Can't open any more databases." Prior to that... It's all fine.
    If I stop it there and start it again... It works (For a while)

    I'm a bit bemused [img]/forums/images/smilies/tongue.gif[/img]

    Why should this happen? Do people know what this message is and how to address it? Do people have examples that I can review to see if I'm making the same mistakes? Do people have any good tips about using Objects in Access?

    As always your help is always appreciated. Even if you can't help it's nice to know that I can moan to the world in general about my woes!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases? (Access 97)

    If you open a database and recordsets you have to close them also.
    Look in your code if you find something like
    MyDatabase.Close
    MyRecordset.Close
    If not add it at the appropriate place.
    Francois

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't Open Any More Databases? (Access 97)

    If by VB you mean VBA in Access, then you've come to the right place. In addition to closing objects as Francois suggested, you also need to Set them to Nothing in order to recover the resources allocated to those objects:

    Set MyDatabase = Nothing
    Set MyRecordset = Nothing

    If you don't do this, you may not be able to close Access when you close the database.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases? (Access 97)

    I'm no epxert but I think I'm closing everything down as I go along.

    I have one procedure that basically ticks through the list of telephone numbers (using a recordset). Active database and a recordset defined. At the END of this procedure they definately get closed / set to nothing.

    Within this procedure I call forms to open. This is what would have happend if somebody was actually using the database.
    Each form opens. Does something (Usually grabbing data) and then closes.
    All objects within those forms end up closed / set to nothing.
    When the form is closed my procedure opens the next form.

    Three forms later it's finished for that record and goes to the next record from my recordset.

    Lots of stuff gets passed to some modules byref - but I thought that that took care of the memory itself.

    Does this (Very vaugue) description give anybody any idea why I'm getting the "Can't open any more databases" message?

    Oh yes. I usually get this message on the Docmd.OpenForm command. No consistancy about which form it errors on though.

    Help?

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can't Open Any More Databases? (Access 97)

    Are you getting any sort of error message with the problem? You normally should be, and that may help track down the details of what's happening.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases? (Access 97)

    I have no other error than an message box which simply states "Can't open any more databases"

    No further error description is given.

    I can still execute the code I just need to step through whatever form is open at the time and then once I return to the main procedure I can simply run from there. (It will then run for another unspecified / random length of records and do it again)
    Nothing else is running on this machine.

    I could get through the work by just manually stepping in when the code breaks - but something must be wrong somewhere. I intend to find out where!!

    Thanks for thinking of me though.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can't Open Any More Databases? (Access 97)

    Do you have error handling effective in your code? If so is it kicking in? If it is, you should be able to display the error message number. My hunch would be along the same lines as previously suggested by Francois and Charlotte. Are you actually opening the database each time you access a record? In any event, if you can post a slimmed down version of you code, perhaps one of us can seem something amok.

    One other possibility to look at is the amount of RAM on your workstation, and the amount of free disk space on your C: drive. Shortages of either of those could cause Access to complain.
    Wendell

  8. #8
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't Open Any More Databases? (Access 97)

    Thanks for the offer.

    Up for riddicule then I commit this code:



    Private Sub btnRip_Click()
    'On Error GoTo err_btnRip
    Dim Sessions As Object
    Dim System As Object

    Dim dbs As Database
    Dim rstDealer As Recordset

    Dim lrowcol, lrecords As Long

    Dim ripped As String

    Dim strdocname As String

    Set System = CreateObject("EXTRA.System")
    Set Sessions = System.ActiveSession

    'DoCmd.Hourglass True

    Dim intTrigger As Boolean
    Dim intLoop As Integer


    'need to open recordset
    Set dbs = CurrentDb
    Set rstDealer = dbs.OpenRecordset(rstTable)

    With rstDealer
    .MoveLast
    Debug.Print "Number of records = " & .RecordCount
    Me![lbl002].Caption = .RecordCount
    .MoveFirst
    .MoveLast

    lrowcol = .Fields.Count

    'itterate through values
    For lrecords = 1 To .RecordCount
    Me![lbl001].Caption = lrecords
    Me.Repaint
    .MoveFirst
    .Move (lrecords - 1)
    Debug.Print .Fields(1)

    If Time >= #7:30:00 PM# Then
    MsgBox "Suspended due to Chartel Shutdown: Login to Chartel and Click OK"
    End If

    'Ensure at OPUS screen.
    If OPUSHandler(System, Sessions) = False Then Call GetToOpus(System, Sessions)


    DoCmd.OpenForm ("Date Dialog")

    Me.Repaint
    'Enter Settle Account
    Call EnterOAcc(System, Sessions, .Fields(1))

    'Should now have account details

    If ScreenGrab(System, Sessions, 2, 51, 2, 67) = "ACTION SUCCESSFUL" Then
    Call OPUSSub(System, Sessions, "22")

    'screen 22 succesful
    If ScreenGrab(System, Sessions, 2, 51, 2, 67) = "ACTION SUCCESSFUL" Then DoCmd.OpenForm ("Frm Option22")

    Call ReturnToOpus(System, Sessions, .Fields(1))
    Call OPUSSub(System, Sessions, "24")

    If ScreenGrab(System, Sessions, 2, 51, 2, 67) = "ACTION SUCCESSFUL" Then DoCmd.OpenForm ("Frm Consol")

    End If

    'Should now have all OPUS details, so get application details.
    Me.Repaint
    If MENUHandler(System, Sessions) = False Then Call GetToMENU(System, Sessions)

    'Enter Prop Account
    Call EnterMAcc(System, Sessions, .Fields(0))

    If ScreenGrab(System, Sessions, 2, 51, 2, 68) <> "PROPOSAL NOT FOUND" Then

    While ScreenGrab(System, Sessions, 1, 24, 1, 27) <> "PSYD"
    Call ScreenType(System, Sessions, "<Enter>")
    Call ScreenSettle(System, Sessions)
    Wend

    strdocname = "Frm Financial"

    If ScreenGrab(System, Sessions, 1, 24, 1, 27) = "PSYD" Then DoCmd.OpenForm (strdocname)

    While blnReady = False

    Wend

    Me.Repaint
    If blnReady = True Then DoCmd.Close acForm, "Date Dialog", acSaveNo

    End If

    .MoveNext

    Next lrecords

    End With

    DoCmd.Hourglass False


    MsgBox "Finished"
    '
    exit_btnRip:

    Set dbs = Nothing
    Set rstDealer = Nothing
    DoCmd.Hourglass False
    Set System = Nothing
    Set Sessions = Nothing

    Exit Sub

    err_btnRip:
    Select Case Err
    Case Else
    MsgBox Err.Description
    Resume exit_btnRip
    End Select
    End Sub




    It was written in a blinding hurry and is I suspect the cause of all my woe. I don't think there is anything wrong with he forms it calls because these have been used for months quite succesfully. (If you remember this code was to batch run a whole list of records and is meant to be a one off run)

    Anything glaringly wrong?

    By the way. Thanks for the interest shown - you've all made it to my Christmas card list.
    (I really am grateful)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •