• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

Error 3014 too many tables open

I developed an application using Access 2000 which runs fine. I tried to run the application on an xp machine using Access 2002 and about half way through the application, I got the Error 3014, too many tables open.

I check Google for the Error Code (3014) and found that there may was a problem with jet 3.5. The site that I found had a patch.

Access 2002 was using jet 4.0, but just to be safe I installed jet 3.5 and used the patch.

I still have the problem.

I have checked all of my code and I close every recordset that I open whenever I am not needing it open.

I do have a few DLOOKUP's - is this opening tables?

After the application bombs, if I try to run it again, it immediately gives me the error message.

My questions are as follows:
1. Do DLOOKUP's open tables? if so how do I close them.

2. Is there any debugging feature where I can tell what tables are open?

Thank you,
  • 5
  • 3
  • 2
  • +3
1 Solution
Check to be sure all recordsets are closed and set to nothing when you are done with them.
if your recordset is called, say, rs1
set rs1 = nothing
Another thought.  Did you try to import everything into a new database, comile then run it?  May be some garbage left behind.
Hy, for this problem there is no solving!!! I have the same problem and the anser is, you only opens one form!!

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

The following things are considered tables by Access (thank you Microsoft).

1. Tables - obviously
2. Open recordsets
3. Combo boxes
4. List boxes

The combo that usually gets people is a combination of 2 and 3.  If you have a form that has lots of combo boxes on it, every single one of them is a table.  Get a couple of instances of that form open and you'll quickly find yourself in the Error 3014 horror shop.

If you haven't seen this particular article, it may shed more light.


Also note, that even with an updated version of Jet you are still limited to 2048 "tables" (these are really Jet Table References, and as I said above, Jet considers a lot of things to be tables).

MADamianoAuthor Commented:
Thanks for the advice, but I don't think that will totally do it.

I had already been to the url that Jzal recommended and even though the jet on the xp machine that i am using was using jet 4.0, I downloaded jet 3.5 and the patch provided.

The application does not have any forms (other than one "Run" button) so open combo or list boxes is not the problem.

I judiciously close all record sets that I open.

The application produces about 100 PDF files, which contain from 1 to 6 individual reports.

I am using a third party add in from ACG Soft which generates temporary pdf files for each report and then combines the individual pdf's into one pdf and then deletes the temporary pdf's. If the application finished (which it does using Access 2000 on a Win NT machine) the final number of pdf's is about 100.

It appears that i can get through about 45 or so of these combined pdf's when i get the error.

The error occurs using an xp machine, and Access 2002, both in Access 2000 file format and Access 2002 format.

I suspect that either the ACG Soft add in (an MDA file), is opening something without closing it, or i have a problem with the version of the jet that i am using.

Thanks so much for the advice, with this added information; maybe we can get closer to a solution?


With the additional information, I am going to have to back out.  What you're doing is far different from anything I have used Access for, although I suspect you may be right, and that it is something in the add-in not behaving correctly.

Hey Michael,
  I bet in the MDA, there is an issue of table objects being left open.  Looks like you may either have to create code to do what that MDA does on your own (or get some support from them)
I would re-install Jet 4.0 as it is substatially better than 3.5

Oddly, you state that it is ok on an NT box.  Is there a memory difference? (more in the NT box)  <--grasping at straws...
MADamianoAuthor Commented:
I found the area of the code that caused the problem (formatting reports) so I did a work around. I have been thus far un impressed with Access 2002 even when using Access 2000 file format.
Want to post the code that's causing the problem?  Maybe we can resolve it.
MADamianoAuthor Commented:
Here is the Formating code that I was using. The application produced over 300 reports. It bombed at about 144 saying that there were "too many tables open".

When I eliminated this code, it worked fine.

I was using the formating code because the reports contained various layouts and as we all know, Access can sometimes loose the set up for a particular report.

Any help would be greatly appreciated.

Thank you,



Option Compare Database
Option Explicit

Public MyPaperSize As Integer
Public MyTopMargin As Integer
Public MyLeftMargin As Integer
Public MyBottomMargin As Integer
Public MyRightMargin As Integer
Public MyOrientation As Integer
Public ReportName As String
Public MyPDF As String
Public MyAm As String
Public MyWhere As String
Public MyLevel As String
Public MyOCM As String

Public bGroup As Boolean

Public Type str_PRTMIP
    strRGB As String * 28
