title: Excel VBA Connection to MS Access (Read/Write) to Multi User Tables/ Queries
body: <p>I have got to the end of trying to figure out why I can't get correct settings for multi user read and update to MS Access tables from Excel (Office 2013). So I am hoping someone here can help.</p>
  
  <p>Background - I have an MS Access database that contains all data in tables that is used by an Excel application that runs on each users machine. The .accdb file resides on a shared QNAP NAS. I have designed the system so there is a user list table that is accessed on startup via a form, allowing users to 'login' to the system. When a user is logged in, the intention is to record a Yes/No field in this table to prevent the same user name being used on two different machines accessing the same subset of data. 
  Now this all works well from one machine or multiple excel instances on one machine.
  When I load the excel application on multiple machines, the Yes/No field is inconsistent between the machines, unless I stop the Excel application on the machine and restart. Sometimes I get access errors or database corruption errors in this multi user setup - so I am clearly doing something wrong - but can't figure it out. Help Please!!</p>
  
  <p>The connection from the excel application is done as follows:</p>
  
  <pre><code>' connect to the Access database
  Set cn = New ADODB.Connection
  With cn
   .Provider = "Microsoft.ACE.OLEDB.12.0"
   .Mode = adModeShareDenyNone
   .CursorLocation = adUseServer
   .Open ThisWorkbook.Worksheets("Title").Range("B1") &amp; "cms.accdb"
  End With
  </code></pre>
  
  <p>The recordset is opened in excel as follows (which does not show a user set as logged on another machine in via the yes/no field unless I start the application AFTER they have 'logged in')</p>
  
  <pre><code>Set rs = New ADODB.Recordset
  sSQL = "SELECT [access list].* FROM [access list] where [access list].[user] = '" &amp;        Me.userlist.Text &amp; "';"
  rs.Open sSQL, cn, adOpenDynamic, adLockPessimistic
  Application.CalculateUntilAsyncQueriesDone ' not sure I need this????
  </code></pre>
  
  <p>The test for this user being logged in is:</p>
  
  <pre><code>If rs.Fields("Logged in").Value = -1 Then
      fname = rs.Fields("user").Value
      rs.Close
      cn.Close
      Set rs = Nothing
      Set cn = Nothing
      MsgBox fname &amp; " is already logged in - please select another user or exit"
  Else
      userid = rs.Fields("user").Value
      rs.Close
      Set rs = Nothing
      sSQL = "Update [access list] Set [access list].[logged in] = '-1' WHERE [access list].[user] = '" &amp; userid &amp; "'"
      cn.Execute (sSQL)
  
      MsgBox "Welcome " &amp; userid &amp; "! Starting system for your contracts."
      cn.Close
      Set cn = Nothing
      Unload Me
  
  End If
  </code></pre>
  
  <p>So what am I doing wrong? I've tried all different ways with little difference. (Note - I am not trying to test race conditions by both logging in to the same user at exactly the same time - a situation that is not of concern - this is just a simple lock out model I'm implementing). I thought what I was doing was keeping record level locking, made sure I could see any other user change to the record in each application on each machine. I'm stumped! Opening MS Access and looking at the database shows the inconsistency - on one machine the user is logged in, on the other the user is not. I'm thinking its how I connect to the access database or some setting in the access database that I haven't got right - but thats just a thought.</p>
  
  <p>One last twist to this - my test environment machines are Apple MacBooks running Win 8.1/ Office 2013 in a VMware Fusion virtual machine. I do not have the environment to test on native Win 8.1 machines at the moment, but this will be the eventual target. Maybe it will work on the target environment, but can't test that yet and as that is the last step, working out any problems with the code in the test environment is my current priority.</p>
  
  <p>Russ</p>