Locking Issue using ADO with Excel as a Database

Hi,

I currently have an ongoing issue using ADO with Excel as the BE database.
My application uses an Excel (2010) workbook as a backend to store the required data, then uses another excel workbook to query (Using list objects with data connections) and also manipulate and edit the data in the back end using ADO recordsets. I have used this setup as MS Access is not currently an option, and I need multiple users to be able to manipulate and query data at the same time without read-only restrictions.

The issue is that occasionally the excel BE database seems to go into a "Locked state". When in this "locked state", the recordset appears to update successfully. However, when I open the BE database or requery with a new ADO connection the changes have disappeared.
The only way for me to fix this currently is to open the excel BE Database, hit Save and close the workbook.

The most frustrating part is that most of the time it works without issue.

Any help would be greatly appreciated. An example of my connection string is below...

 
Function OpenADODBConnection(strSourceFile As String, HDR As String, IMEX As Integer, ByRef adodb As adodb.connection) As Boolean
On Error GoTo debugger:
'----Declare local variables ----------------------------------------------------------------------'
Dim connString As String

'--------------------------------------------------------------------------------------------------'
Set adodb = CreateObject("ADODB.Connection")
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & strSourceFile & _
        "';Extended Properties='Excel 12.0;HDR=" & HDR & ";IMEX=" & IMEX & "';"
        adodb.Open connString
If Not adodb Is Nothing Then
    OpenADODBConnection = True
Else
    OpenADODBConnection = False
End If
myExit:
Exit Function
'----Error Handling --------------------------------------------------------------------------------'
debugger:
    OpenADODBConnection = False
    
End Function

Sub test()

Dim adodb As adodb.connection
Dim rs As New Recordset
OpenADODBConnection "C:\Users\minasa9\Documents\Matrix Testing\SHUTDOWN PLANNING DATABASE clear.xlsx", "YES", 1, adodb

rs.Open "SELECT * FROM `ORDERS$`", adodb, adOpenKeyset, adLockOptimistic, -1

if rs.recordCount > 0 then rs.movefirst

rs.Fields(1).Value = "Test"
rs.update

set rs = nothing
set adodb = nothing

endsub