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

[bigot]

I've not used Sharepoint lists as a back-end (but I have used MSSQL Server as a back-end), do you need to manually update the fields? I think you're just supposed to bind txtField1 with Field1, txtField2 with Field2, etc.

You may be trying to update the row via VBA at the same time Access is trying to update it automatically (via bound controls), that may be causing the problem.


First, try commenting out all that VBA stuff and just binding the controls to the appropriate fields.
If that fails, revert the changes and try:

Set rs = db.OpenRecordset("table", dbOpenDynaset, dbSeeChanges)

I know I'm shooting in the dark here, but no one else replied. Hope this was somewhat helpful.

[NTC]

I doubt the road that you are currently on will resolve your issue. The key to slow is too much code. So fiddling around and adding more - even if it is for cache - probably is not the right path.

I would first create a sanity check benchmark: bring a copy of those lists off of share point and put them back in Access in a separate standard BE file - - - and then test a front/back linked db with both files on the same PC. If it is slow in that set up then you have a fundamental design issue.

if this is OK, then next I would move that BE to the server that holds SharePoint if possible but not to sharepoint itself or at least to a similarly located server (same LAN segment and same horsepower)..... and see how it runs - if it is slow here then one would begin inspecting network issues.....