Link to home
Start Free TrialLog in
Avatar of Bill Ross
Bill RossFlag for United States of America

asked on

Dir function fails on mapped drives

Recently this function in Access 2007 32-bit, WIN7 64-bit has started to fail.

Dir("F:\",vbDirectory)

returns a null string even though the mapped drive exists.

Any idea why?
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

I should return a dot.
Are you sure, the drive letter hasn't been unmapped?

/gustav
Drive mapping has timeout in registry. The mapping definition still exists but the drive is disconnected. Windows are able to reconnect the drive when you click on it in e.g. Windows Explorer but other (custom) applications would need some code to activate the mapped disks.

But you may extend the timeout to rather large value.
Details how to do it are e.g. here: http://www.nextofwindows.com/how-to-fix-the-red-x-on-the-icon-of-mapped-network-drives-in-windows-7
Avatar of Bill Ross

ASKER

I can actually open a file in the mapped drive and the function still fails.  No broken references.  
?len(Dir("F:\",vbDirectory))

returns 0
More - created blank DB and same issue.  Maybe a reinstall of AC2007 will correct.  I'll check back.
Reinstalled 2007 no help.  I'm stuck.  Could this have anything to do with Office365?  Recent upgrade to Office 2016 but still running Access 2007.  ???
I don't think so. We run Office365 and 2016 desktop versions.

Could you attach a demo showing the issue?

/gustav
one question: Is the user that mapped the drive and the username that runs the dir function the same? Also is their elevation level the same? Or one is running as administrator?
The users are the same me...  I am attaching a screen shot that might show the issue.  Blank database, immediate window + windows Explorer.  Thanks for helping...
ScreenShot.png
Returns 1 here.

/gustav
Interesting issue...

Does the dir() work when you provide UNC path? E.g. the F: is mapped as \\SomeComputer\SomeShare

What returns
Dir("\\SomeComputer\SomeShare", vbDirectory)

and also following options:
Dir("\\SomeComputer\SomeShare\", vbDirectory)
Dir("\\SomeComputer\SomeShare\*.*", vbDirectory)
Dir("F:\*.*", vbDirectory)

and also
Dir("C:\", vbDirectory)

If nothing helps then I have to say MS Access 2007 is unsupported product. :-(
From the screenshot, it appeared that the drive letter is mapped to the computername root, not a subfolder. What happens when you get list of f:\SomeDir or when you map f: to some subfolder like \\DELLXPS\Db\Test
Thanks for the help.  The following works:
len(dir("\\DELLXPS\DriveS\",vbDirectory)) = 1
len(dir("\\DELLXPS\DriveS\*.*",vbDirectory)) = 1

where DriveS is the mapped folder as share S

These still fail:
len(dir("S:\",vbDirectory)) = 0
len(dir("S:\*.*",vbDirectory)) = 0

BTW - I think this worked until I upgraded to Office 2016...  Not sure as it just showed up recently but I might not have noticed for a long time.

Bill
I run Access 2016 and can't reproduce your issue.

However, this is a "clean" Windows environment (AD, Windows servers only).
Which OS is running on your DELLXPS?

/gustav
Windows 7 Pro is the DELLXPS computer with the shared drives.  Development computer has Office 2016 upgraded from Office 2013 except I run Access 2007 for development - no Access 2010/2013/2016 is installed.  I do this as some of my clients still have 2007.
Nothing particular I can see. I'm out of ideas.

/gustav
Strange.  Anybody have anything I can check?
You have several options:
1) Use UNC paths instead of mapped drive letter
2) Upgrade Access 2007
3) Uninstall Office 2016

The UNC path use is probably the most simple solution. The proposed solution is to upgrade your Access 2007 to some newer version. I am not sure Office 2016 uninstalling will help because the product uninstallation can leave some files in the OS. These two products are simply incompatible on the given OS and Access 2007 is in Extended support period which means "just security patches" so the probability of the fix is very low.

If you are sure the problem started with Office 2016 then ask Microsoft to fix Office 2016 but you cannot expect some quick action... Microsoft is happy when users are upgrading...
This KB article describes how to obtain the UNC from drive letter: https://support.microsoft.com/en-us/kb/160529
It should be easy to port it to MS Access 2007.
Well it is really strange still.  Using UNC is not an option as that changes from computer to computer.  I may upgrade to Access 2010.  For now, I'll just work around it.

Thanks for all your help.
I'm not quite sure how to cloase this question as there is no solution but others may get this issue...
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi pcelba,

You're correct that relying on a mapped drive is bad design but sometimes we've got to live with what we have.  The application has worked for many years and only recently this became an issue.  I could redesign the process but...

Still - the issue is that the Dir function is failing but it's also worked for years.
Hi gustav,

Looks promising.

I'll give that a try but still wondering why Dir fails...

Thanks,

Bill
"still wondering why Dir fails" ... We cannot know why the particular bug is in the software we did not write...

You are talking about Microsoft products. Some of them are still supported. Did you ask Microsoft already?  What did they say?

You may wait for answer from Microsoft (or hotfix) or use the work around. Both the link in my answer or code in Gustav's answer should work. The final decision is on you.
Thanks for the help!!!
You are welcome!
Should you find out something definitive, please report back.

/gustav
Thanks for your patience :-)

Another way how to obtain the share name is FSO:
Dim FSO As FileSystemObject
Dim drvObj As Drive
Set FSO = New FileSystemObject
Set drvObj = FSO.GetDrive("X:")
Debug.Print drvObj.ShareName

Open in new window