Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder

[Jan T]

I want to connect to a database file on a shared network folder from
Excel using either DAO or ADO.

My experience, is that some user could not connect sometimes because
their system changed the path like: - sometimes mappping like; S://SharedFolder
sometimes T://SharedFolder?

How can I avoid missing the path to the db I want to connect to from
Excel using vba?

Thank you for any help!

Regards Jan T.

 

[GS]

Jan T laid this down on his screen :

I want to connect to a database file on a shared network folder from
Excel using either DAO or ADO.

My experience, is that some user could not connect sometimes because
their system changed the path like: - sometimes mappping like;
S://SharedFolder sometimes T://SharedFolder?

How can I avoid missing the path to the db I want to connect to from
Excel using vba?

Thank you for any help!

Regards Jan T.

Normally, a path on a server is UNC, beginning with ""\\server\share\"
followed by the filename. I don't recommend using 'mapped' folders
because <AFAIK> these are locale-defined, and are only a redirect to a
'namespace' and NOT the absolute path to the actual folder.

You might have better luck storing the UNC path in the Registry via
'SaveSetting' so your project can retrieve it via 'GetSetting' when it
needs it.

Additionally, you could prompt the user to locate the db file if it's
not correct in the Registry so at first use (when/if the file gets
moved) your project resets the Registry value to the new location.

 

[Charlotte E.]

While I totally agree with Garry's answer, you could also let the VBA
search for the path, if using a drive-letter, for some reason, is needed.

Something like:

For Counter = Asc("D") to Asc("Z")
If Dir(Chr(Counter)&":\Sharefolder") <> "" then DriveLetter =
Chr(Counter)
Next

(Just made from back of my head - not tested)

CE


Den 29.01.2012 00:35, Jan T skrev:

 

[GS]

Charlotte E. laid this down on his screen :

While I totally agree with Garry's answer, you could also let the VBA search
for the path, if using a drive-letter, for some reason, is needed.

Something like:

For Counter = Asc("D") to Asc("Z")
If Dir(Chr(Counter)&":\Sharefolder") <> "" then DriveLetter =
Chr(Counter)
Next

(Just made from back of my head - not tested)

CE


Den 29.01.2012 00:35, Jan T skrev:

Charlotte,
That's a good suggestion for finding a specific folder on a local
drive!

<FYI>
What I've learned is that VB[A] can't work with 'mapped' drives on a
network because those are for use by the system only. What the system
does is to create a 'virtual namespace' as a 'pointer' to the actual
location on the network. In this case it's been easier to prompt the
user via the folder picker dialog when the stored path throws an error.

 

[Tim Williams]

<FYI>
What I've learned is that VB[A] can't work with 'mapped' drives on a
network because those are for use by the system only. What the system
does is to create a 'virtual namespace' as a 'pointer' to the actual
location on the network. In this case it's been easier to prompt the
user via the folder picker dialog when the stored path throws an error.

VBA *can* work fine with mapped drives, as long as the drive letter is
consistent.

If each user has a different drive letter mapped, then the UNC path
should be used instead.

Tim

 

[GS]

Tim Williams wrote on 1/30/2012 :

<FYI>
What I've learned is that VB[A] can't work with 'mapped' drives on a
network because those are for use by the system only. What the system
does is to create a 'virtual namespace' as a 'pointer' to the actual
location on the network. In this case it's been easier to prompt the
user via the folder picker dialog when the stored path throws an error.

VBA *can* work fine with mapped drives, as long as the drive letter is
consistent.

If each user has a different drive letter mapped, then the UNC path
should be used instead.

Tim

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Tim,
My mistake! I meant to ref 'mapped folders' in context. My experience
with *directly* referring to folders 'mapped' to UNC drives is that
they return the redirect folder NOT the UNC folder. Same behavior
occurs with drives since both are 'paths' to somewhere. If I'm wrong
I'd be happy to test any code you offer that shows otherwise. AFAIK,
anything other than an absolute path will not work in any programming
language when working with UNC paths over a network.

 

[Tim Williams]

Tim Williams wrote on 1/30/2012 :

<FYI>
What I've learned is that VB[A] can't work with 'mapped' drives on a
network because those are for use by the system only. What the system
does is to create a 'virtual namespace' as a 'pointer' to the actual
location on the network. In this case it's been easier to prompt the
user via the folder picker dialog when the stored path throws an error..

VBA *can* work fine with mapped drives, as long as the drive letter is
consistent.

If each user has a different drive letter mapped, then the UNC path
should be used instead.

Tim,
My mistake! I meant to ref 'mapped folders' in context. My experience
with *directly* referring to folders 'mapped' to UNC drives is that
they return the redirect folder NOT the UNC folder. Same behavior
occurs with drives since both are 'paths' to somewhere. If I'm wrong
I'd be happy to test any code you offer that shows otherwise. AFAIK,
anything other than an absolute path will not work in any programming
language when working with UNC paths over a network.

