Remote views and indexes

[phita]

Hi folks !
I am using remote views connected to an Access database. My problem is, how do I create indexes and store the permanently ? Or do I have to create the index in code every time I want to use the index tag ? I am currently recreating the indexes in the Init event of all my forms. Is there a better way ?

Thanks,

Phita

 

[fluteplr]

You would need to recreate them each time. However if you are using remote views appropriatly you should be only retriving a few of the records at a time, so the need for indexes is pretty rare except for setting sort order. If you are retrieving the entire access table in the remote view you need to have a look at parameterized views. I have been using remote views for about 3.5 years and it is a rare occasion when I retrieve more than 500 records at one time. With the remote view cursor that small a locate with out an index is pretty much as fast as a seek, with an index.

 

[DEDMOD]

What are you doing with these indexes? You can order your view when you create it using one or more fields and make the order ascending or descending as you wish. I'd think this would be sufficient for most purposes. OTOH, I'm not quite sure what the relationship (if any) is between indexes on a table in a database and orders in the records of a view.

I suspect that if you were using a remote view to a MS SQL Server database, whatever SQL statment was sent to the database would be optimized by SQL Server using any indexes available. I don't know how Access works and whether it'd use any existing keys if needed or not. On the other hand I could be all wet.

Dave Dardinger