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

2021-12-03 00:00:00 vba ms-access sql-server dsn

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

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.

但是如果我使用无 DSN 连接到使用 vba 代码的视图(来自 https://support.microsoft.com/en-us/kb/892490)，它作为不可更新的表链接.

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.

我不知道为什么会有所不同，但是如何连接到 SQL Server 中的视图(作为表或 Access 中的查询)并使其可更新?

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 中的表而不是视图建立无 DSN 连接时，它可以在 Access 中更新.我猜我的问题与没有唯一 ID 的视图有关，但我很困惑为什么 DSN 连接可以更新而无 DSN 则不能.

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.

推荐答案

这不是因为它没有 DSN，而是因为你是通过 VBA 创建的.如果您通过 Access GUI 链接视图，它会要求您提供主键.

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.

但通过 VBA，它不知道主键，因此链接视图不可更新.对于表，Access 会通过 ODBC 自动获取主键，因此该表可以正常工作.

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链接视图后设置主键:

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

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

如果您有很多视图，并定期重新链接它们(例如从开发数据库转到生产数据库)，则硬编码它们的名称和 PK 变得不切实际.我编写了一个函数来从链接视图中检索所有主键索引，并在链接后重新创建它们.
如果你愿意，我可以把它挖出来.

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.


这就是我所做的:


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

要链接表或视图第一次，我使用这个(strTable 是表/视图名称):

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

对于表，主键 (PK) 是自动确定的.对于视图，我会在 Access 对话框窗口中指定 PK，就像我手动链接视图一样.
PK 信息存储在链接视图的 TableDef 对象中，因此我不必在任何地方对其进行硬编码.

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 前端的本地表):

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)

还有这个功能.所有视图(以及仅视图)都称为v_*"，因此我可以按名称列出它们.
我实际上不确定您是否可以从 TableDef 对象确定它是指向表还是视图.

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

当我更改表或视图结构，或更改源数据库时(这是通过更改ODBC_String() 的输出来完成的)，我调用此函数:

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

注意:
可以使用字典对象代替表 t_LinkedViewPK.但是在开发它时，将其作为实际表非常有用.

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.

相关文章