The following code is a general outline for what is required (note that index direction is not currently preserved).

to re-point a table to a different server) must recreate the indexes after refreshing the link. So for any scripts which modify the connection (e.g. Unfortunately, pseudo-indexes are not preserved when the linked table connection is updated. So in these situations a pseudo-index is required. Yet, without a pseudo-index, the view would not be updateable from Access. 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. Particularly on updateable views where the underlying table(s) are updated frequently. There are several situations where an index in the linked database is not desirable. Jet will decide that the index must be a pseudo-index and create one.

To create the pseudo-index programmatically use Database.Execute to run a CREATE INDEX statement on the linked table/view. 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). Note that when creating the table using DAO/ADOX methods, this dialog is not presented and the pseudo-index is not created. Simply select the fields to be included in the index. 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). How to add pseudo-indexes to linked tables/views Search for tables, charts, publications, or other. SQL Server) and refresh the table/view link (using Linked Table Manager or programmatically with TableDef.RefreshLink). Access IPEDS data submitted to NCES through our data tools or download the data to conduct your research. How to add indexes to linked tables/viewsĬreate the indexes in the linked database (e.g. Click Access in the Import & Link group from the External Data tab on the Ribbon. Here's how to create a linked table that stores and retrieves its data from another Access 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.). Access has an Import/Export wizard that enables you to set up linked tables. 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. These 'true' indexes can be used by the database engine for executing query plans and are enforced during data modification (e.g. The first is a 'true' index, which resides in the linked database and is not used/modified directly by Jet/Access. Access has 2 distinct notions of an index on a linked table/view.

Ms access linked tables