0

OK we have a multi user (25 users) Access 2013 FE and a SQL Server 2012 BE. Up until yesterday the whole system was working FINE and now it has completely stopped.

  • If user A has a record open via a straight forward select query reading from TABLE Z, then if user B tries to do an insert on TABLE Z, they receive a timeout message. When I go to SQL server and run SP_WH02, it states User B is blocked by User A. When I then investigate the command that is blocking user B, it is just a simple SELECT statement.

Does anyone know why this would be?

The form that User A has open has Record Locks = No Locks and Recordset Type = Dynaset

The record source is a SELECT, retrieving two fields where the key field is a parameter based on the value of another.

However, nothing has changed on this system for months, so I'm confused as to why this would happen.

Thanks for any help.

Michael
  • 2,507
  • 8
  • 35
  • 71
  • Check Access: File->Options->Client Settings->Advanced->Default record locking. Should be "No Locks". – Sergey S. Feb 02 '16 at 14:50
  • Is the form of User A a continuous form with many records? If yes, does it work when A goes to the end of the list (so all records are loaded into Access and the record number is displayed)? – Andre Feb 02 '16 at 14:57
  • Default record locking = No locks. Open database by using record-level locking is ticked though? – Michael Feb 02 '16 at 14:59
  • It is not continuous no, just a single form. – Michael Feb 02 '16 at 14:59
  • UPDATE: We can no longer do any inserts when any user is running a select against Table Z – Michael Feb 02 '16 at 15:21
  • Default record locking=No Locks on all PCs? – Sergey S. Feb 02 '16 at 15:26
  • Clients use the Access 2013 runtime, and it doesn't allow this setting so I'm assuming it takes it from the 'master' copy – Michael Feb 02 '16 at 15:29
  • My question was kinda wrong. Does Table Z have many records (maybe recently increased significantly)? Has the **recordsource** of the form of User A all records, or just one? – Andre Feb 02 '16 at 15:35
  • Table Z has 29,502 rows. Recordsource = all. However, please see the update - we can no longer perform any insert when a select is performed via Access – Michael Feb 02 '16 at 15:50
  • Do you think this is SQL server issue or Access? – Michael Feb 02 '16 at 15:58
  • Is your form bound? You could always switch to an unbound form. – Zaider Feb 02 '16 at 20:18
  • Hi, it was happening when any select is performed against table Z – Michael Feb 03 '16 at 08:37
  • As a note, please use the @Michael syntax when replying to comments, so the user you reply to is notified. -- http://stackoverflow.com/editing-help#comment-formatting – Andre Feb 03 '16 at 11:43

2 Answers2

0

Ok we solved it. If anyone else has the same issue: We archived 9400 records into a new table and now we can do inserts again. It has bought us time so going forward I will normalize table Z further and auto archive records against a criteria.

Michael
  • 2,507
  • 8
  • 35
  • 71
0

It may be the same or similar issue as in MS Access holds locks on table rows indefinitely

Access only fetches the first x rows of the big recordsource, leaving the table in a ASYNC_NETWORK_IO wait state, i.e. locked.

Possible solutions are:

  • Don't have forms or queries that select all records. It usually doesn't make too much sense to scroll through 20k+ records.
  • Force Access to fetch all records, to release the lock. You can do this with Me.RecordsetClone.MoveLast e.g. in Form_Load(). Only advisable with a fast network connection.
Community
  • 1
  • 1
Andre
  • 26,751
  • 7
  • 36
  • 80