How To Specify Primary Key When Using Vba To Create Tables

I've updated the code with the suggestion given below, which I've tested and works great, for quick reference for future users. I'm using the below code to create linked tables wi

Solution 1:

After linking the table with the code from your question, you need to do this:

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

See VBA Code to Add Linked Table with Primary Key for a complete example.

Note that you do not need to do this if you link a table - Access will detect the primary key automatically (as Remou made clear in his comment below).

But when you link a SQL Server view in Access, it is very important to specify a proper primary key for the view in Access. If you specify the wrong key (= you select columns that don't identify a unique record) or no key at all, Access will link the view as read-only table (as you already noticed).

Plus, it will screw up the displayed rows - see Why does linked view give different results from MS Access vs SQL Manager? for more explanation. (read my answer, and my comments under the other answer)