Excel and Access link to external data locks file source file

Question

I use a external connection in an excel file to a shared access database here, but for some reason my connection is making the file unable to be edit for other people. I have no clue why exactly this is happening, but here is the consult code:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=G:\Middle\Controles Gerencias \Rentabilidade Bco\carteiras diarias.mdb;Mode=;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

Chris Meurer Chris Meurer · Accepted Answer · 2017-10-18T21:05:11

By default Excel keeps OLEDB/ODBC connections open (thus locking the access database in use.)

No amount of configuration via connection string will stop Excel from doing this. Contrary to expectations, setting the mode to share deny none, or read only won't get you past the trouble. However, you can modify the properties of the connection via code, to prevent this behavior using the .MaintainConnection property. There is no setting in the UI to modify this property.

Try this code:

Function unlock_conns()
Dim conn

For Each conn In ActiveWorkbook.Connections
    Select Case conn.Type
        Case xlConnectionTypeOLEDB
            conn.OLEDBConnection.MaintainConnection = False
        Case xlConnectionTypeODBC
            conn.ODBCConnection.MaintainConnection = False
    End Select
Next conn

End Function

Excel and Access link to external data locks file source file

1 Answers