具有计数字段的可更新记录集 - MS Access

Question

我不是 SQL 专家。也许我在这里尝试做的事情根本不可能实现。

我正在尝试获取一个可更新的记录集，其中包含一个聚合函数结果的字段。

我正在寻找这样的东西：

SELECT Contact.*, Count(OrderID) as CountOfOrders
FROM Contact INNER JOIN Order ON Order.ContactID = Contact.ContactID
WHERE ContactID = 1

Answer 1

当使用 MSDataShape OLE DE 提供程序和 Jet（或 ACE）的 OLE DB 提供程序时，确实可以创建可更新的 ADO 记录集并APPEND基于一个集合函数，例如 COUNT()。生成的 SQL 式代码看起来更像是这样：

 SHAPE  {SELECT ContactID, ContactName FROM Contact} 
APPEND ({SELECT ContactID, OrderID FROM Orders} 
         RELATE ContactID TO ContactID
       ) As rsDetails, COUNT(rsDetails.OrderID) AS CountOfOrder

这是一个简短的“概念证明”：将以下内容粘贴到任何 VBA 模块中（例如使用 Excel），无需引用，在临时目录中创建一个新的 .mdb，创建包含数据的表，为了证明记录集是可更新的，ContactName 值已更改，并且记录集重新打开以显示它确实已更改：

Sub ShapeAppendCount()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"

    Dim jeng
    Set jeng = CreateObject("JRO.JetEngine")
    jeng.RefreshCache .ActiveConnection

    Set .ActiveConnection = Nothing
  End With

  Dim con
  Set con = CreateObject("ADODB.Connection")
  With con
    .ConnectionString = _
        "Provider=MSDataShape;" & _
        "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    .CursorLocation = 3
    .Open

    .Execute _
    "CREATE TABLE Contact (" & _
    "ContactID INTEGER NOT NULL UNIQUE, " & _
    "ContactName VARCHAR(20) NOT NULL);"

    .Execute _
    "CREATE TABLE Orders (" & _
    "ContactID INTEGER NOT NULL REFERENCES Contact (ContactID), " & _
    "OrderID INTEGER NOT NULL UNIQUE);"

    .Execute _
    "INSERT INTO Contact (ContactID, ContactName)" & _
    " VALUES (1, 'OneDayWhen');"

    .Execute _
    "INSERT INTO Orders (ContactID, OrderID)" & _
    " VALUES (1, 1);"

    .Execute _
    "INSERT INTO Orders (ContactID, OrderID)" & _
    " VALUES (1, 2);"

    Dim rs
    Set rs = CreateObject("ADODB.Recordset")
    With rs
      .CursorType = 2  ' adOpenDynamic
      .LockType = 4  ' adLockBatchOptimistic

      .Source = _
      " SHAPE {SELECT ContactID, ContactName FROM Contact} " & _
      "APPEND ({SELECT ContactID, OrderID FROM Orders} " & _
      "RELATE ContactID TO ContactID) As rsDetails, " & _
      " COUNT(rsDetails.OrderID) AS CountOfOrder"

      Set .ActiveConnection = con
      .Open

      .Fields("ContactName").Value = "Pink Cat"

      .UpdateBatch

      MsgBox .GetString
      .Close
    End With

    With rs
      .Source = _
      "SELECT ContactID, ContactName FROM Contact"

      Set .ActiveConnection = con
      .Open

      MsgBox .GetString
      .Close

    End With
  End With
End Sub