Limit to number of tables in a form?

[JustMe]

Hi All - I hope you all can help me. I have a DB that I created in
office 2000 that has about 35 tables (not all are full...I just thought
it would be easier to maintain them that way). When I tried to create a
form using all of the tables, Access won't let me use all the tables.
Is there therefor a limit to the number of tables a form can use? How
can I get around this (I would rather avoid having to merge tables)? My
ultimate goal is to have a form that uses tabs to navigate through the
different content areas.

Many thanks in advance for your help.

 

[Pavel Romashkin]

I am not sure what you mean by "use all tables in a form". You can
certainly use the fields from them all by basing the form on a select
query that will put all the fields from the tables into a single entity.

Pavel

 

[JustMe]

Well when I tried to use the Form wizard and selected all the fields
from all the tables, I get a Subscript Out of Range error. What does
that mean? Do I have too many fields? Too many tables?

thanks

 

[Pavel Romashkin]

Try doing it differently. Start with the query wizard and use the tables
you need data from for this particular form, with their proper
relationships, to create one query that will show the fields you need
for the form. Save the query. Now, in the form wizard, you can choose
that query as the form's record source, and it will work.

Pavel

 

[JustMe]

Tried that just now and still got subscript out of range. Argh. FYI,
there are about 250 to 300 fields...is that just too much for access to
handle? Is the best thing perhaps to create subforms?

 

[John Vinson]

Hi All - I hope you all can help me. I have a DB that I created in
office 2000 that has about 35 tables (not all are full...I just thought
it would be easier to maintain them that way). When I tried to create a
form using all of the tables, Access won't let me use all the tables.
Is there therefor a limit to the number of tables a form can use? How
can I get around this (I would rather avoid having to merge tables)? My
ultimate goal is to have a form that uses tabs to navigate through the
different content areas.

Many thanks in advance for your help.

WHOA. Stop right where you are.

A 35-table database is not all that unusual - but I suspect NOT in the
way you're doing it! You should certainly *not* try to create one
monstrous form with 35 tables linked to it; it certainly would not be
updateable, for one thing!

You're using a relational database. Use it relationally! Each type of
"entity" - real-life person, thing or event - of importance to your
system should have its own table (only *one* table). Storing the same
kind of information in 35 tables instead of one makes it vastly HARDER
to maintain, not easier!

What are these tables? Are they all of the same structure? If not, how
are they related?

 

[John Vinson]

They are all part of one data entry form. There are 35 separate
sections to the form (each with anywhere from 5 to 20 fields), and I
figured the best thing to do was to have each section be in its own
table. Turns out of course that Access has a limit of about 32 tables.
So what I did was merged as many related tables as possible. The form
now works by having each table be a subform in one master form.

I'm still VERY dubious about your data structure. It really sounds
like you have - logically, even if it's split up into 35 tables - a
single wide-flat record, where every "record" has an entry in each of
these 35 tables, all related one to one.

This is almost CERTAINLY an improperly normalized database. Sticking
with this design will *guarantee* that you'll have endless problems
like this. Normalize your table structure first; you'll find that the
forms will be MUCH easier.

The thing is though that now the users would like a "Front Page" where
they can check off the components they want to use, thus making the
pieces they don't want to use invisible. Any idea how I do this?

Use a Switchboard form (using the forms wizard, or design your own)
that selectively opens just the desired forms, one at a time.