Solved

Can't open more tables

Posted on 2001-07-05
14
372 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
[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
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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
 
LVL 58
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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