Linking via 'New Database Query' on a shared network

[dickharlo]

I'm linking Excel to an Access Database on a shared drive. I can open
the excel spreadsheet and update external data without problem. What
appears to be happening to others, is they are using differenent drive
letters to map to the same network location.

Linking through MS Query, my SQL is as follows:

SELECT CMPM_Grouping.Lead, CMPM_Grouping.`Path Type`,
CMPM_Grouping.Phase, CMPM_Grouping.`PMT ID`, CMPM_Grouping.`PMT
Title`, CMPM_Grouping.`Work Item`, CMPM_Grouping.`Regular Hours`,
CMPM_Grouping.`Paid OT`, CMPM_Grouping.`Non-Paid OT`,
CMPM_Grouping.`Total Hours`
FROM `Z:\Admin DB`.CMPM_Grouping CMPM_Grouping

Can I make this more generic....so instead of indicating drive Z, any
user can execute regardless of drive letter they have used?

Thanks

 

[Thomas Hardy]

I'm linking Excel to an Access Database on a shared drive. I can open
the excel spreadsheet and update external data without problem. What
appears to be happening to others, is they are using differenent drive
letters to map to the same network location.

Linking through MS Query, my SQL is as follows:

SELECT CMPM_Grouping.Lead, CMPM_Grouping.`Path Type`,
CMPM_Grouping.Phase, CMPM_Grouping.`PMT ID`, CMPM_Grouping.`PMT
Title`, CMPM_Grouping.`Work Item`, CMPM_Grouping.`Regular Hours`,
CMPM_Grouping.`Paid OT`, CMPM_Grouping.`Non-Paid OT`,
CMPM_Grouping.`Total Hours`
FROM `Z:\Admin DB`.CMPM_Grouping CMPM_Grouping

Can I make this more generic....so instead of indicating drive Z, any
user can execute regardless of drive letter they have used?

Thanks

Open Windows Explorer, go to the Tools menu and select Disconnect Network
Drive.

You will see your Z drive and what it is mapped to; I would imagine you
could just replace the Z in your code with the full name.

The naming convention (displayed when you select the Map Network Drive
option) is \\server\share , so you might have something like

FROM `\\servername\sharedfoldername\Admin DB`.CMPM_Grouping CMPM_Grouping