Fixing Relationship on back-end DB

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.

Re: Fixing Relationship on back-end DB

On Oct 31, 7:30 am, "paii, Ron" 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?

Re: Fixing Relationship on back-end DB

On Oct 31, 8:11 am, OldPro wrote:
> On Oct 31, 7:30 am, "paii, Ron" 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...

Re: Fixing Relationship on back-end DB

OldPro wrote:
> On Oct 31, 8:11 am, OldPro wrote:
>
>>On Oct 31, 7:30 am, "paii, Ron" 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.

Re: Fixing Relationship on back-end DB

"OldPro" wrote in message
news:1193836779.223545.72210@o80g2000hse.googlegroups.com...
> On Oct 31, 8:11 am, OldPro wrote:
> > On Oct 31, 7:30 am, "paii, Ron" 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...
>

Thank you for your reply.

Some tables need normalization but the problem is that each time I setup a
relationship with referral integrity between a parent table such as the
Inventory control table and a detail table like the Purchase order detail;
Access builds an index that does not show in the table's index list. For
example my inventory table has 12 indexes including the primary key, other
fields, include control codes, reference to manufacture part numbers, part
description ect. I have more then 12 detail tables referencing the part
number. Some of these tables could be combined, but not without
reprogramming the front-end which I was hoping to avoid until the
relationships were fixed.

Is there some way to get Access 97 to use the existing primary key index
instead of creating a new one for each relationship?

Re: Fixing Relationship on back-end DB

OldPro wrote:

>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...

Not at all. I've hit that limit quite a number of times. If anything the database
is well normalized.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Re: Fixing Relationship on back-end DB

OldPro wrote:

>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...

BTW as an example of such a table see the screen shot at
http://www.granite.ab.ca/access/if_an_index_is_a_duplicate_o f.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Re: Fixing Relationship on back-end DB

"paii, Ron" 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.

Excellent.

>then
>create relationships with referral integrity. I was surprised by how few
>orphaned records existed :).

That's a good sign.

>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? No.

>Or will I need to
>restructure the database to reduce the number of child tables referencing
>parent tables.

No, simply because that's not proper design. Now Access does create duplicate
indexes and hidden indexes. And some indexes are super hidden that we can't even get
to programmatically.

I do have a tool which will help identify those duplicate indexes. Email me at tony
at granite.ab.ca and I'll email you a zipped copy.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Re: Fixing Relationship on back-end DB

On Oct 31, 11:13 pm, "Tony Toews [MVP]"
wrote:
> OldPro wrote:
> >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...
>
> BTW as an example of such a table see the screen shot athttp://www.granite.ab.ca/access/if_an_index_is_a_duplicate _of.htm
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Amazing. I never would have guessed that someone could fit that many
sardines in one can. Perhaps it's time for a bigger can... Aren't
there costly and complex solutions to this problem? ;-)

Re: Fixing Relationship on back-end DB

OldPro wrote:

>Amazing. I never would have guessed that someone could fit that many
>sardines in one can. Perhaps it's time for a bigger can... Aren't
>there costly and complex solutions to this problem? ;-)

Sure, and I was about half way through upsizing it. But an idiot of a manager who I
disagreed with (he was later escorted out by security guards) and the corporiate IT
manager, also a lesser idiot, cancelled it when I was half way through. A year later
I decided I'd had enough so I fired hte client.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Re: Fixing Relationship on back-end DB

"OldPro" wrote in message
news:1193836779.223545.72210@o80g2000hse.googlegroups.com...

> 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...

Or you may want to get rid of redundant indexes. I haven't
been in the Access scene for near a decade, but old versions
used to automatically index things that you sometimes didn't
want it to (things with like "ID", "Code", "Key") in their
field names. It would also automatically create new indexes
for your foreign key relationships -- even if there was already
one available. If you didn't watch it closely, you'd end up
with 3 individual indexes (when you thought you had one), all
exactly the same:

(1) The one you wanted
(2) the one Access automagically added 'cause your field
has "ID" in the name, and
(3) the one it automagically created
in support of the foreign key/referrential integrity.

Does Access still do this or am I just showing my age?

Re: Fixing Relationship on back-end DB

"Sgt.Sausage" wrote:

>Does Access still do this or am I just showing my age?

Access still does this. (Although I'm not sure about A2007.)

The problem is that some of those indexes are hidden from the screen view. And
others are hidden even from VBA. My tool exposes all that in a GUI format.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Re: Fixing Relationship on back-end DB

"Tony Toews [MVP]" wrote in message
news:sskii3p68ulgp9qf73bjm1qrt869kh7t15@4ax.com...
> "paii, Ron" 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.
>
> Excellent.
>
> >then
> >create relationships with referral integrity. I was surprised by how few
> >orphaned records existed :).
>
> That's a good sign.
>
> >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? No.
>
> >Or will I need to
> >restructure the database to reduce the number of child tables referencing
> >parent tables.
>
> No, simply because that's not proper design. Now Access does create
duplicate
> indexes and hidden indexes. And some indexes are super hidden that we
can't even get
> to programmatically.
>
> I do have a tool which will help identify those duplicate indexes. Email
me at tony
> at granite.ab.ca and I'll email you a zipped copy.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm?wp_ml=0
> Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/?wp_ml=0

You can email that tool to ron at packairinc dot com. Thank you for the
offer.
I did send you an email, did you receive it? I was have email problems on
that day.

Re: Fixing Relationship on back-end DB

"paii, Ron" wrote:

>You can email that tool to ron at packairinc dot com. Thank you for the
>offer.
>I did send you an email, did you receive it? I was have email problems on
>that day.

Yes, I did get your email. I haven't responded as I need to spend an hour or two
with the utility ensuring it is fit for human consumption.

Tony.

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/