How do you connect to a UNC address using MS Query?

[Dan Waterloo]

I'm trying to use MS Query in Excel to extract data from an MS Access database on a network drive. That is, I go to a clear sheet in an Excel workbook and then using the menu system say,

 Data > Import external data > New Database Query

and create the query. I have no problem doing this. It's a parameter query that depends on the value in cell A1 of the sheet that I have the query in.

My problem is that I want to distribute this workbook to an audience of people who have to the network drive that the Access database resides on but who likely have mapped to it using a different drive letter than I have.

How do I refer to the database using a UNC address rather than the default letter drive address?

MS Query generates this sort of SQL, referring to the T drive:

 SELECT ListActivity.ActivityDescription, ListActivity.ActivityName, ListActivity.ActivityRE, ListActivity.ActivityStatus, ListActivity.ActivityType
FROM `T:\MOS Application`.ListActivity ListActivity, `T:\MOS Application`.ListAssignment ListAssignment
WHERE ListActivity.ActivityID = ListAssignment.AssignActivityID

I can edit it to

 SELECT ListActivity.ActivityDescription, ListActivity.ActivityName, ListActivity.ActivityRE, ListActivity.ActivityStatus, ListActivity.ActivityType
FROM [\\ca.mycompany\genericdirectory\MOS Application].ListActivity ListActivity, [\\ca.mycompany\genericdirectory\MOS Application].ListAssignment ListAssignment
WHERE ListActivity.ActivityID = ListAssignment.AssignActivityID

but when I try to introduce a parameter it balks.

 

[hobgoblinvasya]

Hi all, i would also like to find out if anyone knows how to do it, since i get the same error as the original poster.

thanks

 

[mleesnit]

Hello, I know this is a few years later but did you ever happen to figure out the solution to this problem? I am having an almost identical error and for the love of god can't figure it out. Thanks in advance if you see this.