Too many indexes

my table tblClients has too many indexes. I've deleted and deleted indexes
just to make the tables that I absolutely must enforce RI. Some I will just
have to do in the form setup. I'm almost there. The only index that I still
have showing in the indexes table is the primary key. All of the rest are
only in jet.

I think I can make it if someone can help me with this. I noticed with a
list of all indexes printed in the Immediate window that it lists it like
this:

tblClients
PrimaryKey
ClientID
tblBPSInfotblClients
ClientID
tblClientsClientChurchPositionID
ClientChurchPositionID
tblClientsClientTitleID
ClientTitleID
tblClientsGenderID
GenderID
tblClientsMCTitleID
MCTitleID
tblClientstblClients
ClientChurchID
tlkpClientClubStatustblClients
ClientClubStatusID
tlkpClientOrigintblClients
ClientOriginID
tlkpClientTypetblClients
ClientTypeID

Can I delete the ones that reference the table of the foreign key? For
instance, could I delete the ones that start with "tbl*"?

Thanks for the help.

I wouldn't. If you do choose to do so, attempt it on a copy of your database.
Then run some tests on some intesive selelct queries and reports. See if RI
still works. If you are happy with the results, then do it to your production
database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Doctor" wrote:

my table tblClients has too many indexes. I've deleted and deleted indexes
just to make the tables that I absolutely must enforce RI. Some I will just
have to do in the form setup. I'm almost there. The only index that I still
have showing in the indexes table is the primary key. All of the rest are
only in jet.

I think I can make it if someone can help me with this. I noticed with a
list of all indexes printed in the Immediate window that it lists it like
this:

tblClients
PrimaryKey
ClientID
tblBPSInfotblClients
ClientID
tblClientsClientChurchPositionID
ClientChurchPositionID
tblClientsClientTitleID
ClientTitleID
tblClientsGenderID
GenderID
tblClientsMCTitleID
MCTitleID
tblClientstblClients
ClientChurchID
tlkpClientClubStatustblClients
ClientClubStatusID
tlkpClientOrigintblClients
ClientOriginID
tlkpClientTypetblClients
ClientTypeID

Can I delete the ones that reference the table of the foreign key? For
instance, could I delete the ones that start with "tbl*"?

Thanks for the help.

On Fri, 10 Jul 2009 08:17:01 -0700, Jerry Whittle
wrote:

Actually, I believe you can. The form only shows indexes that Access
didn't create behind the scenes to support RI.

OP should turn off automatic indexing.

-Tom.
Microsoft Access MVP



I wouldn't. If you do choose to do so, attempt it on a copy of your database.
Then run some tests on some intesive selelct queries and reports. See if RI
still works. If you are happy with the results, then do it to your production
database.

Doctor wrote:

my table tblClients has too many indexes.

I have a tool that will show you the duplicate indexes on your system
which are quite common given that Access likes creating indexes on
fields that end in ID, etc as well as fields used in relationships,
etc, etc.

Email me at the email address on the website on my sig and I'll email
you an MDB containing the objects required.

Note: You use this utility at your own risk.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/