Linking Excel to Access via 'New Database Query' on a shared network

  • Thread starter Thread starter dickharlo
  • Start date Start date
D

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
 
Use UNC path, it's always same.

\\servername\foldername\subfoldername if you have\file

That should be a rule when pointing to outside file of back end, you never
know, one day user might not get their drive mapped and your app would be
down, using UNC path it will not.

Amir
 
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
Click to expand...

use the UNC path to the file...
\\servername\directory\filename.xls
 
Back
Top