Need consistent path name for Excel-->Access query; Excel keepschanging it

[Rachel Garrett]

I have spreadsheets that pull from Access for PivotTable information.
The database resides on a shared drive; the spreadsheets are on a
SharePoint. I've set them to automatically refresh.

The problem is, I want the path to the share drive to be \\share\drive
\directory\database.mdb. Excel keeps changing it back to A:\\directory
\database.mdb. Although that path *happens* to work when I have the
drive mapped as A:\\, other people are going to have it mapped to
different letters. What can I do to force Excel to use a consistent
naming scheme?

The part of the query that contains the path to the Access database is
in Edit Query --> Connection. If there is somewhere else I need to be
changing it, please let me know. Deleting my existing queries and
starting from scratch with \\share\drive didn't work; it automatically
made the path be A:\\.

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data
Source=<<this is where I'm changing it>>;Mode=Share Deny
Write;Extended Properties="";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine
Type=5;Jet OLEDBatabase 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 OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

 

[Henry]

I have the same problem. Any solutions out there?

Thanks
H