Search code examples
ms-accesssharepoint-online

OpenRecordset doesn't retrieve latest values from Sharepoint Online list

Dim DB As DAO.Database: Set DB = CurrentDb
Dim Log As DAO.Recordset
        
Set Log = DB.OpenRecordset("SELECT TOP 1 * FROM Table WHERE NOT IsNull(Field) ORDER BY Id DESC")

On the first run, this query retrieves the correct values.
On subsequent runs, the same initial values are returned, even if the actual record has changed.

For example:

  1. Released = 01/01/2000
  2. Recordset returns 01/01/2000
  3. Released = 12/06/2001
  4. Recordset returns 01/01/2000
  5. Open linked table or restart access
  6. Recordset returns 12/06/2001

I'm assuming there's some caching going on, but I can't find how to ignore it and always fetch the latest records?

Solution

  • You need to refresh your Sharepoint linked table:

    CurrentDb.TableDefs("TableName").RefreshLink

    Reference to this:

    Similar question