Table and Relationship Limitations

[Guest]

I have a table in my database that has 42 fields and is
connected to 10 different tables through 1 to many
relationships. This is just a part of the system too.
Have a gone past the limits of access. I am having
problems building the interfaces to use the system without
breaking them down into little mini interfaces.

 

[Rick B]

Does not sound like you are anywhere close to the limits of Access. Most
big databases have 20 or 30 tables (or more) with many relationships.

What specifric problems are you having?

Rick B


I have a table in my database that has 42 fields and is
connected to 10 different tables through 1 to many
relationships. This is just a part of the system too.
Have a gone past the limits of access. I am having
problems building the interfaces to use the system without
breaking them down into little mini interfaces.

 

[Guest]

The first problem I had was when I went to build the
largest form I needed to input information. After I wrote
the query to link the tables and pull the records to the
form, it wouldn't let me leave design view. I kept
getting a message that the form could not be completed.
After I broke the form down into smaller forms, I tried
putting in the sub-forms for the smaller tables I
connected and I kept getting an error that said "the links
you connected can't be used. this link was created based
on relationships between source tables. To use this link
use must add the missing links to the form or the report's
record sources." The thing is the subform works when I
remove all of the other tables from the form query. After
I add a few tables this message keeps coming up.

 

[John Vinson]

I have a table in my database that has 42 fields and is
connected to 10 different tables through 1 to many
relationships. This is just a part of the system too.
Have a gone past the limits of access. I am having
problems building the interfaces to use the system without
breaking them down into little mini interfaces.

Not at all. 42 fields is a very wide table, but you're allowed up to
an absurdly wide 255; and you can have up to 32 relationships to any
single table. What specific interface problems are you having? Have
you Compacted the database?

 

[John Vinson]

To use this link
use must add the missing links to the form or the report's
record sources." The thing is the subform works when I
remove all of the other tables from the form query

The Form should be based on the main table itself - NOT on a query
joining all the tables. Each Subform would be based on one of the
"many" side tables, using its linking field as the Master/Child Link
Field. It sounds like you're trying to do the job twice, including all
the tables in the main form's query and also using subforms.