Exceeds maximum index number

[RSGinCA]

And here's something I found on Expert's Exchange. Somebody was having a
similar problem: "Too many indexes". The following was the "accepted anser":

"Also, in a general manner, it is absolutely not necessary to setup all the
relationships. They are useful if you want to force relational integrity with
cascade updates & deletes, but in most cases they are not needed: In fact this
window should not be called 'relationships' but 'constraints'. In a DBMS the
relationships are generally implicit and the PK-FK constraints are defined only
where the integrity (cascade updates/deletes) must be ensured. In most cases
this integrity is ensured programmatically thru the forms so it is not
necessary to define the relations explicitly. Also these relationships are
contributing for slowing down the updates as Access will have to verify
everytime."


Basically, he's saying you don't need to set up those relationships. Your
program will still work. The only thing you might loose is the enforcement of
relational integrity by the system. If your programs are set up right you
might not have to worry about it.


Sounds like you can say to heck with those friggin relationships!

Rick

 

[Todd]

But now the question that started my inquiries
If I realize that Patient VIsit ID# was entered incorrectly after all the data has been entered, is it possible to change the ID once and have it cascade down to all the other data tables?

 

[RSGinCA]

It doesn't sound like it.... not automatically, unless you write a program to
do it.

It's getting late and maybe I'm not thinking straight, but here's a possible
hokey solution. Create 2 intermediate tables between your Visit table and your
40 Test tables. The 1st table would be "First20Tests" and the 2nd table would
be "Next20Tests". So your Visit table would only be related to 2 tables, and
each of the 2 intermediate tables would be related to 21 tables (20 test tables
and the Visit table). In this way you would avoid the apparent 32
index/relationship limit, and be able to take advantage of enforcing relational
integrity and cascading updates/deletes. That doesn't sound so bad to me now,
but maybe when I wake up in the morning it'll be a different story...

Rick

 

[Tim Ferguson]

(e-mail address removed) (RSGinCA) wrote in

Basically, he's saying you don't need to set up those relationships.
Your program will still work.

Ony if "working" means that having tests without patients is okay; if
having patients visiting non existent doctors is okay; if it's okay to
delete a category and leave a number of records orphaned. In short, if it's
okay to rubbish the original schema and end up with a bunch of unrelated
(sic) bits of random data.

In fact this
window should not be called 'relationships' but 'constraints'.

Coming from aan "Expert Exchange", this demonstrates an ignorance of
databases that is frankly breathtaking. It is like saying that Access
should be called "Microsoft Database Program". Of course a relationship is
a constraint. Anybody who does not know that does not yet understand the
most simple level of R theory. It is a comment that can only be made by
someone who has only ever used MS Access and has only ever read MS Access
manuals.

B Wishes



Tim F

 

[John Vinson]

I thought it was important to have all three active for maintaining data integrity.

If the Primary Key of the "one" side table is an Autonumber, you
should NOT check Cascade Updates - because you can't update an
Autonumber, it would never occur. In your situation I'd be really
leery of checking Cascade Deletes as well - it would be much too easy
to (say) delete a Visit record (or a Patient record...!) in error, and
irreversibly delete forty lab results. I'd say it would be better to
just give the user the error message "Unable to delete because there
were related records..."

Alas this will probably NOT help with the (in my opinion, very
misleading) "too many indexes" error. It is clear that I was mistaken,
and that the error isn't really due to too many indexes, but rather to
too many relationships - and I cannot for the life of me think of a
good solution (other than moving the entire application to SQL/Server
or Oracle).

 

[Jeff Boyce]

Todd

There's something nagging at the back of my mind about possible parallels
between what you are describing, and what Duane Hookum has developed.

Duane has a template-like Access application for use by folks developing
surveys. At issue is how to reflect multiple items/questions associated
together with a single survey/test.

Aren't you describing a similar situation? You may need to fill in the
blanks (?i.e., "questions") on any number of specific medical tests (i.e.,
"tests").

