Re: Fixing Relationship on back-end DB

OldPro wrote:
On Oct 31, 8:11 am, OldPro <rrossk...@xxxxxxxxxxxxx> wrote:

On Oct 31, 7:30 am, "paii, Ron" <n...@xxxxxx> wrote:






I have an application that has been built and expanded for over 10 years. As
modules have been added to the front-end, new back-end DBs have been
created. For example the back-end DBs for Inventory, Purchase order, Work
Order all exist in separate MDBs. This of course eliminates the ability to
create relationships between each of the back-ends, so referral integrity
between the back-ends is maintained in the front-end forms.

Now I am trying to fix this error in design. My 1st step was to create a new
backend database and import all the tables from the other back-ends. then
create relationships with referral integrity. I was surprised by how few
orphaned records existed :). My problem, Access is reporting a limit on the
number of indexes as I turn on more referral integrity. Is there a
historical limit on indexes like controls on a form? Or will I need to
restructure the database to reduce the number of child tables referencing
parent tables.

I was hoping to combine the MDBs without making any major changes to the
table structure. When done, I want to test the existing front-end on the new
back-end.

I've never heard of a limit on indexes... how many do you have?- Hide quoted text -

- Show quoted text -


Okay, I'll answer my own question: a quick search reveals a 32 index
per table limit. If a table has that many indexes, then perhaps it
needs to be normalized...

I oftentimes will index fields on a table because I will be filtering on those fields in SQL statements. Having an index on those fields oftentimes will speed up returning the results.

Should one look at a table and say "There's too many fields...I'll make a bunch of 1-1 related tables to fix this"?

Does normalization include 1-1s? As far as I'm concerned, 1-1s would all make up 1 record.
.

Relevant Pages

  • Re: Fixing Relationship on back-end DB
    ... For example the back-end DBs for Inventory, Purchase order, Work ... create relationships between each of the back-ends, so referral integrity ... between the back-ends is maintained in the front-end forms. ...
    (comp.databases.ms-access)
  • Re: Fixing Relationship on back-end DB
    ... For example the back-end DBs for Inventory, Purchase order, Work ... create relationships between each of the back-ends, so referral integrity ... between the back-ends is maintained in the front-end forms. ...
    (comp.databases.ms-access)
  • Re: Fixing Relationship on back-end DB
    ... For example the back-end DBs for Inventory, Purchase order, Work ... between the back-ends is maintained in the front-end forms. ... Tony Toews, Microsoft Access MVP ...
    (comp.databases.ms-access)
  • Fixing Relationship on back-end DB
    ... For example the back-end DBs for Inventory, Purchase order, Work ... create relationships between each of the back-ends, so referral integrity ... between the back-ends is maintained in the front-end forms. ... historical limit on indexes like controls on a form? ...
    (comp.databases.ms-access)