Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

Can't open more tables

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
jacksonmacd
Asked:
jacksonmacd
  • 4
  • 3
  • 3
  • +3
1 Solution
 
dds110Commented:
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
 
jacksonmacdAuthor Commented:
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
 
PsychoDazeyCommented:
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
Technology Partners: 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!

 
jacksonmacdAuthor Commented:
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
 
PsychoDazeyCommented:
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
 
RKohlerCommented:
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
 
PaurthsCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
jacksonmacdAuthor Commented:
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
 
PaurthsCommented:
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
 
PsychoDazeyCommented:
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
 
jacksonmacdAuthor Commented:
see previous comment about splitting points
0
 
dds110Commented:
0
 
PaurthsCommented:
<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

Industry Leaders: 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!

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