Solved

Can't open more tables

Posted on 2001-07-05
14
362 Views
Last Modified: 2008-02-07
Running into a memory problem with my Acc97 application running on Win98 workstations. The application is split frontend and backend, with two versions of the backend 1) on a Novell Netware 4.11 server, and 2) on a Win98 machine.

On some of my more complex forms, I get an error message "Can't open any more tables". What is the limit on the number of tables that can be opened? Can that limit be managed? The forms have several combo boxes for field lookups.

The forms where this error occurs have multiple Tabs and parts -- I have tried to pare them down to their essentials, but the problem persists. The solution is probably to make several, less complex forms. I can accept that...

HOWEVER, the real problem lies with one workstation -- instead of the ".. too many tables.." message, it generates errors about "..the mSysTombstone table cannot be opened because it is already in use". Sometimes the message is about "MSysTableGuids". What are these messages all about? Are they just bogus messages that are actually warning about too many tables?

The problematic Win98 machine has 64 MB of memory. The application runs OK on another Win98 machine with 32 MB of memory.

The error messages are somewhat different depending on which backend I link to. With Win98 as the backend, it always generates the "Tombstone" message. With the Netware backend, it alternates between the "too many tables" and the "Tombstone" message.

What's the solution to eliminating the Tombstone message?

(PS. I know that Win98 is not a viable backend. This is a temporary solution, and the backend will likely be migrated to Win2000 Server.)
0
Comment
Question by:jacksonmacd
  • 4
  • 3
  • 3
  • +3
14 Comments
 
LVL 8

Expert Comment

by:dds110
ID: 6256777
I'm assuming that the Tombstone table is something you created.  Your description sounds like there are too many instances of the same tables being opened through recordsets and then not being closed when done.  Make sure that you close whatever tables you open programatically i.e.

MyRst.Close
Set MyRst = Nothing

I don't know if I'm on the right track for your particular problem, but I used to get the same errors and this fixed it.

HTH
0
 

Author Comment

by:jacksonmacd
ID: 6256827
The mSysTombstone table is created and maintained by Access as part of the Replication process. Likewise with the MSysTableGuids.

I will have a look at recordsets to see if there are any that I have left open.

0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6257108
I agree with dds, definitely check the rst thing.  Also - are you setting the recordsource properties of combo-boxes to certain tables on the forms?  You should use sql statements for these if that is the case.
0
 

Author Comment

by:jacksonmacd
ID: 6257192
PsychoDazey

The combo boxes have a mixture of recordsources. In the simplest form that still causes trouble, all of the combos are tables or queries. In a different form, some combos are built on static SELECT statements. Can't recall any of the combos that use a built-on-the-fly SQL statement.

I have always been of the understanding that precompiled queries provided the best performance for combo boxes. Are you saying otherwise? Does one format provide better/less memory footprint than another?

I have gone thru the forms -- mostly they had rst.close whenever a recordset was used. Occasionally had no cleanup code. Changed them all to rst.close and set rst = Nothing. It may be marginally better, but not cured.

0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6257229
I am just wondering if you have it set to tables, it is forced to look at that entire table.  If you have something Like 'Me![Field].RecordSource = "Select table.[Field] From Table"' I am under the impression that it is more efficient than using the query builder in access and setting the recordsource = to QueryName.
As far as the other table errors, you may have a problem with different dll's on the 64 meg 98 machine.
0
 

Accepted Solution

by:
RKohler earned 150 total points
ID: 6274563
I had the same error message on a very complex form that had over 30 subforms. Normally, I would never have this many subforms, but Access is very weak with respect to array handling on a form so I had no choice.
I tried all the above suggestions to no avail.
I ended up getting it to work by accident. I realized the app would work on one machine and not another. Comparing  the ODBC drivers I found a difference. Updating the drivers was a cure. Needless to say, you can't always believe the error Access to displays!

Hope that helps.
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6274830
hi RKohler,

i see u are fairly new to EE, so i would like to welcome u.
I would like to add this: in the Access Topic Area it is an unwritten rule that we experts only place comments. Submitting an answer will lock the Q and thus resulting in less experts visiting the Q. Again resulting in less help for the questioner.
Since it is an unwritten rule, u could not be aware of that.

I hope however u understand and appreciate it.

So, welcome again, i hope u can help alot of people in this great community.

cheers
Ricky
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 57
ID: 6278315
The limit is 2048 table references at one time.  That is not 2048 tables, but references to tables.

The reason for the error different error messages is that your running out of locks under Novell.

Use the dbEngine.SetOption dbMaxLocksPerFile setting to set a maximum number of locks that is less then what your allowing under Novell.  You can check your autoexec.ncf for the current settings.  Default for most is 10,000 I believe.

HTH,
Jim.
0
 

Author Comment

by:jacksonmacd
ID: 6281743
To the four who answered:

Tried all the suggestions, but nothing worked definitively. My solution was to replace the Win98 computer with another box -- that made the program work.

In a bit of a quandry about what to do for points. Looked up how to split between accounts, but did not find anything clear. Anyone object if i just award the points to RKohler just to close the question?
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6281753
well, i did not participate, but to award the points to RKohler just to close the Q would be a wrong thing to do.
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6285818
I believe if you get in touch with EE they can have you award the points to them and close the Q, then refund the points.  If you feel that more than 1 person helped, pose one or more Q's such as "points for XYZ".
0
 

Author Comment

by:jacksonmacd
ID: 6310111
see previous comment about splitting points
0
 
LVL 8

Expert Comment

by:dds110
ID: 6310785
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6311210
<see previous comment about splitting points>

?

u have awarded a non-solution. In fact RKohler has said nothing that could helped u ...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now