MS Access ODBC Timeout error on save record

Question

I'm running MS Access 2016 connecting via ODBC to SQLServer 2016. I have a simple form based on a linked table. When I display it in Form View, change some data, and click for next record, it updates the current record and moves to the next, but when I use a combo box to select the record to move to - it displays the record I've selected and if I change some data on it - it returns the error 'ODBC -update on linked table failed; Microsoft ODBC SQL Server Driver Query timeout expired (#0)’

I tried 2 different methods shown below by the code for the 2 different combo boxes

Private Sub cboFindRecord_AfterUpdate()
Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "Id=" & Nz(Me.cboFindRecord, 0)
    If Not rst.EOF Then
        Me.Bookmark = rst.Bookmark
    End If
    rst.Close
    Set rst = Nothing
End Sub


Private Sub FindRecord2_AfterUpdate()
    Me.txtId.Enabled = True
    Me.txtId.Locked = False
    Me.txtId.SetFocus
    If IsNull(Me.FindRecord2) Then
        Exit Sub
    End If
    DoCmd.FindRecord Me.FindRecord2, acEntire, , acSearchAll, , acCurrent
End Sub

(I have this code on other forms in this database, and they work, but for this form, for this table it doesn’t)

There are 500 records on the table, its seems to be a blocking issue rather than a timeout issue. If I run an Sp_who2 on SQL Server it shows there is a process block – but why, can anyone help me?

Do you have multiple complex indexes on the table being updated? — Minty
I have 5 indexes on this table including the primary key - just removed all the indexes apart from the primary key and still doesn't work :-( — I. Seymour
Just realised that if I use the record selectors to move to next/previous record it works!! But if I use my coded combo boxes to select it doesn't. It appears to create another SPID on SQL Server and the update is blocked by the select. — I. Seymour

tomRedox tomRedox · Accepted Answer · 2018-08-14T17:28:08

We've just had a similar issue with an Access system with a SQL backend, which was experiencing intermittent timeouts seemingly at random, that has also turned out to be a combo box issue:

We inherited this system and the original developers would use a combo box to lookup an IDs text equivalent and then reference that combo box from say a label. This meant that, for example, on the Sales Line form there was a hidden combo that contained the entire Stock table just to get the name of the stock item.

That combo appears to have been locking the entire Stock table and preventing inserts/updates/deletes.

We could consistently do the following to illustrate the issue:

Open a sales line on one copy of the system - that sales line has a combo that looks up data from the Stock table
Open the stock item in another copy of the system
Edit and save that stock item
At that point the system showing the stock item hangs
Then close the sales line on the first copy
That seems to release the lock on the Stock table as the stock item will then immediately save on the second copy

We resolved this by creating a SQL pass through query to the stock table and using that as the record source in the combo, rather than using an Access linked-table onto the Stock table in the SQL database as we had been previously. Doing that stopped the table locking occurring.

MS Access ODBC Timeout error on save record

2 Answers