End Type

Public Type type_PRTMIP
    xLeftMargin As Long
    yTopMargin As Long
    xRightMargin As Long
    yBotMargin As Long
    fDataOnly As Long
    xWidth As Long
    yHeight As Long
    fDefaultSize As Long
    cxColumns As Long
    yColumnSpacing As Long
    xRowSpacing As Long
    rItemLayout As Long
    fFastPrint As Long
    fDatasheet As Long
End Type

Public Type str_DEVMODE
    RGB As String * 94
End Type

Public Type type_DEVMODE
    strDeviceName As String * 16
    intSpecVersion As Integer
    intDriverVersion As Integer
    intSize As Integer
    intDriverExtra As Integer
    lngFields As Long
    intOrientation As Integer
    intPaperSize As Integer
    intPaperLength As Integer
    intPaperWidth As Integer
    intScale As Integer
    intCopies As Integer
    intDefaultSource As Integer
    intPrintQuality As Integer
    intColor As Integer
    intDuplex As Integer
    intResolution As Integer
    intTTOption As Integer
    intCollate As Integer
    strFormName As String * 16
    lngPad As Long
    lngBits As Long
    lngPW As Long
    lngPH As Long
    lngDFI As Long
    lngDFr As Long
End Type

Public Type type_MarginInfo
lngLeft As Long
lngTop As Long
lngRight As Long
lngBottom As Long
lngDefaultSize As Long
End Type

Public Sub subFormatReport()

'This section format's the report
Dim DM As type_DEVMODE
Dim DevString As str_DEVMODE
Dim strDevModeExtra As String
Dim rpt As Report

Dim PrtMipString As str_PRTMIP
Dim PM As type_PRTMIP
DoCmd.OpenReport ReportName, acViewDesign
Set rpt = Reports(ReportName)
If Not IsNull(rpt.PrtDevMode) Then
    'Set the page print parameters
    strDevModeExtra = rpt.PrtDevMode
    DevString.RGB = strDevModeExtra
    LSet DM = DevString
    DM.intOrientation = MyOrientation  ' 1 = Potrait / 2 = Landscape
    DM.intPaperSize = MyPaperSize    '  5 = Legal / 1 = Letter
    DM.intCopies = 1    'Number of Copies
    LSet DevString = DM                     ' Update property.
    Mid(strDevModeExtra, 1, 94) = DevString.RGB
    rpt.PrtDevMode = strDevModeExtra
    'Set the page margins
    PrtMipString.strRGB = rpt.PrtMip
    LSet PM = PrtMipString
    PM.xLeftMargin = 1 * MyLeftMargin ' Set margins.
    PM.yTopMargin = 1 * MyTopMargin
    PM.xRightMargin = 1 * MyRightMargin
    PM.yBotMargin = 1 * MyBottomMargin
    LSet PrtMipString = PM            ' Update property.
    rpt.PrtMip = PrtMipString.strRGB
    'If MyTopComp = True And MySortField <> " " Then
    '    rpt.OrderBy = MySortField
    '    rpt.OrderByOn = True
    'End If
End If
DoCmd.Close acReport, ReportName, acSaveYes
End Sub
Thoughts for the day:
I studied the code.  Only thought I had was that the code might be outrunning the save of the report design, could be a issue.  You might want to try a pause right after the 'DoCmd.Close acReport, ReportName, acSaveYes'.  Try a .5 second pause there.  
Remember, even though the error message states 'too many tables open', Acccess sees a table as a object.  It also sees a report as a object and therefore what the error is really saying is 'too many reports open'.  What if you allow this code to run only one time for the session.  Surely the report does not need to be reformatted 300 times.

I have never really had a problem with a report loosing formatting.  Whenever I have had a problem it was due to using a specific printer that another user did not have.  Therefore, I always use a default printer when possible.

Bottom line, though, if it runs fine withoput it, then you do not need it and it is being redundant.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area:
Accept 1William 's answer
Please leave any comments here within the next seven days.
EE Cleanup Volunteer
I am getting the same error from a C++ / MFC program. I have added explict Close() calls to close most record sets, even though Microsoft's CDAORecordSet's destructor does call close.
I added a static counter to track the highwater mark of open record sets. It peaks at only a few hundred record sets (I'm not tracking tables). I'm not using forms or combos. I am using Query Defs.
It fails at different places depending to what activity has been done.

Trevor S.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now