VBA to solve “cannot open any more tables” error (1 Viewer)

corai · May 25, 2012

Hi All

I am using the following code to generate a series of reports based on a query (qryRptS). This is quite intensive on the Jet database engine as after producing reports for the first 9 records an error appears declaring that Access “cannot open any more tables”.

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qryRptS")

qdf.Parameters("[Forms!frmCompany!StartDate]") = Forms!frmCompany!StartDate
qdf.Parameters("[Forms!frmCompany!EndDate]") = Forms!frmCompany!EndDate
Dim rst As DAO.Recordset

Set rst = qdf.OpenRecordset()

rst.MoveFirst

Do While rst.EOF = False

Dim filename As String
filename = rst.Fields("Trading Name") & " " & rst.Fields("Company ID")
filename = Replace(filename, "/", "-")

DoCmd.OpenReport "rptQryS", acViewReport, "", "[Company ID] = " & rst.Fields("Company ID"), acNormal
DoCmd.OutputTo acOutputReport, "rptQryS", "PDFFormat(*.pdf)", "\\HBSRV01\common\PROJECTS\Q1 Test\" & filename & ".pdf", False, "", , acExportQualityPrint
DoCmd.Close acReport, "rptQryS"

rst.MoveNext

Loop

End Function

A lot of the solutions to this problem involve simplifying queries/open tables. I’m not sure whether I would be able to do this to the extent that I would still be able to automate report generation for approx. 600 records.

However, I believe that if I close the recordset Access will clear the “tables memory”, i.e.:

rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing

Is there a clever way that I can get Access to remember which record it was on, so that I can close a recordset, reopen it and resume at the right record? Could I do this by setting a global variable, e.g. Global dbs as DAO.Database? If anyone has any thoughts on this then please let me know.

Thanks.

KenHigg · May 25, 2012

I'm going to take a quick stab at this and say maybe you could insert a couple strategically placed DoEvents?

Otherwise I'd say clunking through a record set while do file stuff is probably not a good idea...

spikepl · May 25, 2012

I wonder what else you have going on in your db. Your function doesn't close the opened recordset, and if you have more of this type of code floating around your db, then yes, Access will eventually give up.

Closing the recordset after 8 reports and reopening sounds like a band-aid solution, which may or may not work. Without knowing more about your db, I suspect the cause of your predicament is elsewhere.

spikepl · May 25, 2012

Here is some fun reading on the subject (0.25 seconds on google)

http://access.mvps.org/access/bugs/bugs0010.htm
http://stackoverflow.com/questions/1807934/ms-access-cant-open-any-more-tables-using-jdbcodbcdriver

VilaRestal · May 25, 2012

I think it must be the opening and closing of reports that's doing it. They're not releasing their recordsets before the next loop opens another.

Could you not open the report once at the start of the loop and then filter it each loop?:

Code:

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qryRptS")
qdf.Parameters("[Forms!frmCompany!StartDate]") = Forms!frmCompany!StartDate
qdf.Parameters("[Forms!frmCompany!EndDate]") = Forms!frmCompany!EndDate
Dim rst As DAO.Recordset
Set rst = qdf.OpenRecordset()
rst.MoveFirst
DoCmd.OpenReport "rptQryS", acViewReport, , , acNormal
Dim filename As String
Do While rst.EOF = False
    Reports!rstQryS.Filter = "[Company ID] = " & rst.Fields("Company ID")
    filename = rst.Fields("Trading Name") & " " & rst.Fields("Company ID")
    filename = Replace(filename, "/", "-")
    DoCmd.OutputTo acOutputReport, "rptQryS", "PDFFormat(*.pdf)", "\\HBSRV01\common\PROJECTS\Q1 Test\" & filename & ".pdf", False, "", , acExportQualityPrint
    rst.MoveNext
Loop
DoCmd.Close acReport, "rptQryS"

spikepl · May 25, 2012

Sure thing. But if you are only 8 openings away from the limit (2024 table handles?) , then something else got you this far.

Other than that, the record source of the report could be filtered itself , so the report did not need to be opened.

VilaRestal · May 25, 2012

The report needs to be open to output it as a pdf I believe.

I think the filtering should work. It will only open the recordset twice. Once for the VBA recordset, once for the report. I'm sure that wouldn't produce 2024 table handles. But if the loop iterates a thousand times and each report doesn't close its recordset immediately it's closed then that easily would.

spikepl · May 25, 2012

The report needs to be open to output it as a pdf I believe.

Open in the sense that Access of course needs the report object to do its stuff. But I save reports to pdf just using OutputTo, without prior explicit opening of the report. So the repoort does get opened, of course, one way or another. I do not know whether the resources consumed are the same in both cases.

