how do you programmatically create a relationship WITHOUT creating an index?

Hi gang,

Please help. I've been through the Access help, searched the Web, and
I can't seem to get a straight answer. As the Subject line suggests, I
want to run a fairly simple VB/Access Sub Function/Module that creates
relationships for my tables. The problem is that I need to provide for
some tables that may have > 32 relationships (which is apparently the
limit on Indexes that Access can support). How can I prevent Access
from automatically creating these indexes, AND still get referential
integrity, as viewable in my Relationships window. I started with
this, which works fine until the 32 index-limit is reached:

strSQL = "ALTER TABLE Movement_Authority ADD CONSTRAINT [FKp_115]
FOREIGN KEY ([FKp_Limit]) REFERENCES Limit;
DoCmd.RunSQL strSQL

Then I read that this would work:

strSQL = "ALTER TABLE Movement_Authority ADD CONSTRAINT NO INDEX
[FKp_115] FOREIGN KEY ([FKp_Limit]) REFERENCES Limit;
DoCmd.RunSQL strSQL

I tried the "NO INDEX" key phrase everywhere imaginable, as well as
with "As Database" using .Execute, still to no avail. Using DROP
obliterates the referential integrity altogether. I sincerely hope
SOMEONE knows what I can do, and hopefully it won't require a page of
code and importing or using all sorts of esoteric do-dads...what the
heck, I'll take any solution. HELP!!! Feel free to post and/or email
me directly. Thanks in advance, -Brian sales@leapse.com

re: how do you programmatically create a relationship WITHOUT creating an index?

Hi Brian.

If we are talking about JET here (i.e. if your tables are in an Access
database), you will not be able to do that. JET must have a unique index in
order to create a relationship with RI.

Your table may have some spurious indexes that you can dump. By default,
Access creates an index on any field that has a name ending in ID, Code,
Num, etc. (To avoid that: Tools | Options | Tables/Queries | Autoindex.)
These indexes are listed in the Indexes dialog (Tools menu) in table design,
and you can delete them there. Additionally Access creates hidden indexes on
all the foreign key fields that are involved in relationships with RI, so if
you manually index your foreign keys as well, you can remove these
duplicates as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian S. Smith" <sales@leapse.com> wrote in message
news:4ad0f901.0406081531.29482ef7@posting.google.c om...[color=blue]
>
> Please help. I've been through the Access help, searched the Web, and
> I can't seem to get a straight answer. As the Subject line suggests, I
> want to run a fairly simple VB/Access Sub Function/Module that creates
> relationships for my tables. The problem is that I need to provide for
> some tables that may have > 32 relationships (which is apparently the
> limit on Indexes that Access can support). How can I prevent Access
> from automatically creating these indexes, AND still get referential
> integrity, as viewable in my Relationships window. I started with
> this, which works fine until the 32 index-limit is reached:
>
> strSQL = "ALTER TABLE Movement_Authority ADD CONSTRAINT [FKp_115]
> FOREIGN KEY ([FKp_Limit]) REFERENCES Limit;
> DoCmd.RunSQL strSQL
>
> Then I read that this would work:
>
> strSQL = "ALTER TABLE Movement_Authority ADD CONSTRAINT NO INDEX
> [FKp_115] FOREIGN KEY ([FKp_Limit]) REFERENCES Limit;
> DoCmd.RunSQL strSQL
>
> I tried the "NO INDEX" key phrase everywhere imaginable, as well as
> with "As Database" using .Execute, still to no avail. Using DROP
> obliterates the referential integrity altogether. I sincerely hope
> SOMEONE knows what I can do, and hopefully it won't require a page of
> code and importing or using all sorts of esoteric do-dads...what the
> heck, I'll take any solution. HELP!!! Feel free to post and/or email
> me directly. Thanks in advance, -Brian sales@leapse.com[/color]

re: how do you programmatically create a relationship WITHOUT creating an index?

