How to programmatically create an ODBC Linked Table to a SQL Server View and have it be editable?

Question

When I create a DSN connection to SQL Server using the wizard, I am able to link it to a view. In this case, Access recognizes it as an editable table.

But if I use a DSN-less connection to a view using vba code (Method 1 from https://support.microsoft.com/en-us/kb/892490), it is linked as a table that is NOT updatable.

I don't know why there is a difference, but how can I make a connection to a view in SQL Server (either as a table or a query in Access) and have it be updatable?

Edit: When I make a DSN-less connection using a table in SQL Server rather than a view, it is updatable in Access. I would have guessed my problem has to do with views not having a unique ID, but I'm confused why a DSN connection can be updatable while DSN-less cannot.

Answer 1

It's not because it's DSN-less, but because you created it via VBA. If you link the view via the Access GUI, it asks you for the primary key.

But via VBA, it doesn't know the primary key, so the linked view is not updateable. With a table, Access gets the primary key automatically via ODBC, so the table works.

Solution: set the primary key after linking the view via VBA:

S = "CREATE INDEX PrimaryKey ON MyViewName (MyPrimaryKeyField) WITH PRIMARY"
DB.Execute S

If you have many views, and re-link them regularly (e.g. going from dev to production database), it becomes impractical to hardcode their names and PKs. I wrote a function to retrieve all primary key indexes from linked views, and re-create them after linking.
If you want, I can dig it up.

---

Edit:
This is what I do:

' This function returns the full DSN-less connect string
Private Function ODBC_String() As String
    ' In the real world there are several constants and variable in there
    ODBC_String = "ODBC;DRIVER={SQL Server};SERVER=aaa;DATABASE=bbb;UID=ccc;PWD=ccc;LANGUAGE=us_english;TRUSTED_CONNECTION=No"
End Function

To link a table or view the first time, I use this (strTable is the table/view name):

DoCmd.TransferDatabase acLink, "ODBC", ODBC_String(), acTable, strTable, strTable, False, True

For tables, the primary key (PK) is determined automatically. For a view, I get the Access dialog window to specify the PK, same as if I link the view manually.
The PK information is stored in the TableDef object for the linked view, so I never have to hardcode it anywhere.

To store the PK information for all linked views, I have this table (it's a local table in the Access frontend for simplicity):

t_LinkedViewPK
    ViewName        Text(100)
    IndexFields     Text(255)

and this function. All Views (and only Views) are called "v_*", so I can list them by name.
I'm actually not sure if you can determine from a TableDef object whether it points to a table or view.

Private Sub StoreViewPKs()

    Dim TD As TableDef
    Dim idx As index
    Dim FD As Field
    Dim RS As Recordset
    Dim S As String

    ' DB is a global Database object, set to CurrentDB
    DB.Execute "Delete * From t_LinkedViewPK"
    Set RS = DB.OpenRecordset("t_LinkedViewPK")

    For Each TD In DB.TableDefs
        If TD.Name Like "v_*" Then
            ' Views must have exactly one index. If not: panic!
            If TD.Indexes.Count <> 1 Then
                MsgBox "View " & TD.Name & " has " & TD.Indexes.Count & " Indizes.", vbCritical
                Stop
            End If

            Set idx = TD.Indexes(0)
            ' Build field list (the index may contain multiple fields)
            S = ""
            For Each FD In idx.Fields
                If S <> "" Then S = S & ", "
                S = S & FD.Name
            Next FD

            RS.AddNew
            RS!ViewName = TD.Name
            RS!IndexFields = S
            RS.Update
        End If
    Next TD

    RS.Close

End Sub

When I make changes to table or view structures, or change the source database (this is done by changing the output of ODBC_String()), I call this function:

Public Function Sql_RefreshTables()

    Dim TD As TableDef
    Dim S As String
    Dim IdxFlds As String

    DB.TableDefs.Refresh

    ' save current Indizes for Views (recreated after .RefreshLink)
    Call StoreViewPKs

    For Each TD In DB.TableDefs
        If Len(TD.Connect) > 0 Then
            If Left(TD.Connect, 5) = "ODBC;" Then

                Debug.Print "Updating " & TD.Name
                TD.Connect = ODBC_String()
                TD.RefreshLink

                ' View?
                If TD.Name Like "v_*" Then
                    IdxFlds = Nz(DLookup("IndexFields", "t_LinkedViewPK", "ViewName = '" & TD.Name & "'"))
                    If IdxFlds = "" Then Stop

                    ' Create PK
                    S = "CREATE INDEX PrimaryKey ON " & TD.Name & " (" & IdxFlds & ") WITH PRIMARY"
                    DB.Execute S
                End If

            End If
        End If
    Next TD

    DB.TableDefs.Refresh

End Function

Note:
Instead of the table t_LinkedViewPK, a dictionary object could be used. But while developing this, it was very useful to have it as an actual table.