Excel VBA Connection to MS Access (Read/Write) to Multi User Tables/ Queries

Question

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.

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!!

The connection from the excel application is done as follows:

' 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") & "cms.accdb"
End With

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')

Set rs = New ADODB.Recordset
sSQL = "SELECT [access list].* FROM [access list] where [access list].[user] = '" &        Me.userlist.Text & "';"
rs.Open sSQL, cn, adOpenDynamic, adLockPessimistic
Application.CalculateUntilAsyncQueriesDone ' not sure I need this????

The test for this user being logged in is:

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 & " 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] = '" & userid & "'"
    cn.Execute (sSQL)

    MsgBox "Welcome " & userid & "! Starting system for your contracts."
    cn.Close
    Set cn = Nothing
    Unload Me

End If

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.

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.

Russ

Answer 1

I see this post is old, so you may have moved on already, but thought I would answer in case it helps someone. I have been investigating a similar issue where cursor location and lock type seem to be specified correctly, but I still sometimes get inconsistent read results. I am no expert, but this may have to do with the cache refresh and write delays associated with JET/ACE/Access databases. You may want to play around with the Page Timeout and Flush Transaction Timeout settings and consider using the DBEngine.Idle dbRefreshCache statement before doing a read - although this is a DAO feature.