Recipe 11.14 Create and Cancel Network Connections Programmatically

11.14.1 Problem

You'd like to be able to connect to remote network devices from within your own Access applications. You know that you could do this manually, using Explorer or File Manager, but there must be some internal API for controlling these connections. Is there some way you can manage connections from within Access?

11.14.2 Solution

Windows provides a rich interface to its networking subsystem through its API. Many of the function calls are difficult, if not impossible, to call from VBA because of the language's lack of pointer variable types. Some important calls, however, are quite simple to use, as you'll see in this solution. The example form will demonstrate connecting to and disconnecting from remote devices (printers and drives) using common dialogs or using code with no user interface.

Load and run frmNetworkSample from 11-14.MDB. Figure 11-16 shows the form in use on a small Windows 2000 network. This sample form, demonstrating all the capabilities covered in this solution, does the following:

  • Retrieves the current username and computer name.

  • Walks through all 26 possible drive letters and displays any drive mappings connected to those drives.

  • Allows you to delete any of the displayed drive connections.

  • Provides a method for adding new connections, where you supply the four necessary parameters.

  • Uses the common dialogs for adding and canceling drive and printer connections.

Figure 11-16. frmNetworkSample allows you to add and cancel connections manually or by using the common dialogs

Though you would never use this exact form in an application, it allows you to experiment with all the functionality covered in this solution. To use these API calls in your own applications, follow these steps:

  1. Import the module basNetwork from 11-14.MDB. This module contains all the API function declarations, wrapper functions, data type declarations, and error constants you'll need.

  2. The sample form, frmNetworkSample, displays the current username. To retrieve this information in your own code, call the acbGetUser function from basNetwork. Its return value is the name of the currently logged-in user. For example:

    Debug.Print acbGetUser( )
  3. The sample form also displays the current computer name. To retrieve this information yourself, call the acbGetComputerName function from basNetwork. Its return value is the name of the current computer. For example:

    Debug.Print acbGetComputerName( )
  4. The list box on the form displays all the current connections. You can choose one and delete it (see Step 5). To retrieve a list of all 26 possible drives and their connections in your own application, call acbListConnections, a function that takes as a parameter an array of 26 acbConnectionInfo structures. The following example fills the list with drive information, then prints it out to the Immediate window:

    Dim aci(0 To 25) As acbConnectionInfo
    intCount = acbListDriveConnections(aci( ))
    For intI = 0 To intCount
       Debug.Print aci(intCount).strDrive, aci(intCount).strConnection
    Next intI
  5. To delete a drive connection once you've selected a drive from the list box, click on the Delete button to the right of the drive list box. When you do, the code calls the acbCancelConnections function, deleting the connection for the drive selected in the list box:

    blnOK = (acbCancelConnection(Me.lstConnections.Column(0), True) = 0)
  6. To manually add a new printer or drive connection, first select Printer or Drive from the option group on the form, then enter the four pieces of information that the acbAddDriveConnection and acbAddPrintConnection functions need: local name (e.g., "LPT1:"), remote name (e.g., "\\GATEWAY\HPLJ4"), username, and password. The remote name is the only required value. Once you've entered the values, click on the Add button to the right of the text boxes. This calls the following code:

    If Me.grpDeviceType = 1 Then
          ' The '& ""' below converts from null values to strings.
          ' Drive
          blnOK = (acbAddDriveConnection(Me.txtLocalName & "", _
             Me.txtRemoteName & "", Me.txtUserName & "", Me.txtPassword & "") = 0)
          ' Printer
          blnOK = (acbAddPrinterConnection(Me.txtLocalName & "", _
             Me.txtRemoteName & "", Me.txtUserName & "", Me.txtPassword & "") = 0)
       End If
    End If
  7. To use the common dialogs for adding or canceling connections, click on any of the four buttons at the bottom of the form. Each calls a single line of Windows API code that pops up the appropriate dialog. The next section describes these function calls in detail.

11.14.3 Discussion

The following sections describe all you need to know to use the networking functionality demonstrated on the sample form. Though you could call the API functions directly, in each case we've provided a wrapper function to shield you from as much detail as possible. For each of the various wrapper functions, we provide information on how to call them, what parameters to send, and what values to expect back.

