Indexes in Access

[stars]

In reading some information on Indexes in Access, I ran across some information that I'm skeptical and would appreciate some input on this.

I know Access sets an index on the primary key field automatically, but the information I was reading stated that Access would also set an index on other fields atomatically such as a "zipcode" field. In one of my Access classes, I had students set up a zipcode field. However, none of my students had an index set automatically for their zipcode field even though it contained a mask for such.

Comments?

 

[RoyVidar]

Hi!

Tools | Options, Tables/Queries tab - there's an "AutoIndex on Import/Create" thingie. The "fragments" there, as beginning or ending characters of a field, would/should automaticly index the field (dupes allowed).

So if "code" is listet there, and "zipcode" is entered as a new field name, it should autoindex.

HTH Roy-Vidar

 

[jrbarnett]

A major point about indexes not commonly pointed out:

It is knowing that while indexes undoubtedly speed up searching and sorting, they slow down insert, update and delete operations because the indexes have to be updated as well as the data. This is why it is only worthwhile indexing fields and combinations of fields that will be searched on or sorted within your access databases.

John

 

[stars]

Thanks for the information, Roy. I went to the tables/queries under options and "ID,key,code and num" are listed under autoindex. Given that,zipcode should have automatically indexed with "code" in there but it didn't!

Any suggestions, or do I have to spell out "zipcode" in autoindex?

Regards,
Stars

 

[TonyJollans]

Hi stars,

No, it should work with just "code" as it is - provided your field is called "zipcode". It does depend how the table is created though - it is an Access (not Jet) feature, so if you run some DDL it won't automatically create an index then, but if the field is added in table design mode you should be alright.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.