Since the OP's code fails after report number 8 or 9, I still wonder what other stuff (~ 2016 table handles) goes on in the db.

VilaRestal · May 25, 2012

True, but I would never bet my life that an error message in Access actually means what it says.

And neither would I bet everything on 2048 being the actual limit of tables handles - the point where that error occurs. Microsoft are often very coy about such limitations in their products. (It may be a theoretical limit but perhaps that error can appear at much less under certain circumstances.)

I expect each report opens more than 2 table handles. A dozen would not be excessive. But that still leaves the best part of 2000 table handles free (if M$ are to be believed).

Perhaps this is a split database with other users using the back end at the same time. If that's the case then the solution is simple: don't try and do such an intensive operation until all other front ends are closed.

KenHigg · May 25, 2012

Even though I suspect this as a whole is a bad practice, he could step through the code and find out for sure what's happening if he utilizes the locals and immediate window features...

Pat Hartman · May 25, 2012

The problem is that the Outputto method does not provide a "where" argument as the OpenForm and OpenReport methods do. To get around this failing, add hidden fields to the form that runs the report and use them to hold the criteria for the report. Change the report's RecordSource query to reference the hidden form fields so the report opens for only one record at a time.

Using this technique you can get rid of the open and close report steps and just leave the OutputTo.

Vila's method might actually be faster since it eliminates the overhead of opening and closing the report but without testing, I couldn't say which method would work better.

corai · May 28, 2012

Hi All,

Thanks for all the advice. The OpenReport command was definitely the culprit – I tried Vila’s initial suggestion of taking DoCmd.OpenReport out of the loop but even then it eventually gave me the same error.

The method of saving reports to pdf using just OutputTo works like a dream though. As Pat mentioned the key issue if doing it this way is finding a way to output reports which are based on individual records in the set. Filtering the report’s recordsource query using the form (e.g. [CompanyID]=[Forms]![frmCompany]![CompanyID]) works perfectly for this.

For the sake of posterity here is the final code I used:

Code:

[FONT=Calibri][FONT=Calibri][SIZE=2]Dim qdf As QueryDef[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]Set qdf = CurrentDb.QueryDefs("qryRptS")[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]qdf.Parameters("[Forms!frmCompany!StartDate]") = Forms!frmCompany!StartDate[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]qdf.Parameters("[Forms!frmCompany!EndDate]") = Forms!frmCompany!EndDate[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]Dim rst As DAO.Recordset[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]Set rst = qdf.OpenRecordset()[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]rst.MoveFirst[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]Dim filename As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]Do While rst.EOF = False[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]   filename = rst.Fields("Trading Name") & " " & rst.Fields("Company ID")[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]   filename = Replace(filename, "/", "-")[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]   DoCmd.OutputTo acOutputReport, "rptQryS", "PDFFormat(*.pdf)", "\\HBSRV01\common\PROJECTS\Q1 Test\" & filename & ".pdf", False, "", , acExportQualityPrint[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]   rst.MoveNext[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]   DoCmd.SelectObject acForm, "frmCOMPANY", False[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]   DoCmd.GoToRecord acForm, "frmCOMPANY", acNext[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]Loop[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]End Function[/SIZE][/FONT]
[/FONT]

Thanks again!

Pat Hartman · May 29, 2012

DoCmd.SelectObject acForm, "frmCOMPANY", False
DoCmd.GoToRecord acForm, "frmCOMPANY", acNext

What is this for? There is no reason to move through the form's recordset. You have opened a query that contains the data needed to control the report creation and are moving through it using rst.MoveNext. Remove these two lines.

corai · May 29, 2012

Hi Pat,
Basically I did it like that because I didn’t know how else to filter the report’s recordsource (qryRptS) other than [Company ID]=[Forms]![frmCompany]![CompanyID]. Is there a way to write something like this:

Code:

Reports!rptQryS.Filter=”[Company ID] = “ & rst.Fields(“Company ID”)

… in the “Filter” section of the report’s Property Sheet?

VBA to solve “cannot open any more tables” error (1 Viewer)

corai

Registered User.

KenHigg

Registered User

spikepl

Eledittingent Beliped

spikepl

Eledittingent Beliped

VilaRestal

';drop database master;--

spikepl

Eledittingent Beliped

VilaRestal

';drop database master;--

spikepl

Eledittingent Beliped

VilaRestal

';drop database master;--

KenHigg

Registered User

Pat Hartman

Super Moderator

corai

Registered User.

Pat Hartman

Super Moderator

corai

Registered User.

Similar threads

Users who are viewing this thread