Most of the functions either return or set an error value, indicating the outcome of the function call. Though there are too many possible errors to list them all here, Table 11-15 lists most of the common ones that you'll receive when making these function calls.

Table 11-15. Common networking errors






No error occurred.



Access is denied.



The network resource type is not correct.



The network name cannot be found.



The local device name is already in use.



The specified network password is not correct.



The requested resource is in use.



More data is available.



The specified device name is invalid.



The device is not currently connected, but it is a remembered connection.



An attempt was made to remember a device that had previously been remembered.



No network provider accepted the given network path.



The specified network provider name is invalid.



Unable to open the network connection profile.



The network connection profile is corrupt.



An extended error has occurred.



The network is not present or not started.



The user canceled a dialog.



This network connection does not exist. Retrieving information

To retrieve the current user's name, call the acbGetUser function:

Public Function acbGetUser(Optional varErr As Variant) As String

   Dim strBuffer As String
   Dim lngRetval As Long
   Dim lngSize As Long
   lngSize = conMaxPath
      strBuffer = Space(lngSize)
      lngRetval = WNetGetUser(0&, strBuffer, lngSize)
   Loop Until lngRetval <> ERROR_MORE_DATA
   If lngRetval <> NO_ERROR Then
      acbGetUser = ""
      acbGetUser = TrimNull(strBuffer)
   End If
   varErr = lngRetval
End Function

The acbGetUser function calls the Windows API to retrieve the currently logged-in user's name. Note that there are several ways for the Windows API and Access to communicate the length of data to be returned. In this case, the code sets up a buffer of arbitrary length and calls the Windows API. If the buffer was large enough, it fills it in with the requested name. If not, it returns the value ERROR_MORE_DATA, indicating that it needs more space. It then passes back in the lngSize variable the actual number of characters it does need, and the code loops around, trying again with the specified size.

If you want to know the exact error that occurred in the attempt to retrieve the current user's name, you can pass a variant variable in as a parameter to acbGetUser. It's optional, but if you supply the value, the function will pass back the error code to you in that variable. For example:

Dim varErr as Variant
' If you care about the error:
Debug.Print acbGetUser(varErr)
Debug.Print "The error was: "; varError
' If you don't care about any errors:
Debug.Print acbGetUser( )

To retrieve the current computer's name, call the acbGetComputerName wrapper function. Windows stores the current computer's name in the registry database and reads it from there when necessary. To shield your code from having to know exactly where that piece of information is stored, Windows provides the GetComputerName API function.

The following function, acbGetComputerName, handles the passing of data between Access and Windows for you:

Public Function acbGetComputerName( ) As String

   ' Retrieve the network name of the current computer.
   Dim strBuffer As String
   Dim lngSize As Long
   Dim blnOK As Integer
   lngSize = conMaxComputerNameLength+ 1
   strBuffer = Space(lngSize)
   blnOK = GetComputerName(strBuffer, lngSize)
   acbGetComputerName = Left$(strBuffer, lngSize)
End Function

Note that in this case, the API function gives you no second chance. If the buffer wasn't large enough, it just returns as much as it could fit into the buffer you passed.

To retrieve the name of the remote device connected to a named local device, call the acbGetConnection function. Pass to it the local device name and an optional variable in which to receive the error code. It will return to you the remote device name connected to the requested local name. For example:

Debug.Print acbGetConnection("LPT1:")

might return a value like this (a \\server\share name):


The function works the same way for drive connections.

The acbGetConnection function works the same way as the acbGetUser function: it calls the API function once with an arbitrarily sized buffer. If that isn't enough room, it'll try again with the buffer resized to fit. Its source code is:

Public Function acbGetConnection( _
 strLocalName As String, Optional varErr As Variant) As String

   Dim strBuffer As String
   Dim lngRetval As Long
   Dim lngSize As Long
   lngSize = acbcMaxPath
      strBuffer = Space(lngSize)
      lngRetval = WNetGetConnection(strLocalName, strBuffer, lngSize)
   Loop Until lngRetval <> ERROR_MORE_DATA
   If lngRetval <> NO_ERROR Then
      acbGetConnection = ""
      acbGetConnection = TrimNull(strBuffer)
   End If
   varErr = lngRetval
