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=""</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.
Accepted Solutions