sql server - How to set primary key when linking with CreateTableDef

Question

In a MS Access database, I'm connecting to views in a SQL Server database like this:

Dim s  As String
s = "ODBC;DSN=mydb;Trusted_Connection=Yes;DATABASE=mydb;"

Dim td As TableDef
Set td = CurrentDb.CreateTableDef("vwMyView", 0, "MySchema.vwMyView", s)

CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh

This creates a linked table, which is linked to a view in SQL Server.

However, I cannot insert/update/delete, because Access does not know the "primary key". How can the information about the primary key added in VBA ?

When using the Linked Table Wizard, you are always asked to select the unique key columns from a listbox. I want to reproduce this behaviour in VBA.

Answer 1

CREATE INDEX <YourIndexName> ON <YourTableName>(<YourFieldName>) WITH PRIMARY