You might want to take a look at Duane's work -- it might stimulate an idea
for an alternate approach to your current design.

See http://www.rogersaccesslibrary.com/duanehookom/AtYourSurvey.zip

 

[TC]

Jumping in here (having only read a few posts of this thread):

Having read some answers in Expert's Exchange, I have to ask: What did they
exchanged their Access experts FOR?

The answer you quote, is a classic example. In Access, data integrity is NOT
"in most cases ensured programmatically thru the forms". For example, say
you have a foreign key field in a table. Access (really, Jet) has no way to
know that field is a foreign key, unless you define a relation on it. And,
the forms will not automatically ensure the integrity of that foreign key,
unless you make that relation enforced.

HTH,
TC

 

[RSGinCA]

See my response, interspersed below.

Hmmmm.... That's what I would describe as taking a quote out of context.

DBMSes have been around for quite a while, and for a long time none of them
provided any automated capability of enforcing referential integrity, and none
provided any automated capability for cascading updates or deletes. Plenty of
programs using those DBMSes worked without "having tests without patients", or
"having patients visiting non existent doctors" or "deleting a category and
leaving a number of records orphaned". And they worked without "rubish[ing]
the original schema and end[ing] up with a bunch of unrelated bits of random
data". Those programs worked because they were specifically coded to avoid
those problems. Of course, some programs screwed up.

Now here is the FULL quote:

And here's something I found on Expert's Exchange. Somebody was having a
similar problem: "Too many indexes". The following was the "accepted anser":

"Also, in a general manner, it is absolutely not necessary to setup all the
relationships. They are useful if you want to force relational integrity with
cascade updates & deletes, but in most cases they are not needed: In fact this
window should not be called 'relationships' but 'constraints'. In a DBMS the
relationships are generally implicit and the PK-FK constraints are defined only
where the integrity (cascade updates/deletes) must be ensured. In most cases
this integrity is ensured programmatically thru the forms so it is not
necessary to define the relations explicitly. Also these relationships are
contributing for slowing down the updates as Access will have to verify
everytime."

If you actually read the full message, it would have been clear that what I
meant by "working" had absolutely nothing

to do with things like "having tests without patients".

Coming from aan "Expert Exchange", this demonstrates an ignorance of
databases that is frankly breathtaking. It is like saying that Access
should be called "Microsoft Database Program". Of course a relationship is
a constraint. Anybody who does not know that does not yet understand the
most simple level of R theory. It is a comment that can only be made by
someone who has only ever used MS Access and has only ever read MS Access
manuals.

I wasn't the author of that quote, however it appears to me that what he was
saying was that the relationships exist whether or not you use that
"Ralationships" window to draw the little lines between the tables.