If I map a local drive Z to \\someserver\somefolder I can do this:

Debug.Print Dir("Z:\")

or this:

Debug.Print Dir("\\someserver\somefolder\")

and I get the same result. Maybe we're talking at cross-purposes here
- do you have an example which doesn't work like this?

Tim

 

[GS]

Tim Williams used his keyboard to write :

If I map a local drive Z to \\someserver\somefolder I can do this:

Debug.Print Dir("Z:\")

or this:

Debug.Print Dir("\\someserver\somefolder\")

and I get the same result. Maybe we're talking at cross-purposes here
- do you have an example which doesn't work like this?

We might very well not be on the same page here!

I was thinking of the situation where I was using a Windows Explorer
FolderView method to return a path and whenever I selected a 'mapped'
*folder* it returned the redirect folder rather than the absolute path.

I'm sure you are correct about a local drive as explained, and so we
are not on the same page regarding 'mapped' network folders (not as a
local drive) as represented in Windows Explorer's folderview list under
'My Network Places'. IIRC, these folders are 'namespaces' that the
system redirects via a '.lnk' to the absolute path. We can't use the
value returned by the namespace as a valid path. The term that applies
here is 'AbsolutePIDL' as pertains to Windows file system. There's no
easy way to get the target of the link without jumping through several
API hoops to get there.

I opted to use a 3rd party OCX replacement for WE that promised all
kinds of customization. Awesome tool but duplicated what WE did to a
'tee'! When I explained the problem to the author of the FolderView.ocx
he understood exactly what I meant but stated that his ocx duplicates
what Windows Explorer does. I replied that this is exactly why I was
using his control since it was advertised as being identical to WE
*'plus'* fully customizeable. Since this was used in a proprietary file
manager app, he was gracious enough to upgrade the ocx by adding an
additional method to always return an AbsolutePIDL to an additional
'AbsolutePath' property so I could access it more easily. This allowed
me to continue using the ocx with my project. What was interesting is
that both WE Fileview and his Fileview.ocx displayed the actual folder
contents which, I suspect, is because his tool did in fact duplicate WE
in 'out-of-the-box' configuration. Explains why/how he knew exactly
what to do to give me what I was looking for.

 

[Tim Williams]

Tim Williams used his keyboard to write :






We might very well not be on the same page here!

I was thinking of the situation where I was using a Windows Explorer
FolderView method to return a path and whenever I selected a 'mapped'
*folder* it returned the redirect folder rather than the absolute path.

I'm sure you are correct about a local drive as explained, and so we
are not on the same page regarding 'mapped' network folders (not as a
local drive) as represented in Windows Explorer's folderview list under
'My Network Places'. IIRC, these folders are 'namespaces' that the
system redirects via a '.lnk' to the absolute path. We can't use the
value returned by the namespace as a valid path. The term that applies
here is 'AbsolutePIDL' as pertains to Windows file system. There's no
easy way to get the target of the link without jumping through several
API hoops to get there.

I opted to use a 3rd party OCX replacement for WE that promised all
kinds of customization. Awesome tool but duplicated what WE did to a
'tee'! When I explained the problem to the author of the FolderView.ocx
he understood exactly what I meant but stated that his ocx duplicates
what Windows Explorer does. I replied that this is exactly why I was
using his control since it was advertised as being identical to WE
*'plus'* fully customizeable. Since this was used in a proprietary file
manager app, he was gracious enough to upgrade the ocx by adding an
additional method to always return an AbsolutePIDL to an additional
'AbsolutePath' property so I could access it more easily. This allowed
me to continue using the ocx with my project. What was interesting is
that both WE Fileview and his Fileview.ocx displayed the actual folder
contents which, I suspect, is because his tool did in fact duplicate WE
in 'out-of-the-box' configuration. Explains why/how he knew exactly
what to do to give me what I was looking for.

I see. That's not something I've run into, but typically I'm not
asking users to browse the full network for files/folders.

Tim

 

[GS]

It happens that Tim Williams formulated :

I see. That's not something I've run into, but typically I'm not
asking users to browse the full network for files/folders.

Tim

Yeah.., I can relate.

My experience results from making a special file manager app for
working CNC machine program files over a network. In some cases the
machines were connected directly to the network. My solution needed to
service every possible scenario for getting at the files wherever they
happened to be located. The version that has the built-in explorer is a
VB6.exe (about to be released as a C++.exe since the vbruntime is being
deprecated next Windows version). The Excel VBA version uses the
FolderPicker dialog as the solution via error trapping when a user
selects mapped folders. The AbsolutePath is stored on the worksheet in
both versions. (The VB6.exe also uses its own 3rd party
spreadsheet.ocx)