Too Many Indexes

[DawnTreader]

Hello All

I have a situation where this error came up. I actually have the situation
under control, but i have a few questions about it.

as per one suggestion i found here i went to the tools, options, table/query
properties and autoindex on import/create and found there was nothing in the
box. the suggestion i found said to do something here to stop fields from
being autoindexed. what do i do?

is there no place where you can turn the "feature" off?

 

[Jerry Whittle]

It's strange that nothing is listed in the auto index box unless someone
manually removed it. I suggest putting something unlikely, such as ZZ in it,
doing a compact and repair, then seeing if only the ZZ is still listed in the
auto index box.

 

[Allen Browne]

Chris, what technology area do you have a Microsoft MVP award in?

I don't see a Chris O'* listed for Access on Microsoft's site?

 

[Allen Browne]

My MVP profile is only available to Microsoft employees.

Chris
Microsoft MVP

Unlikely story.

Plonk

 

[John W. Vinson]

Hello All

I have a situation where this error came up. I actually have the situation
under control, but i have a few questions about it.

as per one suggestion i found here i went to the tools, options, table/query
properties and autoindex on import/create and found there was nothing in the
box. the suggestion i found said to do something here to stop fields from
being autoindexed. what do i do?

is there no place where you can turn the "feature" off?

You might find this little code snippet useful:

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) <> "MSys" 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

Limit it to just this one table if you wish rather than looping the Tabledefs
collection. It will at least show you all the indexes and the fields that they
index; you may well have some redundant ones which can be deleted. Compact the
database after you do so to recover the waste space (and the freed-up slots!)

 

[Tony Toews [MVP]]

Unlikely story.

Ah, no. Chris did log in under his Windows Live account and posted a
message. I then saw the little MVP symbol beside his name.

Plonk

Yes, I realize that at first glance Chris may appear to be claiming
something he isn't eligible for but he appears to be legitimate.

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/

 

[DawnTreader]

Hello All

thanks for the responses. i took out the unneeded indexes, it fixed my
problem. i used a bunch of field names that ended in ID and access auto
indexed them. i figured i would go and take the ID out of that option spot,
but got there to find nothing.

i will try adding one and compacting to see what happens. for the most part
i just curious as to why, but it wont make any difference now that i have
gotten past the problem.

again, thanks all!

 

[Allen Browne]

... for the most part i just curious as to why,

Chances are, the table was created before you unchecked that 'auto-index'
option.

 

[Chris O''C]

Thanks, Tony. I appreciate it.

Unlikely he'd look here, eh?

http://www.microsoft.com/office/com...&p=1&tid=521b3ae2-25ee-416d-8471-6df09e208efd


Chris
Microsoft MVP

 

[Tony Toews [MVP]]

Unlikely he'd look here, eh?
http://www.microsoft.com/office/com...&p=1&tid=521b3ae2-25ee-416d-8471-6df09e208efd

Now that I've replied to your posting it is extremely likely that
Allen will take a look at the link. <smile>

Allen, note how Jerry has a little MVP symbol beside his name. Then
scroll down to Chris' above posting and you'll see the same symbol.

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/

 

[Allen Browne]

Chris, I just looked up some of your posts from the last 12 months.

You have given useful replies to many people, demonstrating technical skills
and a willingness to share your knoweldge with peers. Your contributions
stand on their own merit, whether Microsoft recognises them or not.

I apologize for my comment, which was unhelpful, hasty, and ill-advised.

 

[Tom Wickerath]

Hi Allen,

Chances are, the table was created before you unchecked that 'auto-index'
option.

This isn't an option that one checks or unchecks, at least in Access 2003
and earlier versions. The option is found under Tools | Options... --->
Tables/Queries tab. This option is a global setting; it does not travel with
the .mdb file. I suspect that perhaps the fields were created on a different
PC, where this global option has not been cleared.

http://www.access.qbuilt.com/html/gem_tips.html#AutoIndex


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tony Toews [MVP]]

It all worked out thanks to your help, Tony. I appreciate it.

You're welcome.

Allen's a very good guy. I expected no less of him once he realized
the situation.

Feel free to join us in the private MVP newsgroups. And I see no
reason why you can't continue to obfuscate your last name there
either.

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/