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?
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:
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. For example, Figure 11-17 shows the Disconnect Network Drive dialog.
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:
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.
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( )
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( )
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
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:
fOK = (acbCancelConnection(Me!lstConnections.Column(0), True) = 0)
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., “\GATEWAYHPLJ4”), 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 fOK = (acbAddDriveConnection(Me!txtLocalName & "", _ Me!txtRemoteName & "", Me!txtUserName & "", Me!txtPassword & "") = 0) Else ' Printer fOK = (acbAddPrinterConnection(Me!txtLocalName & "", _ Me!txtRemoteName & "", Me!txtUserName & "", Me!txtPassword & "") = 0) End If End If
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.
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
Value |
Constant |
Description |
---|---|---|
0 |
|
No error occurred. |
5 |
|
Access is denied. |
66 |
|
The network resource type is not correct. |
67 |
|
The network name cannot be found. |
85 |
|
The local device name is already in use. |
86 |
|
The specified network password is not correct. |
170 |
|
The requested resource is in use. |
234 |
|
More data is available. |
1200 |
|
The specified device name is invalid. |
1201 |
|
The device is not currently connected, but it is a remembered connection. |
1202 |
|
An attempt was made to remember a device that had previously been remembered. |
1203 |
|
No network provider accepted the given network path. |
1204 |
|
The specified network provider name is invalid. |
1205 |
|
Unable to open the network connection profile. |
1206 |
|
The network connection profile is corrupt. |
1208 |
|
An extended error has occurred. |
1222 |
|
The network is not present or not started. |
1223 |
|
The user canceled a dialog. |
2250 |
|
This network connection does not exist. |
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 fOK As Integer lngSize = conMaxComputerNameLength+ 1 strBuffer = Space(lngSize) fOK = 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
\servershare
name):
\WOMBATHPLJ4
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
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 |
Action |
---|---|
acbConnectDriveDialog |
Add a drive connection. |
acbDisconnectDriveDialog |
Cancel a drive connection. |
acbConnectPrintDialog |
Add a printer connection. |
acbDisconnectPrintDialog |
Cancel a printer connection. |
For example, to pop up the common drive connection dialog, you’d call:
fOK = 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 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 |
Action |
Parameters |
Description |
---|---|---|---|
acbAddDriveConnection |
Add a drive connection. |
|
Local name, like “LPT1:” or “G:”. |
|
Remote name, like “\SERVERSHARE”. | ||
|
Username to be used. If empty, uses default user’s name. | ||
|
Password for the user specified. If empty, uses the default user’s password. | ||
acbAddPrintConnection |
Add a printer connection. |
|
See parameters for acbAddDriveConnection. |
acbCancelConnection |
Cancel any connection. |
|
Local name of resource to disconnect. |
|
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:
fOK = acbAddPrintConnection("LPT2:", "\BARTCanonColor", "", "")
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, fForce As Boolean) As Long acbCancelConnection = WNetCancelConnection2( _ strName, CONNECT_UPDATE_PROFILE, fForce) 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, since 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.