Perhaps you could tell us a little more about your database design.
The need for over 32 relationships for a single table suggests that perhaps
a different table structure could be appropriate.

HTH
- Turtle

"Brian S. Smith" <sales@leapse.com> wrote in message
news:4ad0f901.0406081531.29482ef7@posting.google.c om...[color=blue]
> Hi gang,
>
> Please help. I've been through the Access help, searched the Web, and
> I can't seem to get a straight answer. As the Subject line suggests, I
> want to run a fairly simple VB/Access Sub Function/Module that creates
> relationships for my tables. The problem is that I need to provide for
> some tables that may have > 32 relationships (which is apparently the
> limit on Indexes that Access can support). How can I prevent Access
> from automatically creating these indexes, AND still get referential
> integrity, as viewable in my Relationships window. I started with
> this, which works fine until the 32 index-limit is reached:
>
> strSQL = "ALTER TABLE Movement_Authority ADD CONSTRAINT [FKp_115]
> FOREIGN KEY ([FKp_Limit]) REFERENCES Limit;
> DoCmd.RunSQL strSQL
>
> Then I read that this would work:
>
> strSQL = "ALTER TABLE Movement_Authority ADD CONSTRAINT NO INDEX
> [FKp_115] FOREIGN KEY ([FKp_Limit]) REFERENCES Limit;
> DoCmd.RunSQL strSQL
>
> I tried the "NO INDEX" key phrase everywhere imaginable, as well as
> with "As Database" using .Execute, still to no avail. Using DROP
> obliterates the referential integrity altogether. I sincerely hope
> SOMEONE knows what I can do, and hopefully it won't require a page of
> code and importing or using all sorts of esoteric do-dads...what the
> heck, I'll take any solution. HELP!!! Feel free to post and/or email
> me directly. Thanks in advance, -Brian sales@leapse.com[/color]

re: how do you programmatically create a relationship WITHOUT creating an index?

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:40c6607a$0$29791$5a62ac22@freenews.iinet.net. au...[color=blue]
> Hi Brian.
>
> If we are talking about JET here (i.e. if your tables are in an Access
> database), you will not be able to do that. JET must have a unique index[/color]
in[color=blue]
> order to create a relationship with RI.
>
> Your table may have some spurious indexes that you can dump. By default,
> Access creates an index on any field that has a name ending in ID, Code,
> Num, etc. (To avoid that: Tools | Options | Tables/Queries | Autoindex.)[/color]


I don't think this behaviour applies when you create constraints using DDL.

re: how do you programmatically create a relationship WITHOUT creating an index?

"Brian S. Smith" <sales@leapse.com> wrote in message
news:4ad0f901.0406081531.29482ef7@posting.google.c om...[color=blue]
> Hi gang,
>
> Please help. I've been through the Access help, searched the Web, and
> I can't seem to get a straight answer. As the Subject line suggests, I
> want to run a fairly simple VB/Access Sub Function/Module that creates
> relationships for my tables. The problem is that I need to provide for
> some tables that may have > 32 relationships (which is apparently the
> limit on Indexes that Access can support). How can I prevent Access
> from automatically creating these indexes, AND still get referential
> integrity, as viewable in my Relationships window. I started with
> this, which works fine until the 32 index-limit is reached:[/color]

I think that Access does not create an index when you define a constraint
using DDL. Perhaps constraints themselves count towards the 32 index limit.

BTW, a table with 32 foreign keys often indicates a design problem, but you
may have a special case.

re: how do you programmatically create a relationship WITHOUT creating an index?

"John Winterbottom" <assaynet@hotmail.com> wrote:
[color=blue]
>BTW, a table with 32 foreign keys often indicates a design problem, but you
>may have a special case.[/color]

And I have one of those special cases. An MDB with 150 tables with at least 30 or
40 joins to the Job table. So it's unlikely but possible.

We also print the relationships diagram on a 36" plotter. It's about as tall as it's
wide. I can fit one more layer of tables, maybe two at the bottom before I run out
of room.

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

