excelvbadata-connections

Change Data Connection to Read Only

I have a "summary" Excel workbook with pivots and data connections to other "data" workbooks.

  1. When refreshing the connection and a user is in the data workbook the refresh falls over saying it is read only. (It then tries to open the data workbook.) Is there a way around this? E.g. read the data rather than needing write access? Data String properties below.

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Test\Test.xlsm;Mode=Share Deny Write;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;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;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

  1. When I have refreshed the connections and a user goes into the data workbooks, excel says it read only and used by "Another user".

I'm refreshing using VBA ActiveWorkbook.Connections("Test.xlsm").Refresh.

Is there a way in VBA to tell Excel to drop the access so the data workbook becomes free. The only solution I can think of is to shut down the workbook and open again.

Solution

  • ok I found a solution and hopefully it will help someone else in the future.

    1. I'll deal with the 2nd problem first!

      Dim conn As Variant

For Each conn In ActiveWorkbook.Connections
    conn.OLEDBConnection.MaintainConnection = False
 Next conn

    So in excel 2013 I discovered the maintain connection property is set the true by default. This sets it to false after I have refreshed the data connections. Works a treat and now the user can go into the source data after my refresh with no problems at all.

    1. For the first issue and after much searching + trying to set the connection string Mode=Read/Readonly=False etc.. (which I could not get to work) I decided to handle the read only issue when refreshing the connection. Basically I got some code to check if a user in in the source file. If they are open the source file as read only, refresh the connection (and pivots) then close the file. If no user has the file open then simply refresh the connection. If anyone needs the code please comment and I can post

    Many thanks!