End Function Adding and canceling connections using common dialogs

Adding or canceling a connection with a common dialog in Windows is easy: just make a single function call, as shown in Table 11-16. Each wrapper function expects a single parameter: a window handle for the parent of the dialog window. Most of the time, this will just be Me.hWnd or Screen.ActiveForm.hWnd.

Table 11-16. Wrapper functions for common dialog connections

Function name



Add a drive connection.


Cancel a drive connection.


Add a printer connection.


Cancel a printer connection.

For example, to pop up the common drive connection dialog, you'd call:

blnOK = acbConnectDriveDialog(Me.hWnd)

The code in each of the wrapper functions is similar and quite trivial. In each case, the code just calls a single Windows API function. We've provided the wrappers only to provide a consistent interface for all the API functions; there's no real reason not to call the API functions directly, except for a tiny bit of convenience. For example, the acbConnectPrintDialog function looks like this:

Public Function acbConnectPrintDialog(hWnd As Long) As Long
   ' Use the common print connection dialog to create a new connection.
   acbConnectPrintDialog = WNetConnectionDialog(hWnd, RESOURCETYPE_PRINT)
End Function Adding and canceling connections with no user intervention

Adding or canceling a connection "silently" requires a bit more work, but it's not a problem. Table 11-17 lists the available wrapper functions, and the information they require.

Table 11-17. Functions to manually add and cancel connections

Function name





Add a drive connection.

strLocalName As String

Local name, like "LPT1:" or "G:".


strRemoteName As String

Remote name, like "\\SERVER\SHARE".


strUserName As String

Username to be used. If empty, uses default user's name.


strPassword As String

Password for the user specified. If empty, uses the default user's password.


Add a printer connection.

strLocalName As String, strRemoteName As String, strUserName As String, strPassword As String

See parameters for acbAddDriveConnection.


Cancel any connection.

strLocalName As String

Local name of resource to disconnect.


blnForce As Boolean

If True, forces disconnection even if the device is in use. If False, the function returns an error if it tries to disconnect an active device.

For example, the following code fragment adds a new printer connection for LPT2: to the CanonColor printer on server Bart, set up for the current user and password:

blnOK = acbAddPrintConnection("LPT2:", "\\BART\CanonColor", "", "")

Each of these functions will return an error value (NO_ERROR (0)) if there was no error, or return some other error from Table 11-15 if an error occurs. Functions that add connections call the private function AddConnection, which in turn calls the Windows API to create that connection, as shown here:

Public Function acbAddDriveConnection( _
 strLocalName As String, strRemoteName As String, _
 strUserName As String, strPassword As String)
   acbAddDriveConnection = AddConnection( _
    RESOURCETYPE_DISK, strLocalName, _
    strRemoteName, strUserName, strPassword)
End Function

Private Function AddConnection(intType As Integer, _
 strLocalName As String, strRemoteName As String, _
 strUserName As String, strPassword As String)
   ' Internal function, provided for adding new connections.
   ' Call acbAddPrinterConnection or acbAddDriveConnection instead.
   Dim lngRetval As Long
   nr.lpLocalName = strLocalName
   nr.lpRemoteName = strRemoteName
   nr.dwType = intType
   lngRetval = WNetAddConnection2(nr, strPassword, _
   AddConnection = lngRetval
End Function

The acbCancelConnection function is simple. It calls directly to the Windows API, canceling the connection for the named local device:

Public Function acbCancelConnection( _
 strName As String, blnForce As Boolean) As Long
   acbCancelConnection = WNetCancelConnection2( _
    strName, CONNECT_UPDATE_PROFILE, blnForce)
End Function

You may find it interesting to work through all the code in basNetwork. There are some interesting twists involved in transferring information between Access and the Windows API, especially since it seems that every API function that involves strings uses a different mechanism for indicating how much space it needs.

It would be useful to have a function that could enumerate all network resources, and of course Windows itself provides functions to do this. Unfortunately, calling these functions from Access requires a great deal of effort, because VBA just doesn't support the necessary mechanisms (specifically, pointers) to make it possible. It's possible, but it's beyond the scope of this book.