Connecting to a network drive via Access VBA 1

[DreamerZ]

I have an Access form that runs some Excel Code that pulls data from a DB on another network drive protected by a user name and password (the reasons for this setup are very convoluted and silly, but be secure in the knowledge that I cannot do anything about it. It's big company logic!).

I have a local PC that opens the Access DB form automatically. I need to ensure, however, that I am connected to the network drives before opening the Excel files. Essentially, I want to "Map a Network Drive" via Access code.

Possible? If so, how? OR, can this be done with Excel?

TIA


DreamerZ

 

[earthandfire]

Off hand I don't know what the code would be (it would probably require an API call), but you could try using the DOS command NET USE DriveLetter: \\ServerName\ShareName.

Something like Shell(cmd.exe /c NET USE etc etc), I think.

The easiest way would be to this would be to map the drive, or to have a batch file that you could execute.

Hope this helps.

 

[PHV]

Something like this ?
Dim WN As Object
Set WNetwork = CreateObject("WScript.Network")
WN.MapNetworkDrive "X:", "\\Server\Public", , "User name", "Password"
Set WN = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

I think that might just work, PHV .

I never think of Script solutions - if I can't solve a problem directly in VBA, I tend to find the hardest alternative.

 

[Blorf]

Er, Set WNetwork maybe should be Set WN?


ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[DreamerZ]

As usual, PHV, you've come through. How do I check for an existing connection though? The code errors if the connection already exists. Guess I could disconnect the mapped drive at then end. Tried WN.Disconnect and WN.DisconnectNetworkDrive

Neither worked. There a command for that too?


DreamerZ

 

[PHV]

Dim WN As Object
Set WN = CreateObject("WScript.Network")
WN.MapNetworkDrive "X:", "\\Server\Public", , "User name", "Password"
' your stuff here
WN.RemoveNetworkDrive "X:", True
Set WN = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DreamerZ]

heh. Didn't try "Remove."

Thanks!


DreamerZ