OLEDB连接没有刷新日期。

Question

我需要以编程方式检查Excel中多个OLEDB数据连接到SQL表和视图的刷新日期。它们都配置相同并使用相同的连接字符串，我正在使用VBA进行检查：

Connections.OLEDBConnection.RefreshDate

然而，有一些连接没有刷新日期，我不是指RefreshDate属性返回NULL，而是该属性根本不存在。当我检查连接属性时，“上次刷新”字段为空，VBA会抛出“应用程序定义或对象定义错误”：

无论我如何构建连接或刷新多少次，连接到那些特定的SQL表和视图是一致的。我被迫使用OLEDB，而我们的一些机器与Power Query存在兼容性问题。有人知道这是什么原因，或者我需要在Excel或SQL中进行哪些更改吗？

- C Dieguez

---

这些不显示连接有什么共同点？我已经复制了这种行为，所以这可能不是你特定的问题。 - Lucas Raphael Pianegonda

微软还没有修复这个漏洞。 - Tom the Toolman

Answer 1

我还没有找到一个令人满意的解决方案，但如果您急需知道连接何时被更新，这可能会对您有所帮助。这也可能取决于您所拥有的连接类型。免责声明：这个解决方案更像是一个hack而不是专业的解决方案，但目前似乎可以工作。以下是计划：

1. 假显示

在工作表中显示来自您的连接的数据。这个工作表Sheet1可以是隐藏或非常隐藏。并不重要。

2. 事件

将Worksheet_Change事件修改如下：

Private Sub Worksheet_Change(ByVal Target As Range)
RefreshDate (Now())
End Sub

3 模块

此外，您需要一个模块来提供在另一张工作表上存储和访问RefreshDate属性的功能。您可以使用存储在Thisworkbook属性中的对象来完成这个任务，但据我所知，这并不安全。

以下是代码：

Sub RefreshDate(D As Date)
Sheet2.Range("A1").Value = D
End Sub

Public Function GetRefreshDate() As Date
GetRefreshDate = Sheet2.Range("A1").Value
End Function

对于所有连接执行4次冲洗和重复操作

现在您需要对所有无法使用RefreshDate的连接执行此操作。 您可能希望将所有日期保存在一个工作表中，并为每个连接准备一个工作表。

这种解决方案很丑陋吗？ 是的，但它有效。

基本思路如下：每次刷新连接时，工作表都会更改，这将触发事件：Worksheet_Change ，现在您可以保存日期以便稍后访问。

如果您找到其他方法来访问每当连接被刷新时触发的事件，则这也能实现同样的效果。 如果您找到其他方法来保存RefreshDate，那么它也能实现同样的效果。

- Lucas Raphael Pianegonda

Answer 2

如果没有填写refreshDate，那么你可能就没那么幸运了。

作为一种解决方法，你可以自己跟踪刷新。起点是表格的afterRefresh事件。为此，你需要将以下代码添加到Workbook模块中（不能使用常规模块，因为With Events需要一个class）。

Option Explicit
Private WithEvents table As Excel.QueryTable

Private Sub table_AfterRefresh(ByVal Success As Boolean)
    Debug.Print table.WorkbookConnection.name & " refreshed. (success: " & Success & ")"
    If Success Then
        Call trackRefreshDate(table.WorkbookConnection.name, Now)
    End If
End Sub

现在你只需要一个逻辑来保存刷新事件。在我的例子中，我将其保存为工作簿级别的名称，当然你也可以将其保存在（隐藏的）工作表中。把这个放到一个常规模块中：

Sub trackRefreshDate(tableName As String)

    Dim nameObj As Name, nName As String
    Set nameObj = Nothing
    nName = "Refresh_" & tableName
    On Error Resume Next
    ' Check if name already exists
    Set nameObj = ThisWorkbook.Names(nName)
    On Error GoTo 0
    Dim v
    v = Format(Now, "dd.mm.yyyy hh:MM:ss")
    If nameObj Is Nothing Then
        ' No: Create new
        Call ThisWorkbook.Names.Add(nName, v)
    Else
        nameObj.Value = v
    End If
End Sub

Function getRefreshDate(tableName As String)
    Dim nName As String
    nName = "Refresh_" & tableName
    On Error Resume Next
    getRefreshDate = Replace(Mid(ThisWorkbook.Names(nName), 2), """", "")
    On Error GoTo 0        
End Function

- FunThomas

Answer 3

为什么不在SQL源中添加一列（无论是视图还是过程），并将其连接到？在那里添加列RefreshTime=GETDATE()。每次用户从SQL中提取数据时，他们都会在返回的结果中看到RefreshTime。

如果您需要存储用户刷新SQL源时的信息，请在SQL数据库中创建存储过程：

create stored procedure ShareMySource as
-- part one, prepare data
select * from MySQLTable;

-- part two, get user data
insert into dbo.LogBook
select 
RefreshTime=getdate(), 
User = ORIGINAL_LOGIN()

必须先创建LogBook表。

- Przemyslaw Remin

---

如果他有源代码的访问权限，那肯定是可以的。但有时你可能没有源代码的权利或访问权限，比如使用外部资源时。 - Lucas Raphael Pianegonda

但是如果您没有访问外部资源的权限，您无法刷新。但是您仍然可以看到最后一次为您刷新该源的拥有访问权限的人的信息。 - Przemyslaw Remin