sql server - VBA Code to Add Linked Table with Primary Key

Question

I have an updatable view in sql server database. When I create linked table to it with ODBC, I'm asked to select unique record identifier, in order for it to be updateable.

I need to dynamically relink this table in VBA, so I need to drop and recreate the linked table (I cannot update the TableDef.Connect property for ODBC table).

I found several solutions, which are not applicable in my case:

• create the index after linking: I cannot for ODBC source
• create the primary key in database: I cannot, it's a view

These would be applicable:

• a code which will do what the wizard does
• a code to relink without the need to delete TableDef and that works with ODBC linked table, and will not reset previously set identifier

Temporary workaround:

• convert the view to materialized view and create unique index on it

Answer 1

Why can't you create an index for an ODBC source after linking?

At work, we are using Access with linked SQL Server tables, and when someone wants to connect to a different database (change from production environment to test environment), we do something like this for all tables:

Dim TD As TableDef
Dim ConString As String

ConString = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DbName;Trusted_Connection=Yes;"

CurrentDb.TableDefs.Delete "SomeTable"

Set TD = CurrentDb.CreateTableDef("SomeTable", 0, "SomeTable", ConString)
CurrentDb.TableDefs.Append TD
Set TD = Nothing

CurrentDb.Execute "CREATE UNIQUE INDEX SomeIndex ON SomeTable (PrimaryKeyColumn) WITH PRIMARY"