VBA MapNetworkDrive to server with windows credentials

Question

I'm using windows 7 and Access 2010. I'm trying to connect to our server to see files. My problem is that I don't want to have the username or password in the code. I would like to have windows to prompt for it if authentication needed (from my tests, once you authenticate you don't have to re-do it for a while). From MapNetworkDrive Object MSDN, strUser and strPassword is optional and uses current users credentials, but this doesn't seem to work for a server.

Dim objFSO As Object
Dim objFolder As Object
Dim objNetwork As Object
Dim strShareLetter As String
Dim strURL As String, strUser as String, strPassword as String
strShareLetter = "L:"


Set objFSO = CreateObject("Scripting.FileSystemObject")
strURL = "\\company@SSL\DavWWWRoot\companydav\nodes\12345678\"

Set objNetwork = CreateObject("WScript.Network")
objNetwork.MapNetworkDrive strShareLetter, strURL, False, strUser, strPassword

Set objFolder = objFSO.GetFolder(strURL)

MsgBox objFolder.Files.Count
MsgBox objFolder.SubFolders.Count

Set objFolder = Nothing
Set objFSO = Nothing

objNetwork.RemoveNetworkDrive (strShareLetter)
Set objNetwork = Nothing

If I write my user/password in the code everything works. But I need other users to be able to use it and I don't want my credentials being used everywhere. I would like a windows credentials prompt or something along that way. I don't want to prompt for the user password via vba for security reasons.

Any ideas? I searched google for solutions but I can't seem to find one. thank you

Answer 1

It sounds like you've already recognized that you need some sort of user form in your project to collect the user's credentials.

Here's how I would do that. I'd insert a User-Form from the VBA editor. From there I'd grab a couple of text boxes and labels to it, label one box as "User ID" and the other as "Password." For the password text box I'd be sure to insert "*" in the PasswordChar property to mask the password. I'd also put a button on there that says "Submit" or "Log In" or something to that effect.

Once you've done that, You'll need a couple of properties:

Private pUserId As String
Private pPassword As String 

Public Property Let UserId(value as string)
    pUserId = value
End Property

Public Property Get UserId() as string
    UserId = pUserId
End Property

Public Property Let Password(value as string)
    pPassword = value
End Property

Public Property Get Password() As String
    Password = pPassword
End Property 

Then you will want to use your log in button's click event to load the information from the text boxes into the properties and hide the form.

Private Sub CommandButton1_Click()
    UserId = TextBox1.text
    Password = TextBox2.text
    Me.hide
End Sub

Then all you have to do is dim and display the user form to the user.

...
Set objNetwork = CreateObject("WScript.Network")
Dim LogInForm As UserForm1
Set LogInForm = New UserForm1
LogInForm.Show vbModal
objNetwork.MapNetworkDrive strShareLetter, strURL, False, LogInForm.UserId, LogInForm.Password
...

And that's pretty much it. You might need to learn how to create user forms in VBA or brush up on that.