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