re: how do you programmatically create a relationship WITHOUT creating an index?

"Tony Toews" <ttoews@telusplanet.net> wrote in message
news:3toec0tg68s7oq1d717k6rs3ppdcmlimvt@4ax.com...[color=blue]
> "John Winterbottom" <assaynet@hotmail.com> wrote:
>[color=green]
> >BTW, a table with 32 foreign keys often indicates a design problem, but[/color][/color]
you[color=blue][color=green]
> >may have a special case.[/color]
>
> And I have one of those special cases. An MDB with 150 tables with at[/color]
least 30 or[color=blue]
> 40 joins to the Job table. So it's unlikely but possible.
>
> We also print the relationships diagram on a 36" plotter. It's about as[/color]
tall as it's[color=blue]
> wide. I can fit one more layer of tables, maybe two at the bottom before[/color]
I run out[color=blue]
> of room.
>[/color]


Oh, yes I know these cases do exist - that's why I add the proviso :) A
little more on this subject though - after some testing it seems that when
you create a FK constraint using a DDL script, an index is added to the
tables' DAO indexes collection, even though no index is visible through the
UI. Perhaps Jet creates in index to enforce the constraint. AFAIK this is
different behaviour to sql server, where a constraint and an index are two
separate things.

re: how do you programmatically create a relationship WITHOUT creating an index?

"John Winterbottom" <assaynet@hotmail.com> wrote:
[color=blue]
>Oh, yes I know these cases do exist - that's why I add the proviso :) A
>little more on this subject though - after some testing it seems that when
>you create a FK constraint using a DDL script, an index is added to the
>tables' DAO indexes collection, even though no index is visible through the
>UI. Perhaps Jet creates in index to enforce the constraint. AFAIK this is
>different behaviour to sql server, where a constraint and an index are two
>separate things.[/color]

Yup, Jet does the same thing if you create a relationship via the GUI and the DAO
relationships collection.

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

re: how do you programmatically create a relationship WITHOUT creating an index?

Thank you all for your prompt responses! This is a great group.

Sounds like I'm SOL. Just to clear things up, though, the table and
its relationships are what is given to me in another form in another
application, so I know it is not an optimal design--and it really
isn't intended to be; it's more of a conceptual model.

But John, you have given me a glimmer of hope with your comment about
DDL. As you know, Access automatically creates indexes when you
specify a foregin key constraint. Could you give me some guidance on
how I can avoid this via DDL? Here's the example of what I'm using
agian:

strSQL = "ALTER TABLE Movement_Authority ADD CONSTRAINT [FKp_115]
FOREIGN KEY ([FKp_Limit]) REFERENCES Limit;
DoCmd.RunSQL strSQL

What is the syntax I can use to create a relationship WITHOUT an
index???

Thanks a whole bunch.

-Brian


"John Winterbottom" <assaynet@hotmail.com> wrote in message news:<2incbbFp57nvU1@uni-berlin.de>...[color=blue]
> "Brian S. Smith" <sales@leapse.com> wrote in message
> news:4ad0f901.0406081531.29482ef7@posting.google.c om...[color=green]
> > Hi gang,
> >
> > Please help. I've been through the Access help, searched the Web, and
> > I can't seem to get a straight answer. As the Subject line suggests, I
> > want to run a fairly simple VB/Access Sub Function/Module that creates
> > relationships for my tables. The problem is that I need to provide for
> > some tables that may have > 32 relationships (which is apparently the
> > limit on Indexes that Access can support). How can I prevent Access
> > from automatically creating these indexes, AND still get referential
> > integrity, as viewable in my Relationships window. I started with
> > this, which works fine until the 32 index-limit is reached:[/color]
>
> I think that Access does not create an index when you define a constraint
> using DDL. Perhaps constraints themselves count towards the 32 index limit.
>
> BTW, a table with 32 foreign keys often indicates a design problem, but you
> may have a special case.[/color]