Forum Discussion

alandorss's avatar
alandorss
Brass Contributor
Nov 27, 2019

New "Power Query" How to change SHARE DENY WRITE to READ ONLY

Hi,

If i export the connection file and open in notepad I can see the "Share Deny Write" property. i could modify this in notepad to "Read Only" to avoid locking my database. How do i change this property within excel? I spent alot of time creating reports off of this source, but if I cant change this property within excel, i will have to relink the reports to the read only data source.

Excel version is 2016

 

EDIT:

 

In older versions of excel, I was able to go to data-> existing connection -> and change the properties in the connection window from share deny write to read only. This allowed me to pull data from access and not lock it when the excel file was open. 

Now, in 2016, this is the window I have to work with: 

 

Yet, check out the connection file when I export it:

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="<a href="http://www.w3.org/TR/REC-html40" target="_blank">http://www.w3.org/TR/REC-html40</a>">

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<title>Query - tblAllDataCombined</title>
<xml id=docprops><o:DocumentProperties
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns="<a href="http://www.w3.org/TR/REC-html40" target="_blank">http://www.w3.org/TR/REC-html40</a>">
  <o:Description>Connection to the 'tblAllDataCombined' query in the workbook.</o:Description>
  <o:Name>Query - tblAllDataCombined</o:Name>
 </o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
  xmlns:odc="urn:schemas-microsoft-com:office:odc"
  xmlns="<a href="http://www.w3.org/TR/REC-html40" target="_blank">http://www.w3.org/TR/REC-html40</a>">
  <odc:Connection odc:Type="OLEDB">
   <odc:ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%%%%HiddenLink%%%%;Mode=SHARE DENY WRITE;Jet OLEDB:Engine Type=6;</odc:ConnectionString>
   <odc:CommandType>Table</odc:CommandType>
   <odc:CommandText>tblAllDataCombined</odc:CommandText>
  </odc:Connection>
  <odc:PowerQueryConnection odc:Type="OLEDB">
   <odc:ConnectionString>Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=tblAllDataCombined;Extended Properties=&quot;&quot;</odc:ConnectionString>
   <odc:CommandType>SQL</odc:CommandType>
   <odc:CommandText>SELECT * FROM [tblAllDataCombined]</odc:CommandText>
...

I literally just need to change SHARE DENY WRITE to Read only and my life is complete.

Excel
  • JKPieterse's avatar
    JKPieterse
    Nov 28, 2019
    It is simple, really. Once you've set up the query as usual, click the Export button shown in the properties window in your screen-shot. Then discard your current connection and edit the newly created odc file as you already indicated. Then create a new connection using the "Existing connections" button and point at the edited connection file.
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Nov 28, 2019
    It is simple, really. Once you've set up the query as usual, click the Export button shown in the properties window in your screen-shot. Then discard your current connection and edit the newly created odc file as you already indicated. Then create a new connection using the "Existing connections" button and point at the edited connection file.

Resources