wake-up-neo.com

OLEDB-Verbindung hat kein Aktualisierungsdatum

Ich muss das Aktualisierungsdatum für eine Reihe von OLEDB-Datenverbindungen in Excel mit SQL-Tabellen und -Ansichten programmgesteuert überprüfen. Sie sind alle auf dieselbe Weise konfiguriert und verwenden dieselbe Verbindungszeichenfolge. Ich überprüfe sie in VBA mit: 

Connections.OLEDBConnection.RefreshDate

Eine Handvoll dieser Verbindungen hat jedoch kein Aktualisierungsdatum, und ich meine nicht, dass die RefreshDate-Eigenschaft einen NULL-Wert zurückgibt. Diese Eigenschaft existiert nicht einmal. VBA löst aus und "anwendungsdefinierter oder objektdefinierter Fehler". Wenn ich die Verbindungseigenschaften überprüfe, ist das Feld "Zuletzt aktualisiert" leer:

 enter image description here

Es ist konsistent für Verbindungen zu diesen bestimmten SQL-Tabellen und -Ansichten, unabhängig davon, wie ich die Verbindung aufbaue oder wie oft ich sie aktualisiere. Ich verwende OLEDB, und einige unserer Maschinen haben Kompatibilitätsprobleme mit Power Query. Weiß jemand, was dazu führen würde oder was ich ändern muss, entweder in Excel oder in SQL?

8
C Dieguez

Ich habe keine zufriedenstellende Lösung gefunden, aber dies kann Ihnen helfen, wenn Sie unbedingt wissen müssen, dass die Verbindung aktualisiert wurde. Dies kann auch von der Art der Verbindung abhängen, die Sie haben. Haftungsausschluss Diese Lösung ist eher ein Hack als eine professionelle Lösung, scheint jedoch bis jetzt zu funktionieren. Hier ist der Plan:

1 Die Dummy-Anzeige

Zeigen Sie ein Datenstück Ihrer Verbindung in einem Arbeitsblatt an. Dieses Arbeitsblatt Sheet1 kann Hidden oder VeryHidden sein. Ist nicht wirklich wichtig. 

2 Das Ereignis

Ändern Sie das Worksheet_Change-Ereignis wie folgt:

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

3 Modul

Außerdem möchten Sie ein Modul, das Funktionen zum Speichern und Zugreifen auf die RefreshDate -Eigenschaft auf einem anderen Arbeitsblatt bietet. Möglicherweise möchten Sie dies mit einem Objekt tun, das in der Thisworkbook-Eigenschaft gespeichert ist. Dies ist jedoch, soweit ich das beurteilen kann, nicht vor der Zerstörung geschützt.

Hier der Code: 

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 Spülen und für alle Verbindungen wiederholen

Sie müssen dies nun für alle Verbindungen tun, die nicht mit der RefreshDate funktionieren. Möglicherweise möchten Sie alle Datumsangaben in einem Arbeitsblatt speichern und haben für jede Verbindung ein Arbeitsblatt. 

Ist diese Lösung hässlich? Ja, so ist es. Funktioniert es? Ja tut es. 

Die Grundidee ist folgende: Jedes Mal, wenn die Verbindung aktualisiert wird, ändert sich das Arbeitsblatt. Das Ereignis wird ausgelöst: Worksheet_Change Jetzt können Sie das Datum speichern, um später darauf zugreifen zu können.

Wenn Sie andere Möglichkeiten finden, auf ein Ereignis zuzugreifen, wenn eine Verbindung aktualisiert wird, sollte dies auch der Trick sein. Wenn Sie andere Möglichkeiten finden, die RefreshDate zu speichern, wird der Trick ausgeführt. 

Wenn die refreshDate nicht gefüllt ist, haben Sie wahrscheinlich kein Glück.

Als Problemumgehung können Sie die Aktualisierung selbst nachverfolgen. Ausgangspunkt ist das afterRefresh- Event einer Tabelle. Dazu müssen Sie dem Workbook- Modul den folgenden Code hinzufügen (funktioniert nicht mit einem regulären Modul, da With Events ein class benötigt.

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

Jetzt benötigen Sie nur noch eine Logik, um das Aktualisierungsereignis zu speichern. In meinem Beispiel speichere ich es als Name auf Arbeitsmappenebene. Natürlich können Sie es auch in einem (versteckten) Arbeitsblatt speichern. Fügen Sie dies in ein reguläres Modul ein:

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
2
FunThomas

Warum fügen Sie nicht einfach eine Spalte in Ihre SQL-Quelle ein, zu der Sie eine Verbindung herstellen (sei es eine Ansicht oder eine Prozedur) ?. Fügen Sie dort die Spalte RefreshTime=GETDATE() hinzu. Jedes Mal, wenn der Benutzer die Daten aus SQL abruft, wird RefreshTime in den zurückgegebenen Ergebnissen angezeigt. 

Wenn Sie Informationen speichern müssen, wenn der Benutzer die SQL-Quelle aktualisiert hat, tun Sie dies auch in der SQL-Datenbank. Gespeicherte Prozedur erstellen:

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

Die Tabelle LogBook muss zuerst erstellt werden. 

0