Access has 2 distinct notions of an index on a linked table/view. The first is a "true" index, which resides in the linked database and is not used/modified directly by Jet/Access. These "true" indexes can be used by the database engine for executing query plans and are enforced during data modification (e.g. for uniqueness). The second is a "pseudo-index" which is "a dynamic cross-reference" that Jet/Access creates/maintains to facilitate data modification on linked tables/views which lack a unique index in the linked database. Constraints in pseudo-indexes can not be enforced outside of Access (and I am unsure if they are even enforced within Access) and are not used during query execution on the linked database (of course, although I have no knowledge of how they are used within Access...).
How to add indexes to linked tables/views
Create the indexes in the linked database (e.g. SQL Server) and refresh the table/view link (using Linked Table Manager or programmatically with TableDef.RefreshLink).
How to add pseudo-indexes to linked tables/views
When a link to a table/view without an index is created, Access prompts the user with a "Select Unique Record Identifier" dialog box (which is also triggered from code using DoCmd.TransferDatabase to create the link). Simply select the fields to be included in the index. Note that when creating the table using DAO/ADOX methods, this dialog is not presented and the pseudo-index is not created. Also, attempting to add an index to a linked table using DAO/ADOX results in an error (similarly to attempting to add an index through the table designer). To create the pseudo-index programmatically use Database.Execute to run a CREATE INDEX statement on the linked table/view. Jet will decide that the index must be a pseudo-index and create one.
Why pseudo-indexes?
There are several situations where an index in the linked database is not desirable. Particularly on updateable views where the underlying table(s) are updated frequently. Creating an indexed view has the side effect of materializing the table on disk, which requires every update to the underlying tables to involve multiple writes to disk as both the original table and the indexed view are updated. Yet, without a pseudo-index, the view would not be updateable from Access. So in these situations a pseudo-index is required.
Caveats
Unfortunately, pseudo-indexes are not preserved when the linked table connection is updated. So for any scripts which modify the connection (e.g. to re-point a table to a different server) must recreate the indexes after refreshing the link. The following code is a general outline for what is required (note that index direction is not currently preserved). The code should be run on the index before the connection is updated, then the returned SQL run after the connection is changed and the link is refreshed:
function indexToSQL(index, tableName) {
var e, query;
query = new Array();
query.push("CREATE ");
if (index.Unique) {
query.push("UNIQUE ");
}
query.push("INDEX ");
query.push(index.Name);
query.push(" ON ");
query.push(tableName);
query.push(" (");
for (e = new Enumerator(index.Fields); !e.atEnd(); e.moveNext()) {
query.push(e.item().Name);
query.push(", ");
}
query.pop();
query.push(") ");
if (index.IgnoreNulls || index.Primary || index.Required) {
query.push("WITH ");
if (index.IgnoreNulls) {
query.push("IGNORE NULL ");
}
if (index.Primary) {
query.push("PRIMARY ");
}
if (index.Required) {
query.push("DISALLOW NULL ");
}
}
return query.join("");
}
