By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

Excel: query can only be refreshed if your using z: as your drive header

atljpe
P: 11
In Excel: My query can only be refreshed if your using z: as your drive header. I want to change it so anyone can refresh this query wether they're using x, y, or z for their drive header:

The company I'm working with uses shared drives such as 'Sabntfilp001', each individual can have the same shared drive but may have different letters in front such as x:\Sabntfilp001\ or z:\Sabntfilp001\. The query will only recognize one or the other. This is the query:

Expand|Select|Wrap|Line Numbers
  1. ..Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data 
  2. Source=z:\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls;Mode=ReadWrite;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database 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 OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False..
I've tried hard coding the actual drive into the Source (this worked with using VBA) but this didn't work.
This is my second time using a query so I not familiar with proper terminology so I hope my question is clear

I wasn't sure wich forum this should be under...please move this thread if need be

Thanks
atljpe
May 7 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Aside from the drive which can be different from computer to computer, all shared network "drives" should have a name that you can use.

In your network drives folder, you'll see something in the format of:
Foldername on 'Network Drive Description (Network Drive Absolute Path)' (X: )
That Network Drive Absolute Path is what you want to use, just prefix it with \\.

So Music on 'Rabbits Music (RMusic)' (L: ) can be accessed using \\RMusic
May 7 '07 #2

atljpe
P: 11
I'm not doing something(s) right.

Actual Network drives shows this from my computer: Opr on 'Sabntfilp001'( Z: )

Current query source:
Source=z:\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

Based on suggestion I tried these variations:

\\sabntfilp001\opr\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

\\Opr\sabntfilp001\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

\\sabntfilp001\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

please help

Thanks atljpe
May 8 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
I'm not doing something(s) right.

Actual Network drives shows this from my computer: Opr on 'Sabntfilp001'( Z: )

Current query source:
Source=z:\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

Based on suggestion I tried these variations:

\\sabntfilp001\opr\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

\\Opr\sabntfilp001\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

\\sabntfilp001\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

please help

Thanks atljpe
Source=\\sabntfilp001\oprPowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

That should work. If not try relinking it using the network path rather than using the drive header.
May 8 '07 #4

atljpe
P: 11
I didn't know it but I was disconnected from the drive when I tried this before so it couldn't work.

This one I previously tried does work:
\\sabntfilp001\opr\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

Thank you Rabbit for all your help

Satisfied once again with "The Scripts" knowledge base

:)
Brgds atljpe
May 8 '07 #5

Rabbit
Expert Mod 10K+
P: 12,366
I didn't know it but I was disconnected from the drive when I tried this before so it couldn't work.

This one I previously tried does work:
\\sabntfilp001\opr\PowderRiverDiv\ALLIANCE V2 PROJECT\Hostler Worksheet\Hostler Tracking Sheet.xls

Thank you Rabbit for all your help

Satisfied once again with "The Scripts" knowledge base

:)
Brgds atljpe
Not a problem, glad to help.
May 8 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.