OLEDB Connection has no refresh date

Question

I need to programatically check the refresh date on a number of OLEDB data connections in Excel to SQL tables and views. They're all configured the same way and use the same connection string, and I'm checking them in VBA using:

Connections.OLEDBConnection.RefreshDate

However, a handful of those connections do no have a refresh date, and I don't mean that RefreshDate property returns a NULL, that property doesn't even exist. VBA throws and "application-defined or object-defined error," and when I check the connection properties, the "last refreshed" field is blank:

It's consistent for connections to those particular SQL tables and views, regardless of how I build the connection or how many times I refresh it. I'm stuck using OLEDB, and some of our machines have compatibility issues with Power Query. Does anyone know what would cause this or what I need to change, either in Excel or in SQL?

Contortive

What do the connecitons that don't show this have in common? I was able to replicate the behaviour so it may not be a specifically your issue. – Geometrize

This bug still hasn't been fixed by MS. – Cardona

Answer 1

If the refreshDate is not filled, probably you are out of luck.

As a workaround, you could keep track about the refresh by yourself. Starting point is the afterRefresh-Event of a table. For this you have to add the following code to the Workbook-Module (will not work with a regular module as the With Events need a 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

Now you just need a logic to save the refresh event. In my example, I save it as name on workbook level, of course you could also save it in a (hidden) sheet. Put this into a regular module:

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

Doodlebug

Answer 2

I haven't found a satisfactory solution but this may help you if you desperately need to know went the connection was updated. This may also depend on the sort of connection you have. Disclaimer this solution is more of a hack than a professional solution but seems to work until now. Here is the plan:

1 The Dummy Display

Display a piece of data from your connection in a worksheet. This worksheet Sheet1 may be Hidden or VeryHidden. Doesn't really matter.

2 The Event

Modify the Worksheet_Change Event as following:

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

3 Modul

On top of that you want a modul that provides functionality for storing and accessing the RefreshDate property on another sheet. You may want to do it with an object stored in the Thisworkbook property but that's not save from destruction as far as I can tell.

Here the 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 Rinse and Repeat for all Connections

You now need to do this for all connections that don't work with the RefreshDate. You may want to save all Dates in one worksheet and have one worksheet for each connection.

Is this solution ugly? Yes it is. Does it work? Yes it does.

The basic idea is the following: Every time the connection gets refreshed the worksheet will change, this will trigger the event : Worksheet_Change now you can save the date in order to access it later.

If you find other means to access an event whenever a connection is refreshed this should do the trick too. If you find other means to save the RefreshDate it will do the trick.

Geometrize

Answer 3

Why not simply add a column in your SQL source which you connect to (be it a view or procedure)?. Add there the column RefreshTime=GETDATE(). Every time user pulls the data from SQL they have RefreshTime in returned results.

If you need to store information when user refreshed SQL source do it as well in SQL database. Make stored procedure:

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

The table LogBook must be created first.

Blas

That would certainly be possible, if he has access to his source. Sometimes you don't have the rights or access to the source. For example if you use external sources. – Geometrize

But if you don't have access to external source, you cannot refresh. But you still can see when someone who has access rights last refreshed that source for you. – Blas