Caching Causing Error on Linked Tables

[Unknown_Individual]

Hi all,

I've built a multi-user database which uses SharePoint lists as linked tables and a font-end app in Access. We have about 40 users in total. Everything works great, but the lists are becoming quite large and as a result the performance has taken a hit. Logging in, for example, can take over a minute as it runs some queries on a large table.

I think the answer to my problem is caching. When I enable 'use the cache format that is compatible with MS Access 2010 and later' the performance increase (after the first cache) is brilliant. However, it causes an error to crop up when writing to various tables. The error is runtime 3167 'Record is Deleted' and the debug takes me to the rs.update line of my recordset.

Googling the error (god bless Google) seems to suggest the issue is a corrupt database that needs compacting and fixing. However, if I literally do nothing else but turn caching off, it works fine. And the error occurs on multiple tables, some large and some small. So I believe its directly related to caching.

I'm (almost) positive the issue is caused by the cached version being updated and then no longer being aligned to the online version. I've limited the number of indexes to reduce the risk (I read somewhere this can help), but obviously still have the ID as an index.

There must be some way of handling my recordset or the cached/online tables to stop this happening (such as making sure the two align before updating, or opening my recordset in a particular way), but I've reached the limit of my knowledge and could do with a nudge in the right direction!

This is how I handle the updating of a record (I've renamed the tables/fields) -

 Sub Update_Table(Success As String)
    
    With Forms!Frm_Main
        
        '/ open recordset as table
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("table", dbOpenDynaset)
         
        rs.AddNew
        
        '/ update record with form details
        rs![Field1] = !txtField1
        rs![Field2] = !txtField2
        rs![Field3] = !txtField3
               
        '/ update and close recordset
        rs.Update
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        
    End With
    
End Sub

I'd very much appreciate some suggestions on how I can fix this.

Thanks

 

[Unknown_Individual]

Any ideas? I can provide more information if needed.