How do I tell an Access table to use an index 1

[GeorgeDurkee]

I have a table with a number of indexes built into it. I want to let the user select which way he wants to view the data by using these indexes.&nbsp;&nbsp;How do I tell Access to use one my indexes?<br><br>The only things I can seem to find refer to creating a new index, not using an existing one.&nbsp;&nbsp;I took the following from the example, but it gives me an error (Operation is not supported for this type of object) when assigning a value to .INDEX.&nbsp;&nbsp;All I did from the example was change the name of the table.<br>==============================================<br>&nbsp;Dim dbs As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim tdf As TableDef<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rst As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim idxLoop As Index<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Set dbs = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = dbs.OpenRecordset(&quot;tblMaster&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;Set tdf = dbs.TableDefs!tblmaster<br><br>&nbsp;&nbsp;&nbsp;&nbsp;With rst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;For Each idxLoop In tdf.Indexes<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Index = idxLoop.Name&nbsp;&nbsp;&lt;&lt;&lt;&lt;=========<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Next idxLoop<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br><br>&nbsp;&nbsp;&nbsp;&nbsp;dbs.Close<br>=====================================================<br><br>Any ideas?<br><br>Thanks

 

[JimHorton]

George,<br>If they're viewing in a form, you can set the Sort property, or you can set a Sort on the underlying query--if the forms source is a table, you can make a query based on it and sort that query, (on any field, indexed or not ). Same with reports.&nbsp;&nbsp;<br>--Jim

 

[DougP]

Another Note:<br>A Primary Key will always sort on it first no matter what.<br>Also a form and subform will always link on a Primary key not matter what.<br>You cannot delete it an use nothing even if you want to. Access will put it back even after it's deleted. Try it sometime.<br>That's why I never use a Primary key. <br>I don't put anything in the table itself.<br>I just sort when I need to. In a query on my form etc.<br>I usually always pass SQL which has an &quot;Order by&quot; clause.<br>I never use Relationships either.<br>I wasted more time tripping over the errors Relationships give.<br><br>

 

[GeorgeDurkee]

The issue here is that the users are very non-tech and can't handle the idea of using a sort button or changing the underlaying criteria.&nbsp;&nbsp;In addition, the indexes I want to use involve at least three and sometimes four fields from the table.<br><br>The processing time involved in changing the underlaying sort order is much greater than that involved in changing a prebuilt index.<br><br>Any other ideas?

 

[JimHorton]

George,<br>The simplest thing would *be* a Sort button, and if your users can't deal with that, I don't know of an easier way to have them do this (!).&nbsp;&nbsp;Under the click event of a sort button, you could change the order by clause of the query,ie:<br><br>sub btnSortName_Zip_Click()<br>dim sq as string<br>sq = &quot;Select * from MyTable &quot;<br>sq = sq & &quot; Where blah, blah,&quot;<br>sq = sq & &quot; Order by Name,Zipcode &quot;<br>me.recordsource = sq<br>me.requery 'I'm not sure, but this might be redundant (implicit in above recordsource)<br>End sub<br><br>Now just have several buttons with the different sorts.&nbsp;&nbsp;This *will* use the Index on Name, Zipcode if you do indeed have that index--Jet is not stupid, if the index is there it will use it.&nbsp;&nbsp;I can't think of a faster, easier way to dynamically sort a forms output.<br>--Jim

 

[GeorgeDurkee]

I answered this, although I don't like the answer.<br><br>You can tell Access to use an index, just as described above.&nbsp;&nbsp;However, it does not work with linked tables.&nbsp;&nbsp;You just CAN'T tell it to use an index on a linked table.<br><br>It took me three days to figure this out.<br><br>I ended up using the sort form feature, which seems to work nicely.<br><br>Thanks to all for the help.<br>

 

[davesmith]

DougP never uses a primary key?<br>Blasphemy!<br>&quot;Every table needs a key, the whole key and nothing but the key, so help me Codd!&quot;<br>(Dr Codd, father of relational databases)<br><br>I do use Primary keys, but I rarely, if ever, use a table directly. I only use queries of the tables, and I can use them in any sort I want.