Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Clearing Memory for Open tables (2002)

    Hi,

    I have a database that runs a variety of reports with multiple subreports. Only one report is run/printed at a time and it is closed before the next one is generated. After I've printed 4 reports and try to print a 5th report, I get the error message to the effect that no more tables can be opened. If I shut down the database and reopen it I can print another 4 reports before it happens again. I'm wondering if there is code that will clear the memory or background data that may be open in the database so that I don't have to keep closing and reopening the database.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clearing Memory for Open tables (2002)

    I found a mention that this was a problem with some versions of Jet 4.0. See MSKB article How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine and use it to check which version you have. If necessary, download and install the latest version.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clearing Memory for Open tables (2002)

    Hi Hans,

    Well, the good news is that after checking, my computer already has the most current Jet version so although I'm "current" that doesn't seem to be what is causing the issue.

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clearing Memory for Open tables (2002)

    The error message is misleading. The "tables" it mentions are actually data connections used by the Jet database engine underlying Access. You have a total of 2048 data connections that can be open at any one time. Each open bound form, bound report, and recordset consumes one or more connections, as well as each list box and combo box, as well as several other database objects. Make sure that you close all recordsets that you open in VBA when you don't need them any more and set them to Nothing, Also, don't keep more forms open than necessary.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clearing Memory for Open tables (2002)

    Hi Hans,

    I thought I asked this but apparently must've did it in my sleep because I don't see that I ever posted it!!!

    >>Make sure that you close all recordsets that you open in VBA when you don't need them any more and set them to Nothing

    How do I set the recordsets to "nothing" when they are closed? I'm not sure if I'm asking this correctly. There is only one form open with a list box and three text boxes. The error comes after running reports more than 3 times. The reports are only open one at a time and then closed before the next one is run. Is there a way to be sure that none of the recordsets from the previous report are still open?

    Thanks,
    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clearing Memory for Open tables (2002)

    Do you have code in your database that contains something like

    Dim rst As DAO.Recordset

    or

    Dim rst As ADODB.Recordset

    If so, you should always close the recordset at the end of the procedure (or when you've finished using it):

    rst.Close
    Set rst = Nothing

    If you don't use recordsets in code, the above doesn't apply.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clearing Memory for Open tables (2002)

    Hi,

    No I don't have that in the code. This is how the code reads:
    __________________________________________________ ________________
    'Alerts if no choice made

    If IsNull(Me.optReports) Then
    MsgBox "Please make a report type selection."
    Me.optReports.SetFocus
    Exit Sub
    Else:


    'Opens selected report


    Select Case Me.optReports
    Case 1
    DoCmd.OpenReport "rptOutcomeSummary - adliadl", acViewPreview
    Case 2
    DoCmd.OpenReport "rptOutcomeSummary - demographics", acViewPreview
    Case 3
    DoCmd.OpenReport "rptOutcomeSummary - dx", acViewPreview
    Case 4
    DoCmd.OpenReport "rptOutcomeSummary - physicalstatus", acViewPreview
    Case 5

    DoCmd.OpenReport "rptOutcomeSummary - adliadl", acViewPreview

    DoCmd.OpenReport "rptOutcomeSummary - demographics", acViewPreview

    DoCmd.OpenReport "rptOutcomeSummary - dx", acViewPreview

    DoCmd.OpenReport "rptOutcomeSummary - physicalstatus", acViewPreview
    End Select
    End If
    __________________________________________________ _______________

    Would I insert the two components at the beginning and end?

    Thanks,
    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clearing Memory for Open tables (2002)

    Leesha, if you don't have Recordset objects in your code, there is no reason to introduce them.

    I notice that if optReports = 5, you open four reports at once. Is this the situation that causes the error, or does it also occur if optReports is 1, 2, 3 or 4?

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clearing Memory for Open tables (2002)

    Hi,

    No I actually don't even use that option since it gave me the error. I print each report separately now. I open, print and close before selecting the next report. If I try to do this more than 4 times I get the table error.

    Leesha

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clearing Memory for Open tables (2002)

    Apparently the reports are very complicated. I'm afraid it's hard to say more without seeing the database, and I don't know if we could help if we saw the database. This kind of problem is difficult to solve.

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clearing Memory for Open tables (2002)

    The database itself is pretty simple. It is the queries that are quite complex and huge, all close to the limits of what can be put in them. I'd upload the database but am pretty sure its going to be one of those issues that the time used to find a fix is not worth it as I can simply shut down that database and reopen to get the reports I need.

    Thanks for the help! As always its a learning experience,
    Leesha

Posting Permissions

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