Access network Drive Delay

stephenlane80

Good afternoon lads and lasses,

I have spent he last day tearing my hair out ove this one,

On paper it is a very simple problem:

1.) map network drive
2.) copy files from the drive
3.) close network connection

Well this is not so easy as it seems in Access (VBA) when the network drive has a different username and password and the client pc.

I am able to open and close the network drive fine, but when i try to copy the files while it is mapped it doesnt work, but i get no error.

I have used both a FileScriptingObject and the windows API to connect to the network drive, both work, but both take too long to allow me to copy the files. I have used the windows api to check the mapped drives status before i copy the file and it says the drive is ready but is obviously isnt.

Interestingly if i run the code immediately afterwards and for subsequent runs it works fine.

The problem only arises when i log in for the first time or when i try a new drive.

Here is the code, this example uses the windows API which i have a wrapper in a module called NetAPI:

Dim result As Long
result = NetAPI.Connect(cadDirDrive, machine, User, Password)

'path to cad files
cadDir = cadDirDrive & cadFolder

'Check if cad file exists then import cad data
If NetAPI.DriveStatus(cadDirDrive) = DRIVE_READY Then
'MsgBox NetAPI.DriveStatus(cadDirDrive)
    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("Del_CAD_Final")
    DoCmd.SetWarnings True
    UpdateCadData cadDir
Else
'try again
    MsgBox "Drive Not Ready, Please Try Again"
    Exit Function
End If

NetAPI.Disconnect cadDirDrive

The funny thing is that it never gets to the messgae box error no matter when i run it,

Any body else had this problem ?

Martyr

can you see what's happening on the network using wireshark?

windows might be searching for a domain controller to authenticate with.
wireshark would definitely give you a better idea than we could without the full code

Ginger

Silly question.. why dont you just use UNC paths??? No need to map drives at all

Also if you add a DoEvents just after the connect it should allow your code to catch up to the return values...

stephenlane80

Martyr wrote: »

can you see what's happening on the network using wireshark?

Havent heard of this before i will check it out thanks

@Ginger: I need to map the drive before i use UNC paths cause the pc with the share is not on the same domain as the client. This means that the connection has to be authenticated every time the client logs in.

I wish i saw this yesterday, becaue in the meantime i figured out the DoEvents function Doh !!

I create a do while loop with DoEvents, interestingly it only works when i call DoEvents before i map the drive ? doenst make sense to me !

stephenlane80

Appologies for bumping an old thread but for completeness sake i have found a good solution for this problem:

Create a process handle using the CreateProcessA windows API function, then map the network drive with that process handle: "net use x: \\computer name\share name"

Then use WaitForSingleObject() also form the windows API to wait until that process has finished before proceeding.

Example using calc.exe instead of "net use ...." :

http://support.microsoft.com/kb/178116

Seth Brundle

would the Dir() function looking for a directory not have been easier here?

stephenlane80

The souution i posted last has sucked once i tested it a production envoirnment

Dir will just tell you if the Drive exists which isnt the problem,

The problem is that a drive is mapped using vba, but when i try to use it it isnt available.

If you map a network drive, then use Dir or a FileSystem object to check if it exists, both of them will falsely report that the drive has been successfully mapped, there seems to be a lag between it seeming avaliable and being actually available,

ive tried building a delay into the vba does not work, as the code being used to run the delay prevents the API from working, i tried using DoEvents also but it doesnt help

It seems like embedding a "map network drive" into vba is a no runner, if you need to use the drive straight away ?

Any one else come acroos this ?

Seth Brundle

Have you tried using WScript?

Set WshNetwork = CreateObject("WScript.Network")

On Error Resume Next
WshNetwork.RemoveNetworkDrive "L:"
On Error GoTo 0

WshNetwork.MapNetworkDrive "L:", "\\mydrive\share" 'HardDrive"

Set WshNetwork = Nothing

stephenlane80

hi thanks for the suggestion, ive tried this already

The three methods ive tried so far are;

.1) WshNetwork.MapNetworkDrive "L:", "\\mydrive\share" 'HardDrive"
.2) WNetAddConnection2A() from the windows API
.3) net use "L:", "\\mydrive\share" password /USERusername /persistent:no

all three seem to behave the same when called from vba, the drives get mapped but not in time to be accessed by the next step in the code

Ginger

I am tempted to say you should write the network access module in .NET and expose it as COM and then instiate it in your VBA code..

A bit of work but the network access stuff is a lot more robust in .NET.

stephenlane80

Ginger wrote: »

A bit of work but the network access stuff is a lot more robust in .NET.

I like the sound of that, but im a bit hesitant adding a dependancy to the project, its nice when everything is contained in access, or accessable in the Windows API,

Having said that, id say i would be hard pushed to find a PC that doesnt at least have .Net 2 installed

stephenlane80

I would also have to register the DLL to access it wouldnt i, which would involve a setup script, just makes things more complicated

Delirium

Private Declare Function WNetAddConnection Lib "mpr.dll" Alias "WNetAddConnectionA" (ByVal lpszNetPath As String, ByVal lpszPassword As String, ByVal lpszLocalName As String) As LongPrivate 
 
Declare Function WNetCancelConnection Lib "mpr.dll" Alias "WNetCancelConnectionA" (ByVal lpszName As String, ByVal bForce As Long) As Long Private DisconnectIt As Long 
 
Private Sub cmdMapDrive_Click()
'Here [URL="file://\\vdk00005\proj"]\\vdk00005\proj[/URL] is the name of the network path
'H: is the Drive Letter
'Map Network Drive (Connect)
WNetAddConnection "[URL="file://\\vdk00005\proj"]\\vdk00005\proj[/URL]", "", "H:"
End Sub 
 
Private Sub cmdReNameFile_Click()
Name "Abc.Exe" As "abcYYYYMMDD.exe"
End Sub 
 
Private Sub cmdCopyFile_Click()
FileCopy "c:\abcYYYYMMDD.exe", "H:\abcYYYYMMDD.exe"
End Sub 
 
Private Sub cmdUnMapDrive_Click()
'Where H: is the drive letter you wish to connect
'The second parameter of this API determines whether to disconnect the drive if 
'there are files open on it.   If it is passed FALSE, the disconnect will fail if there are open files
'If it is passed TRUE, the disconnect will occur no matter what is open on the drive
DisconnectIt = WNetCancelConnection("H:", True)
End Sub

Found the code on this thread, http://www.vbforums.com/showthread.php?t=144263

Looks like it does what you want with the exception of the filecopy copying the file for the C drive to the mapped drive.

stephenlane80

Hi Koth

Thanks for the reply,

I am able to map a drive ok, but the problem is that the drive doesnt become available in time for me to use it, as the network seems quite slow,

if i loop and test to see if the connection exists, then windows tells me the drive is ready when it actually isnt,

S

MOH

stephenlane80 wrote: »

T

ive tried building a delay into the vba does not work, as the code being used to run the delay prevents the API from working, i tried using DoEvents also but it doesnt help

Have you tried using wscript.sleep for the delay?

stephenlane80

MOH wrote: »

Have you tried using wscript.sleep for the delay?

I havent tried that, i will rig up a vb script to map the drive with wscript.sleep built in to see if that works