Solved

Can't open more tables

Posted on 2001-07-05
14
359 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 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

11 Experts available now in Live!

Get 1:1 Help Now