DB connection using relative drive mapping?

[dnstapes]

I need to link my Access DB to an Excel spreadsheet. The problem is that both the database and the spreadsheet reside on a network drive. It is highly possible that people may have this network drive mapped to different drive letters. For example, I have this drive mapped to H: but I know the guy that sits next to me has it mapped as J: and yet another guy has it mapped to G:

I've tried to get everyone to agree to a standard drive letter for this drive, but no one wants to change, and the PC support guys aren't helping either. There are about 100 potential users of this app, and several different drive mappings I'm sure.

So I guess what I'd like to know is if it's possible to create a data source in Excel that connects to a network drive, but doesn't use a specific letter to connect. in other words, something like //computer/directory/myDB.mdb instead of H:/directory/myDB.mdb

Any help would be appreciated.

Thanks,
Dana

 

[xlbo]

Just use your "Network Neigbourhood" path - ie the full path from the server. I have a drive mapped as W: which is "Knowledge"
in actual terms, it is:
Yorkshire\Shared\Knowledge, where "Yorkshire" is the server name
So, if I had a file on W:\Knowledge\Geoffs Stuff\Excel, the ACTUAL path to use would be
Yorkshire\Shared\Knowledge\Geoffs Stuff\Excel

It's similar to relative and absolute referencing in excel - the full path will never change, no matter who's logon / drive mappings are used.
You can get the full path by going to Windows Explorer, Tools>Map Network Drive.
Select the drive from the drop down list and you should see
\\ServerName\folder\folder\folder and that'll be what your H drive ACTUALLY is
Rgds
~Geoff~

 

[dnstapes]

Thanks for the response. I understand how relative and absolute addressing work, but how do I set up Excel so that it uses relative addressing? Currently, I'm grabbing data from Access by using Data->Get External Data->Create New Query. This asks for a data source. If I click on new data source, it eventually asks me to locate my Access database. When I enter in a path, it requires me to map to a drive letter. There is an option to use no drive letter, but I've tried this several times, and I can't get it to work at all.

Is there another way to connect to a database using VBA that would allow me to use relative addressing?

Thanks,
Dana

 

[xlbo]

Hi Dana - set up the query on your mapping, then go to the .DQY file and open it in notepad or something. there is a string that indicates the database location....change this from a relative mapping to an absolute reference ie \\ServerName\Folder............ etc etc and save the .DQY file - it should now use the full NN address and be safe from different mappings Rgds
~Geoff~