How to set a primary key when connecting to CreateTableDef

Question

In the MS Access database, I connect to the views in the SQL Server database as follows:

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 associated with the view in SQL Server.

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

When using the Linked Tables wizard, you are always prompted to select unique key columns from the list. I want to reproduce this behavior in VBA.

Answer 1

You can always update the table you just added to include the Index / Primary key. Sort of,

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.Execute "CREATE UNIQUE INDEX SomeIndex ON vwMyView (PrimaryKeyColumn) WITH PRIMARY".

CurrentDb.TableDefs.Refresh

Set td = Nothing

, CurrentDB INDEX. , - , CREATE.

Answer 2

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