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:

Over the past weekend I installed SQL Server 2005 Express Reporting Services according to the directions in KB934164 on my Windows 7 machine (which did not go particularly smoothly due to problems with the default Application Pool Identity that caused some Reporting Services Configuration steps to fail... but that is another story). Returning to developing ASP.NET in VS2010 I found that most pages of the site I am working on would no longer load in the ASP.NET Development Server for debugging. The failing pages produced the following error message:

The error could easily be reproduced by calling System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(HttpContext.Current.Request.ApplicationPath).

After a bit of searching, I found several discussions of the problem (or very similar problems) and all of the posts that contained a solution involved running Visual Studio as Administrator (e.g. Gabe Sumner on Sitefinity or Miha Markič on Righthand Blogs). I found this solution unacceptable (for a number of reasons) and decided to dig deeper.

After much tracing and browsing around in .NET Reflector, I determined that System.Web.Configuration.ProcessHostConfigUtils makes calls to several external functions in System.Web.Hosting.UnsafeIISMethods which were failing. Although these calls failed, the code in ProcessHostConfigUtils ignores the failures up until it throws the nearly useless message quoted above (rather than using the failure message from the UnsafeIISMethods methods). Using reflection, I invoked the external methods directly, then converted the HRESULT to an exception using System.Runtime.InteropServices.Marshal.GetExceptionForHR and received the following error message:

Finally! Something useful. So I granted my user account read permission on C:\Windows\System32\inetsrv\config and the problem was solved (note that I don't have any sensitive information in this directory which would need to remain private, so I don't have a problem with extra read-only access).

It seems very odd that this file would be loaded at all, given that IIS is not configured with the "shared configuration" feature that uses redirection.config (in fact it has the default configuration in every way). It also seems strange that the code would need to consult the global IIS configuration, although this could be due to any number of reasons (legitimate or due to code which makes incorrect assumptions about IIS and the ASP.NET Development Server co-existing on a machine). However, since all of the code necessary to reproduce the problem is outside of my control, there isn't much more I can do. Enjoy the workaround.

For anyone who has tried creating a blank Access Data Page in Access 2003 (or Access 2002 aka Access XP) on Windows Vista or 7, you will have seen the following message:

Just a quick warning: Don't install Access Runtime 2010 on any computer where you are still using Access 2003 (this may apply to other version combinations as well). I recently installed the latest version of SQL Server Migration Assistant for Access, which requires the Microsoft Access 2010 Runtime. After installing the runtime, creating an event procedure would cause Access 2003 to crash. With a bit more testing, I found that the Visual Basic Editor was automatically creating a reference to the "Microsoft Access 14.0 Object Library" (that came with the 2010 Runtime) instead of the "Microsoft Access 11.0 Object Library" (that came with Access 2003) and it would not allow me to change this library. After removing the 2010 runtime, all is back to normal.