title: Outputting multiple reports in access vba causes error 3014, too many tables open
body: <p>I am working writing out reports from our company database using VBA and MS Access 2010.  I have almost 3000 employees and I need to write out 10 different reports for each employee and then combine those 10 reports into one pdf per employee.  These files are then saved in directories that are sorted by their duty station.</p>
  
  <p>The code I have written works great and does the job intended to do EXCEPT after writing out 1024 reports I receive an error.  Err.Number 3014, Cannot open anymore tables.</p>
  
  <p>According to what I could find on the internet this has to do with the Jet table references and is tricky to troubleshoot.  I have followed what advice I could find I believe I have properly closed out everything after use.  I thought maybe the problem arose in the merge pdf files routine but even if you comment that out it still fails at 1024 reports.</p>
  
  <p>I would like this code to be able to process about 30,000 reports without failing.  Any ideas or thoughts would be appreciated.</p>
  
  <pre><code>Public Function combined_report(EmployeeSelectionQuery As String)
  
  Dim DefaultPdfDir As String     ' contains path to where pdf files will be written on local computer
  Dim rst As Recordset            ' recordset object for set of selected plots from query:Employees_COMBINED
  Dim n_employees As Integer      ' Number of employees selected by query:Employees_COMBINED
  Dim current_employee_number As Variant     ' current employee number, used when writing combined reports
  Dim current_duty_station As Variant     ' current duty station, used when writing combined reports
  Dim strWhere As String          ' String containing the where clause for the combined openreport WhereCondition
  Dim arrayReport(0 To 9) As Variant      ' Array containing all the reports to be processed in combined
  Dim strReport As Variant        ' String containing prefix to reports
  Dim tempOutputPdfFile As String ' Individual report before they are combined
  Dim combinedOutputPdfFile As String     ' Combined report composed of individual reports REQUIRES that adobe acrobat - full version be installed.
  Dim intCounter As Integer       ' A iteration counter used to update the status bar
  Dim combOutputPdfFile As String ' Combined Output Pdf File Path
  
  On Error GoTo error_handler
  
  Set rst = CurrentDb.OpenRecordset(EmployeeSelectionQuery)
  
  'Force Access to accurately update .RecordCount property
  rst.MoveLast
  rst.MoveFirst
  n_employees = rst.RecordCount
  
  If n_employees = 0 Then
    Call MsgBox("No employees selected by query: " &amp; EmployeeSelectionQuery, vbCritical + vbOKOnly + vbDefaultButton1, "No Employees Selected")
    combined_report = False
  Else
  
    DoCmd.Hourglass True
  
    'Set HomeDir and create output folder
    DefaultPdfDir = "C:\temp"
    MakeDir DefaultPdfDir
  
  
    arrayReport(0) = "REPORT_1"
    arrayReport(1) = "REPORT_2"
    arrayReport(2) = "REPORT_3"
    arrayReport(3) = "REPORT_4"
    arrayReport(4) = "REPORT_5"
    arrayReport(5) = "REPORT_6"
    arrayReport(6) = "REPORT_7"
    arrayReport(7) = "REPORT_8"
    arrayReport(8) = "REPORT_9"
    arrayReport(9) = "REPORT_10"
  
  
    'Set counter to zero
    intCounter = 0
  
    Do While (Not (rst.EOF))
  
          'Get employee number and duty station to name the files and sort by directory
          current_employee_number = rst!EN
          current_duty_station = rst!DUTY_STATION
  
          'Make the output directory if it doesn't exist and specify the output file path
          MakeDir "C:\Final\" &amp; current_duty_station
          combOutputPdfFile = "C:Final\" &amp; current_duty_station &amp; "\" &amp; current_employee_number &amp; ".pdf"
  
  
          'Increment counter by one for each employee processed
          intCounter = intCounter + 1
  
          'Where statement used by DoCmd.OpenReport to run the report for one employee only
          strWhere = "[EN] = " &amp; current_employee_number &amp; " OR [en] = " &amp; current_employee_number
  
          'Process each report
          For Each strReport In arrayReport
  
              'Specify the file path and name for the report
              tempOutputPdfFile = DefaultPdfDir &amp; "\" &amp; current_employee_number &amp; "_" &amp; strReport &amp; ".pdf"
  
              'Update Status Bar
              Status ("Processing " &amp; intCounter &amp; " of " &amp; n_employees &amp; ": " &amp; tempOutputPdfFile)
  
              'Open the report and write it to a pdf file
              DoCmd.OpenReport strReport, acViewPreview, "", strWhere, acHidden
              DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, tempOutputPdfFile, False
              DoCmd.Close acReport, strReport, acSaveNo
  
              'Merge the files
              MergePdfFiles combOutputPdfFile, tempOutputPdfFile, combOutputPdfFile
  
  
          Next strReport
  
          'Delete the last temp file before moving on to the next employee
          DeleteFile tempOutputPdfFile
  
      rst.MoveNext
  
    Loop
  
    'Close everything up
    Status ("")
    rst.Close
    Set rst = Nothing
    DoCmd.Hourglass False
    combined_report = True
  
  End If
  
  Exit Function
  error_handler:
      MsgBox "Error: " &amp; Err.Number &amp; vbNewLine &amp; _
             "Description: " &amp; Err.Description, vbCritical, "combined_report function error"
      DoCmd.Hourglass False
      combined_report = False
      Status ("")
  
  End Function
  </code></pre>