Add a PseudoINDEX to ODBC Linked Tables in MS Access (1 Viewer)

[GohDiamond]

When you link a an ODBC table to MS Access you are asked during the process to Select a Unique Record Identifier.
If you do not select one of the fields then the table will not be updateable.
Why didn't you select a field?

1. Perhaps you don't know which one to select, OR
2. You may know that there is NO identifier designated in the ODBC table on the server.

Still, you'd like to be able to edit a row (record) if needed, Or ADD a NEW ROW (record). The latter is most often the case.
You may be using a VIEW as the linked ODBC Table and the view may consist of more than one table.
Access still needs an index in order to allow the table or view to be updateable.

Here's your Answer:

Currentdb.EXECUTE "CREATE UNIQUE INDEX <<DesiredIndexName>> ON <<LinkedTableName>> (<<FieldToUseAsIndex>>) WITH PRIMARY"
This will not create a real index in Access, and it certainly will not add an index to the source table or view.
What is does is it tells Access to use the designated field in the table AS IF IT WERE a real index. Thus making the table updateable.

It doesn't seem to adversely affect the linked table content and the new index can be seen in the table attributes.

You're Welcome!!

Credit for this revelation goes to StackOverflow - Albert D. Kallal

 

[isladogs]

I also wrote about this at Create SQL View Index (isladogs.co.uk)

If you are using DSN-less connections, you don't get the option of adding an index when the link is setup.
Whether pseudo or real isn't really important in this context.
Its certainly true that the original SQL Server table/view does not get modified

As far as Access is concerned, it behaves as an index on both tables & views and allows updating in Access

 

[ehall pass]

The solution you provided seems quite effective. By using the Currentdb. EXECUTE command and creating a unique index with a desired index name on the linked table and designating a field to use as an index, Access treats it as if it were a real index, ultimately making the table updateable.