ACC2000: Connecting to the First Available Network Drive

• Microsoft Access 2000

SUMMARY

To make a network connection with the Windows application programming interface (API), you can use an unused logical drive letter or a Universal Naming Convention (UNC) name, such as \\Servername\Sharename. You can call the Windows API function, GetDriveType(), from a Visual Basic for Applications module to find the first available drive letter, and then make a network connection to this available drive.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. In a new Microsoft Access database, create a module and type the following lines in the Declarations section:




Option Explicit

Declare Function GetDriveType Lib "kernel32" Alias "GetDriveTypeA" _
    (ByVal nDrive As String) As Long
Declare Function WNetAddConnection Lib "mpr.dll" Alias _
    "WNetAddConnectionA" (ByVal lpszNetPath As String, _
    ByVal lpszPassword As String, ByVal lpszLocalName _
    As String) As Long
 

2. Type the following procedure:




Public Function FreeDrive(MyShareName$, MyPWD$) As Integer
   On Local Error GoTo FreeDrive_Err
   Dim DriveNum As Long, FirstFreeDrive As String
   Dim FirstDrive As Long, Results As Long
   DriveNum = 2
   Do
      DriveNum = DriveNum + 1
      FirstFreeDrive = Chr$(DriveNum + 65) + ":\"
      FirstDrive = GetDriveType(FirstFreeDrive)
   Loop Until FirstDrive = 1
   FirstFreeDrive = Left(FirstFreeDrive, 2)
   FreeDrive = WNetAddConnection(MyShareName$, MyPWD$, _
    FirstFreeDrive)
FreeDrive_End:
   Exit Function
FreeDrive_Err:
   FreeDrive = Err
   MsgBox Error$
   Resume FreeDrive_End
End Function 

3. To test this function, type the following line in the Immediate window, and then press ENTER

   
   ?FreeDrive(<"\\servername\sharename">, <"MyPwd">) 

   where <"\\servername\sharename"> is a valid server and sharename in your network organization and <"MyPwd"> is your password that provides permission to the resource.
   
   NOTE: Some of the possible return values for the FreeDrive() function include WN_SUCCESS, WN_NET_ERROR, and WN_BAD_PASSWORD. Other run-time errors could be returned from the function; therefore, error trapping should be implemented to handle any problems.

REFERENCES

For more information about declaring API functions, click Microsoft Access Help on the Help menu, type "Declare Statement" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words:

Keywords : kbprg kbdta AccCon KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto