SQL Server View Primary Key in Access

By | 2024-04-29

Linking a new View

You can link to a SQL Server View in your Access Application using DoCmd.TransferDatabase in the same way that you would link to a table. In the following example you would use the name of the view for both the source table name and the table name (unless you wan’t to use an alias).

DoCmd.TransferDatabase _
acLink, “ODBC Database” _
, strConnectionString, acTable _
, strSourceTableName, strTableName

For the View to be mutable (allow edits) in Access, the View needs a Primary Key in the Frontend Application.

Previously I used CREATE UNIQUE INDEX PrimaryKey ON… to create the PK and specify the name.

Now, when you run TransferDatabase, you are prompted to select a column from the query and Access creates the PK for you and gives it the name “__uniqueindex” that’s with two underscores. Same name for every view.

You can retrieve the PK column name like this

PKColumn_Name = CurrentDB.TableDefs([View Name]).Indexes(0).Fields(0).Name