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.)
jacksonmacdAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)President / OwnerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.