如何以编程方式创建ODBC链接表到SQL Server视图并使其可编辑？

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.

当我使用向导创建到SQL Server的DSN连接时，我能够将其链接到视图。在这种情况下，Access将其识别为可编辑的表。

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.

但是，如果我使用vba代码（来自https://support.microsoft.com/en-us/kb/892490的方法1）使用与DSN无关的连接，则会将其链接为不可更新的表。

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?

我不知道为什么会有区别，但是如何连接到SQL Server中的视图（作为Access中的表或查询）并使其可更新？

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.

编辑：当我使用SQL Server中的表而不是视图创建无DSN连接时，它在Access中是可更新的。我猜想我的问题与没有唯一ID的视图有关，但我很困惑为什么DSN连接可以更新而DSN-less不能。

1 个解决方案

#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.

这不是因为它是DSN-less，而是因为你是通过VBA创建的。如果通过Access GUI链接视图，它会要求您输入主键。

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.

但是通过VBA，它不知道主键，因此链接视图不可更新。使用表，Access通过ODBC自动获取主键，因此表可以正常工作。

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

解决方案：通过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.

如果您有许多视图，并定期重新链接它们（例如从开发人员转到生产数据库），那么对其名称和PK进行硬编码变得不切实际。我编写了一个函数来从链接视图中检索所有主键索引，并在链接后重新创建它们。如果你愿意，我可以把它挖出来。

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):

要第一次链接表或视图，我使用它（strTable是表/视图名称）：

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.

对于表，主键（PK）是自动确定的。对于视图，我得到Access对话框窗口以指定PK，就像我手动链接视图一样。 PK信息存储在链接视图的TableDef对象中，因此我无需在任何地方对其进行硬编码。

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

要存储所有链接视图的PK信息，我有这个表（为简单起见，它是Access前端中的本地表）：

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.

而这个功能。所有视图（和唯一视图）都称为“v_ *”，因此我可以按名称列出它们。我实际上不确定您是否可以从TableDef对象确定它是指向表还是视图。

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:

当我对表或视图结构进行更改或更改源数据库（这是通过更改ODBC_String（）的输出来完成）时，我调用此函数：

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.

注意：可以使用字典对象代替表t_LinkedViewPK。但是在开发它的同时，将它作为实际表格非常有用。

#1