SQL Indexed Views vs defining index on view in Access

[JGALEY]

I have a SQL 2000 DB with Access as a front end. My SQL tables have views that allow me to split the table and have more flexible security. I link the views to Access via ODBC. Then my Access forms use Access queries on the linked views.

I found I had trouble getting the views to allow updates from Access bc Access kept 'losing' the index it created when I linked the views. So I ended up indexing the views in SQL, updating the links to Access, and all seems better, now.

My question is, would there be any advantage to defining the index in Access using the SQL view in the query designer? Or should I stick with the SQL indexed views?

Thanks,

Jason

 

[synapsevampire]

The SQL side should be faster, though if you're on an OLTP system with high transactions, every index hurts performance when inserting.

-k