Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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)
   Else
   ' 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.

 NO_ERROR

 ERROR_ACCESS_DENIED

 ERROR_BAD_DEV_TYPE

 ERROR_BAD_NET_NAME

 ERROR_ALREADY_ASSIGNED

 ERROR_INVALID_PASSWORD

 ERROR_BUSY

 ERROR_MORE_DATA

 ERROR_BAD_DEVICE

 ERROR_CONNECTION_UNAVAIL

 ERROR_DEVICE_ALREADY_REMEMBERED

 ERROR_NO_NET_OR_BAD_PATH

 ERROR_BAD_PROVIDER

 ERROR_CANNOT_OPEN_PROFILE

 ERROR_BAD_PROFILE

 ERROR_EXTENDED_ERROR

 ERROR_NO_NETWORK

 ERROR_CANCELED

 ERROR_NOT_CONNECTED

11.14.3.1 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
Do
strBuffer = Space(lngSize)
lngRetval = WNetGetUser(0&, strBuffer, lngSize)
Loop Until lngRetval <> ERROR_MORE_DATA
If lngRetval <> NO_ERROR Then
acbGetUser = "
Else
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):

\\WOMBAT\HPLJ4

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
Do
strBuffer = Space(lngSize)
lngRetval = WNetGetConnection(strLocalName, strBuffer, lngSize)
Loop Until lngRetval <> ERROR_MORE_DATA
If lngRetval <> NO_ERROR Then
acbGetConnection = "
Else
acbGetConnection = TrimNull(strBuffer)
End If
varErr = lngRetval
End Function

11.14.3.2 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.

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

11.14.3.3 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.

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 nr As NETRESOURCE
Dim lngRetval As Long
nr.lpLocalName = strLocalName
nr.lpRemoteName = strRemoteName
nr.dwType = intType
lngRetval = WNetAddConnection2(nr, strPassword, _
strUserName, CONNECT_UPDATE_PROFILE)
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.