B
BarryP
Hi
I have an Excel 2003 spreadsheet which contains a link to a data table in
Access 2003. I have set up the link to use the full UNC path and have
checked this by checking the SQL statement of the query.
However after several users have used the sheet the link appears to revert
to using mapped drives. Let me explain further.
The sheet is used on several sites; some use T:\ to map to \\server\dept,
others use V: for the same share. ALTHOUGH the SQL statement still shows
\\server\dept the sheet appears to be looking in T:\ or V:\ depending on the
last user to save the sheet.
I have used Microsoft Script Editor to examine the contents of the workbook
and behold it shows
<x:QuerySource>
<x:Connection>DSN=MS Access
Database;DBQ=T:\public\commondata\pcf_v2.mdb;DefaultDir=T:\public\commondata;DriverId=25;FIL=MS Access;MaxBufferS</x:Connection>
and not the UNC path! Yes the SQL statement still shows \\server\dept
Yes I have tried using the find and replace in the editor, and this seems to
fix the issue (at least until another user opens it I think) BUT it wrecks
virtually every graph in the workbook.
Other Microsoft pages suggest that Excel is clever enough to change full UNC
paths for Mapped drives (thanks for that), however the workbooks are opened
using shortcuts which also utilise the full UNC path and NOT mapped drives
which is supposed to negate this feature.
Any Suggestions??
I have an Excel 2003 spreadsheet which contains a link to a data table in
Access 2003. I have set up the link to use the full UNC path and have
checked this by checking the SQL statement of the query.
However after several users have used the sheet the link appears to revert
to using mapped drives. Let me explain further.
The sheet is used on several sites; some use T:\ to map to \\server\dept,
others use V: for the same share. ALTHOUGH the SQL statement still shows
\\server\dept the sheet appears to be looking in T:\ or V:\ depending on the
last user to save the sheet.
I have used Microsoft Script Editor to examine the contents of the workbook
and behold it shows
<x:QuerySource>
<x:Connection>DSN=MS Access
Database;DBQ=T:\public\commondata\pcf_v2.mdb;DefaultDir=T:\public\commondata;DriverId=25;FIL=MS Access;MaxBufferS</x:Connection>
and not the UNC path! Yes the SQL statement still shows \\server\dept
Yes I have tried using the find and replace in the editor, and this seems to
fix the issue (at least until another user opens it I think) BUT it wrecks
virtually every graph in the workbook.
Other Microsoft pages suggest that Excel is clever enough to change full UNC
paths for Mapped drives (thanks for that), however the workbooks are opened
using shortcuts which also utilise the full UNC path and NOT mapped drives
which is supposed to negate this feature.
Any Suggestions??