title: MS Access Admin State During Statement Execution
body: <p><strong>Background</strong></p>
  
  <p>I develop a number of tactical data capture tools all of which use the same approach of a split front and back end as below:</p>
  
  <ul>
  <li>Excel VBA forms based front end (using ADO to connect to the back end)</li>
  <li>Access 2007 (accdb) database as a back end</li>
  </ul>
  
  <p>My preference would be to use SQL Server as a back end but this is not possible due to restrictions that I am not able to solve.</p>
  
  <p>Each tool distributed has a varying number of users using the tools at the same time (anywhere between 10 - 300+). I understand that Access is not the ideal solution giving the potential number of concurrent users but once again, this is out of my control.</p>
  
  <p>When the tools are in use, users sometimes receive the <code>The database has been placed in a state by user 'Admin' on machine '***' that prevents it from being opened or locked.</code> error.</p>
  
  <p>Given the volume of transactions, the error occurs roughly 0.001% of the time.</p>
  
  <p>I've read a number of articles on the topic, the majority of which end with an assumption that an object within the database is being modified or that a user is entering <code>Design Mode</code> which should be addressed by having a separated front and back end.</p>
  
  <p><strong>Question</strong></p>
  
  <p>A number of different types of queries occur including <code>SELECT</code> and <code>INSERT INTO</code> statements. The users do not directly access the database file and therefore no object as being modified and nothing is being put into <code>Design Mode</code> therefore why are users experiencing this error? Is it due to the shear number of users?</p>
  
  <p>I use the same approach towards connecting to the database using the below method:</p>
  
  <pre><code>Public Function fGetOrderStatus() As Variant()
  
  Dim oDB As ADODB.Connection
  Dim oCM As ADODB.Command
  Dim oRS As ADODB.Recordset
  
  On Error GoTo Err:
  
  Set oDB = New ADODB.Connection
  oDB.Open gcConn
  Set oCM = New ADODB.Command
  
  With oCM
      .ActiveConnection = oDB
      .CommandText = "SELECT OrderStatusId, OrderStatus FROM ct_elh_OrderStatus WHERE Deleted Is Null"
      .CommandType = adCmdText
      Set oRS = .Execute
  End With
  
  If Not oRS.BOF And Not oRS.EOF Then
      fGetOrderStatus = oRS.GetRows()
  Else
      Erase fGetOrderStatus
  End If
  
  oRS.Close
  Set oRS = Nothing
  oDB.Close
  Set oDB = Nothing
  
  Exit Function
  
  Err:
      MsgBox ("An unexpected error occurred. Please try again later."), vbCritical, "Error"
  
  End Function
  </code></pre>