Too Many Indexes Error

[Doctor]

I'm trying to make an enforced relationship between tblClients and other
tables. But when I do I get the error that there are too many indexes on
tblClients.

Table Design View reveals that there are 4 indexes on this table. And wont
let me add more.

The code at the bottom reveals that there are only 10 indexes on this table
(includes 3 duplicate indexes).

I only have 11 number or foreign key fields in this table that I need to
index. I think I'm allowed near 30? There are 72 main tables in this database
(plus 46 more lookup tables). Probably about 32 or so tables need to be
related to tblClients. As far as I can tell, this should work? Please help.

Are there other hidden indexes somewhere? As I look at the indexes of other
tables with the code below, I notice indexes still present for old field
names that are no longer used. Is this a cause.

'******Code********
Sub ShowAllIndices()
Dim db As DAO.Database
Dim tdf As TableDef
Dim idx As Index
Dim fld As Field
Set db = CurrentDb()
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "tblc" Then
Debug.Print tdf.Name
For Each idx In tdf.indexes
Debug.Print Tab(5), idx.Name
For Each fld In idx.Fields
Debug.Print Tab(10), fld.Name
Next fld
Next idx
End If
Next tdf
End Sub

 

[Allen Browne]

Microsoft calls these indexes, but they are actually constraints. It uses a
constraint for any relationship where you enforce referential integrity, as
well as for each index. If you could the number of enforced relationships to
this table, and add the number of indexes, you are probably hitting close to
32.

In practice, there may be some hidden indexes. This code will list them for
you:

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IndexDescrip(ind)
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Debug.Print " " & IndexDescrip(ind)
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

Function IndexDescrip(ind As DAO.Index) As String
'Purpose: Return a string describing the characteristics of the index.
Dim strOut As String 'String to concatenate to.
Const strcSep = ", " 'Separator between items.

If ind.Primary Then
strOut = strOut & "Primary" & strcSep
End If

If ind.Foreign Then
strOut = strOut & "Foreign" & strcSep
End If

If ind.Clustered Then
strOut = strOut & "Clustered" & strcSep
End If

If ind.Unique Then
strOut = strOut & "Unique" & strcSep
End If

If ind.Required Then
strOut = strOut & "Required" & strcSep
End If

If ind.IgnoreNulls Then
strOut = strOut & "Ignore nulls" & strcSep
End If

'Return the string without the trailing separator.
If strOut <> vbNullString Then
IndexDescrip = Left$(strOut, Len(strOut) - Len(strcSep))
End If
End Function

 

[Doctor]

So does what you are saying mean that you can only enforce RI on 32 tables
even if they all did index on the same field, e.g. ClientID, and if you only
had the one ClientID index in table design?

I counted the relationships with tblClients. And found that I have a total
of 31 relationships both RI and normal combined. A couple foreign tables have
two fields related back to tblClients. Your DbIssueChecker reveals that
presently 10 tables do not have integrity rules enforced, and I am unable to
do so with them.

So then, am I correct in assuming that there is nothing more that I can do
except do my best to make sure the integrity is provided for in the structure
of forms? If yes, then I guess that I am doing well with what I have already
setup. Unless you have additional thoughts.

Thanks again.

This is what your code returned:
? Showindexes("tblClients")
ChurchActiveCharter
Field(s): ChurchActiveCharter
ChurchCurrentCharter
Field(s): ChurchCurrentCharter
ClientID Unique
Field(s): ClientID
Unique
PrimaryKey Primary, Unique, Required
Field(s): ClientID
Primary, Unique, Required
tblClientstblClients Foreign
Field(s): ClientChurchID
Foreign

 

[Allen Browne]

Yes, that's right: the problem exists even if all the related tables come
back to the one field.

IME, this is the most commonly hit ceiling in Access.