Locking Issue using ADO with Excel as a Database

Sorry, that was a typo. I am trying to do it all with ADO.

Excel isn't multi user, you're going to have these sorts of problems unless you use a database. Why isn't access an option?

MS Access is against company policy.
This is small scale, I am not likely to get more than 2 users at any one time.
The issues are arriving also arriving during testing when there are not multiple users using the system.


For example when I run the following code. When the file is in a locked state output reverts back to the original value, even though it successfully updates the value at the start.
When the database is not in a locked state the output changes to "TestUpdated".
I hope this makes sense, I have also checked the file attributes to make sure that it is not read only etc.

Sub CheckDatabaseLocked()
Dim adodb As adodb.connection
Dim adodb2 As adodb.connection
Dim strDBPath As String
strDBPath = ThisWorkbook.Names("CONNECTION_FILE").RefersToRange.Value   '---Add DB file name here

OpenADODBConnection strDBPath, "YES", 0, adodb
Dim rsNew As New Recordset
rsNew.Open "SELECT  `Order` FROM `ScopeInclusion$`", adodb, adOpenKeyset, adLockPessimistic, -1
rsNew.Fields("Order").Value = "TestUpdated"
rsNew.Update
debug.print rsNew.Fields("Order").Value
rsNew.Close

adodb.Close
Set adodb = Nothing
set rsNew = nothing

Dim rs As New Recordset
OpenADODBConnection strSourceFile:=strDBPath, HDR:="YES", IMEX:=0, adodb:=adodb2

rs.Open "SELECT  `Order` FROM `ScopeInclusion$`", adodb2, adOpenKeyset, adLockPessimistic, -1
debug.print rs.Fields("Order").Value

rs.close
adodb2.close
set adodb2 = nothing
set rs = nothing

End Sub
Public 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:
'----Close out objects from memory -----------------------------------------------------------------'
Exit Function
'----Error Handling --------------------------------------------------------------------------------'
debugger:
    OpenADODBConnection = False
    
End Function

Hi scott,
i have the same problem... everything seems to work fine (no errors) but after updating, the database (.xlsm file) is always the same.
Please, let me know if you resolved this problem!
Daniele

Hi scott,
i have the same problem... everything seems to work fine (no errors) but after updating, the database (.xlsm file) is always the same.
Please, let me know if you resolved this problem!
Daniele