?
Solved

Error 3014 too many tables open

Posted on 2003-03-18
13
Medium Priority
?
620 Views
Last Modified: 2007-12-19
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,
Michael
 
0
Comment
Question by:MADamiano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +3
13 Comments
 
LVL 18

Expert Comment

by:1William
ID: 8160973
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
rs1.close
set rs1 = nothing
0
 
LVL 18

Expert Comment

by:1William
ID: 8161137
Another thought.  Did you try to import everything into a new database, comile then run it?  May be some garbage left behind.
0
 

Expert Comment

by:creck
ID: 8166761
Hy, for this problem there is no solving!!! I have the same problem and the anser is, you only opens one form!!

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:Jzaltheral
ID: 8170203
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.

http://www.mvps.org/access/bugs/bugs0010.htm

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).

Jzal
0
 
LVL 1

Author Comment

by:MADamiano
ID: 8173817
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?

Michael

 
0
 
LVL 2

Expert Comment

by:Jzaltheral
ID: 8176661
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.

Jzal
0
 
LVL 18

Expert Comment

by:1William
ID: 8176697
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...
0
 
LVL 1

Author Comment

by:MADamiano
ID: 8546531
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.
0
 
LVL 18

Expert Comment

by:1William
ID: 8549738
Want to post the code that's causing the problem?  Maybe we can resolve it.
0
 
LVL 1

Author Comment

by:MADamiano
ID: 8549909
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,

Michael

*********************************************************************************************



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
0
 
LVL 18

Accepted Solution

by:
1William earned 1500 total points
ID: 8550135
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.
0
 
LVL 2

Expert Comment

by:HobsonT
ID: 8737202
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.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Trevor
EE Cleanup Volunteer
0
 

Expert Comment

by:TrevorBStokes
ID: 9607225
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question