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

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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,
Code:
 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:

Code:
 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

Code:
 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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

hobgoblinvasya

Board Regular
Joined
Jun 29, 2005
Messages
215
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

New Member
Joined
Apr 15, 2016
Messages
1
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top