I don't think that I would consider a relationship to be a 'constraint'.
What's your definition of restraint? One dictionary says, "something which
limits the freedom of someone or something". A relationship is a reflection of
the connection between data. It's a statement of fact about the data. The
relationships exist regardless of whether or not that 'Relationships' window is
used. What the 'Relationships' window accomplishes is to put 'constraints' on
those relationships. The 'constraints' that are imposed are things like
referential integrity (you can't have a child without a father) and it allows
the automation of that constraint by specifying cascading deletes and updates.
In other words, it "limits the freedom" of what you can do. Please don't
suggest that I'm trying to say that that is a bad thing. I'm saying that those
are 'constraints', and that's why the author of the quote was saying "this
window should not be called 'relationships' but 'constraints".

The fact is that the original poster of this thread has designed a database
and, apparently because of the limitation of MS Access, he is being prevented
from implementing the definition of that database. I have no reason to believe
that there is anything wrong with that design. (Perhaps you can provide some
input.) In any event, a search with Google shows that he isn't the only person
to have faced this problem. It does not 'work'. Period. It would appear that
he can make it 'work' (and at the same time retain referential integrity) by
deleting those little lines in the 'Relationships' window, and specifically
coding his programs in such a way that referential integrity is retained. He's
got a problem... how would you solve it?

Rick

 

[John Vinson]

If I follow your advice and uncheck 'allow cascade delete' then is there code that will allow deletion of data from ALL tables for the particular PatientVisitIDnumber if it is truely desired? Similarly, is there code that will allow modification of a specific PatientVisitIDnumber for ALL tables if it was initially entered incorrectly? I guess such codes would take the place of the cascades?

Not easily. You'ld need to run multiple update or delete queries.

It may just be that the application is too complex to be implemented
in Access! You might want to try doing it in SQL/Server - which is
easier and cheaper than you might think; in the SQL folder on your
Access/Office CD there's a copy of "MSDE", which is... ta-DAA!... a
full implementation of SQL server, limited to five concurrent
"threads" and 2 GByte of data. It's possible that you could set up the
desired cascades in SQL even if you can't in JET.

 

[RSGinCA]

The answer you quote, is a classic example. In Access, data integrity is NOT

"in most cases ensured programmatically thru the forms". For example, say
you have a foreign key field in a table. Access (really, Jet) has no way to
know that field is a foreign key, unless you define a relation on it. And,
the forms will not automatically ensure the integrity of that foreign key,
unless you make that relation enforced.

I don't know if what the author I quoted said, that in "most cases" data
integrity is "ensured programmatically thru the forms" is correct or not. I
know that issues like referential integrity and cascading updates/deletes are
irrelevant in most forms I deal with. This might be because there simply are
no deletes, or there are no changes to key fields. Or, because of the design
of the forms there can be no deletes of parent records while child records
still exist. Or, the programming of the form deals with the problem by
specifically deleting any child records when a parent is deleted.

You said, "And, the forms will not automatically ensure the integrity of that
foreign key, unless you make that relation enforced." The quote refered to
data integrety being enforced "programatically", NOT "automatically" by the
forms via Access/Jet.

In any event, frankly, I don't know and I don't care whether what he said was
totally correct or not. The fact is that the original poster has a problem
that is apparently caused by the limitations of MS Access. MS Access is not
allowing him to define his database. It is apparently related to the numerous
relationships (the little lines) defined in the 'Relationships' window. He
needs to resolve that problem.

One way of resolving the problem, and allowing him to define and implement his
database, is to get rid of those "little lines". If his forms are designed and
coded correctly they will not corrupt his data.

There is a problem that needs to be solved. How would you solve it?

Rick

 

[Tim Ferguson]

So, we have created quite a thread here, but no answer to my original
question. Any ideas out there?

It's an Access/ Jet bug. It seems that the engine is redundantly creating a
new index on the same field for every FK that references it, so that the
limit is reached when there are 31 related tables. See the code below, if
you want a demonstration. This seems to be the barest-bones approach I can
think of, but if anyone knows how to get around it I'd be interested too.

Solutions (1) - rethink the design. There is no theoretical reason not to
have a large number of tables related to one, but it's pretty uncommon and
I can't think of many legitimate cases. In your case, grouping the test
results into a small number of type/ subtype sets might help, or even
rethinking the function of the data.

Solutions (2) - moving up to a real database that does not have the
limitations of Jet. As John says, you already have MSDE although I haven't
tested this particular problem there.

Solutions (3) - complain to Microsoft and ask them to fix it. Hmmm

B Wishes


Tim F

--

by the way, for anyone who wants to experiment or test it:

Option Compare Database
Option Explicit

Public Sub TestIt()
Dim n As Integer

' make the main table
MainTable

' now make 50 child tables related to it
' this fails to make table number 32
For n = 1 To 50
ChildTable n
DoEvents

Next n

End Sub


Public Sub MainTable()
Dim db As Database ' yes this uses DAO...
Dim strSQL As String

' make a main table
strSQL = ";"
BuildSQL ""
BuildSQL "CREATE TABLE MainTable ("
BuildSQL " MainID AUTOINCREMENT CONSTRAINT pk PRIMARY KEY,"
BuildSQL " AnotherField TEXT(32) NOT NULL"
BuildSQL ")"
BuildSQL strSQL

' debug here
'MsgBox strSQL

' and execute the command
Set db = CurrentDb()
db.Execute strSQL, dbfailonerror

Set db = Nothing

End Sub

Public Sub ChildTable(Index As Integer)

Dim db As Database
Dim strSQL As String

' Make a child table with a single relationship
strSQL = ";"
BuildSQL ""
BuildSQL "CREATE TABLE Child" & Format$(Index, "00") & " ("
BuildSQL " ChildNum INTEGER CONSTRAINT pk PRIMARY KEY,"
BuildSQL " BelongsTo INTEGER NOT NULL "
BuildSQL " CONSTRAINT Parent" & Format$(Index, "00") & _
" REFERENCES MainTable"
BuildSQL ")"
BuildSQL strSQL

' debug here
'MsgBox strSQL

Set db = CurrentDb()
db.Execute strSQL, dbfailonerror

Set db = Nothing
End Sub

Private Sub BuildSQL(ByRef SQLText As String)
' standard SQL building routine
' initialise it with a zero length string
' terminate it with a semi colon

Const tfSemiColon = 59 ' = asc(";")
Static strCommand As String

If Len(SQLText) = 0 Then
' reinitialise
strCommand = ""

ElseIf Asc(Left$(SQLText, 1)) = tfSemiColon Then
' end of command: return the string
SQLText = strCommand & ";"

Else
' just add a line of text
If Len(strCommand) > 0 Then
strCommand = strCommand & vbNewLine
End If
strCommand = strCommand & SQLText

End If

End Sub

 

[Tim Ferguson]

(e-mail address removed) (RSGinCA) wrote in

Hmmmm.... That's what I would describe as taking a quote out of
context.

Unfortunately, it's quite a common sentiment around Access circles and
arises from the fact that this one program allows you to draw lines in the
"relationships" window and pretend they are relationships, with some kind
of optional "enforce relational integrity" property. It's a wrong-headed
attempt to make Access an end-user tool rather than a developer platform
and is just not a feature of real dbms systems.

DBMSes have been around for quite a while, and for a long time none of
them provided any automated capability of enforcing referential
integrity, and none provided any automated capability for cascading
updates or deletes.

If we are talking about relational databases, they _all_ provided the first
because that is inherent in the notion of R. The second is not part of the
R model at all, but is provided by many systems as a (dubious) convenience
to the developer.

What you are saying was true of previous systems like network and
hierarchical dbs, and that's why they died so fast when R came out. I am
old enough to remember Fabian Pascal's outbursts on Compuserve as the old
guard went through the pain of moving.

If you actually read the full message, it would have been clear that
what I meant by "working" had absolutely nothing

to do with things like "having tests without patients".

Apparently so. But for me, that kind of error means it's not working.

I wasn't the author of that quote,

If I thought you were, I would not have been nearly so rude

however it appears to me that what
he was saying was that the relationships exist whether or not you use
that "Ralationships" window to draw the little lines between the
tables.

No: it's saying that relationships exist whether you switch on RI or not.

I don't think that I would consider a relationship to be a
'constraint'.

So what else use is it? If a relationship does not protect the data it
doesn't actually do anything at all. In Access, a line drawn in the
relationships window without RI enforced, does nothing except provide a
default join in the query design grid. Big Deal.

It's a
statement of fact about the data.

No: it's an instruction to the db engine about what operations are legal
and what are not. "Statements of fact" are important in the documentation
and the data dictionary etc. but the db engine won't care about them at
all.

The fact is that the original poster of this thread has designed a
database and, apparently because of the limitation of MS Access, he is
being prevented from implementing the definition of that database.

True: see my other response, having done some testing.

I
have no reason to believe that there is anything wrong with that
design. (Perhaps you can provide some input.)

As has been suggested, it's a bit of an odd design and I guess there
probably is a way of getting the links down to fewer than 31; but of course
I don't know his data and there is no theoretical reason why he should have
to.

Bit long: sorry!
All the best


Tim F

 

[RSGinCA]

----- Original Message -----
From: "Tim Ferguson" <[email protected]>
Newsgroups: microsoft.public.access.tablesdbdesign
Sent: Thursday, March 04, 2004 8:33 AM
Subject: Re: Exceeds maximum index number

(e-mail address removed) (RSGinCA) wrote in


Unfortunately, it's quite a common sentiment around Access circles and
arises from the fact that this one program allows you to draw lines in the
"relationships" window and pretend they are relationships, with some kind
of optional "enforce relational integrity" property.

WHAT "is quite a common sentiment around Access circles"? Taking quotes out of
context? Seriously, I don't think that is what you were refering to, but I'm
not sure what it was that you were actually referring to.

It's a wrong-headed
attempt to make Access an end-user tool rather than a developer platform
and is just not a feature of real dbms systems.

Again, I'm not sure what you are talking about, but I suspect I agree with what
you're saying...

If we are talking about relational databases,

I was indeed talking about relational databases. Perhaps I was making too much
of an assumption about the context, and didn't specifically state that my
comments were in the context of relational DBMSes.

they _all_ provided the first
because that is inherent in the notion of R.

'First' refers to "automated capability of enforcing referential integrity", I
assume. I have to disagree on that point. I worked with a relational dbms in
which there was absolutely nothing that the dbms did to prevent someone from
adding a child record without a parent record being present. Sure, it's
"inherent in the notion of R" that you can't have a child record without a
parent, but that's theory. I'm talking about real DBMSes and what they do.

However, this was rarely a problem, as long as you know what you're doing.
Plenty of relational databases have been developed and work just fine without
relying on the dbms to enforce referential integrity.

The place where it is (or can be) a problem is in situations where users have
direct access to the data in the dbms. This is situations like Access's
datasheets. There, if the dbms doesn't enforce RI, the user can delete a
master record without deleting the child records, or he could change a key
field in a master record not knowing that he's probably leaving orphanned child
records.

The second is not part of the
R model at all, but is provided by many systems as a (dubious) convenience
to the developer.

'Second' refers to "automated capability for cascading updates or deletes", I
assume. It also protects the system from wayward users (and developers!)
directly accessing the data.

What you are saying was true of previous systems like network and
hierarchical dbs, and that's why they died so fast when R came out. I am
old enough to remember Fabian Pascal's outbursts on Compuserve as the old
guard went through the pain of moving.


Apparently so. But for me, that kind of error means it's not working.

Of course. However, in my message I had indicated that the programs had to be
set up right, i.e. coded in such a way that it wouldn't allow things like
"having tests without patients".

If I thought you were, I would not have been nearly so rude


No: it's saying that relationships exist whether you switch on RI or not.

And what's wrong (incorrect) about that?

So what else use is it? If a relationship does not protect the data it
doesn't actually do anything at all. In Access, a line drawn in the
relationships window without RI enforced, does nothing except provide a
default join in the query design grid. Big Deal.

I get the impression we're talking about 2 different things... Or maybe,
looking at things from 2 entirely different perspectives.

To me, it's like my 'relationship' to my mother. My mother is my mother. That
is a statement of fact. I don't consider that to be a 'constraint'. However,
there are some rules about my relationship to my mother which I would consider
to be 'constraints'. Such as, "you don't talk back to your mother". Now, THAT
is a constraint!

The statement of fact that my mother is my mother, reflect the real
relationship. A line on a geneology chart between me and my mother is nothing
but a reflection of the fact that she is my mother. You ask the question "So
what else use is it?". If you ask me that question about that relationship
between me and my mother, what can I say?

No: it's an instruction to the db engine about what operations are legal
and what are not. "Statements of fact" are important in the documentation
and the data dictionary etc. but the db engine won't care about them at
all.

I disagree. It's those constraints associated with the relationship that are
telling the "db engine about what operations are legal and what are not"

True: see my other response, having done some testing.


As has been suggested, it's a bit of an odd design and I guess there
probably is a way of getting the links down to fewer than 31; but of course
I don't know his data and there is no theoretical reason why he should have
to.

Rick

 

[RSGinCA]

----- Original Message -----
From: "Tim Ferguson" <[email protected]>
Newsgroups: microsoft.public.access.tablesdbdesign
Sent: Thursday, March 04, 2004 8:16 AM
Subject: Re: Exceeds maximum index number

It's an Access/ Jet bug. It seems that the engine is redundantly creating a
new index on the same field for every FK that references it, so that the
limit is reached when there are 31 related tables. See the code below, if
you want a demonstration. This seems to be the barest-bones approach I can
think of, but if anyone knows how to get around it I'd be interested too.

I was able to modify your code so that there were more than 31 related tables.
I ended up with 50 tables related to the Main table. I had to replace your SQL
statements with DAO calls.

I ended up with the Main table with "litlle lines" connecting it to 50 Child
tables. However, this was accomplished by setting up the relationships without
the enforcement of referential integrity:

Set relNew = db.CreateRelation("Parent" & Format$(Index, "00"), _
"MainTable", tdfNew.Name, _
dbRelationDontEnforce)

I do realize that you have said, "In Access, a line drawn in the relationships
window without RI enforced, does nothing except provide a default join in the
query design grid. Big Deal."

However, for whatever it's worth, this does clarify that it's the "enforcement
of refernetial integrity" that causes there to be "too many indexes" and not
the 'relationship' itself.

Rick

 

[Tim Ferguson]

(e-mail address removed) (RSGinCA) wrote in

I get the impression we're talking about 2 different things... Or
maybe, looking at things from 2 entirely different perspectives.

Probably the latter, and it may be a training issue. Put simply, inventing
some spurious kind of non-enforced relationship breaks Codd's rules 10
(integrity independence) and 12 (non subversion rule). Of course, Access
itself already fails on rule zero, but that is not the point here.

To me, it's like my 'relationship' to my mother. My mother is my
mother. That is a statement of fact. I don't consider that to be a
'constraint'. However, there are some rules about my relationship to
my mother which I would consider to be 'constraints'. Such as, "you
don't talk back to your mother". Now, THAT is a constraint!

No, I disagree: this is a user convention. There is nothing to _stop_ you
talking back to your mother except your decision not to do so. The
constraint is that you share 50% of your genes with her. That is something
you cannot get away from; can't decide to subvert; it's in the definition.
Oedipus did all kinds of things you can't do with your mother, but he could
not avoid the gene-sharing thing.

And in database land, that is exactly the point. Old file-sharing systems
like Paradox and DBase and real systems like IMS relied on data integrity
being built in to the application, and all you could do was to hope that
nobody forgot or introduced a bug. The point of R databases is that the
data rules are built into the engine, they are an integral (sic) part of
the design and all access paths have to go through them. In our case, this
includes datasheets and queries, ADO and DAO, MS Query, Excel and
everything. Remember that you can use CorelDraw! to manipulate a database
now!

I think we have probably taken this to the point of sterility and extreme
boredom for the rest of the NG!


Best wishes



Tim F

 

[Tim Ferguson]

(e-mail address removed) (RSGinCA) wrote in

However, for whatever it's worth, this does clarify that it's the
"enforcement of refernetial integrity" that causes there to be "too
many indexes" and not the 'relationship' itself.

Not quite the point. The enforcement of referential integrity _is_ the
relationship.

And Jet should not be re-indexing the same column time and time again.

B Wishes


Tim F