ACC: Sample Function to Determine the Access Startup Directory

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes the steps involved in creating a function that returns the Microsoft Access startup directory.

Microsoft Access does not have a built-in command to return the Microsoft Access startup directory. However, you can accomplish this by making Windows application programming interface (API) calls in Access Basic.

NOTE: Microsoft Access 2.0 does include the function SysCmd() to return the directory where the Msacess.exe file is located. See the bottom of this article for more information.

MORE INFORMATION

The Access Basic CurDir$() function returns the current directory. Because Microsoft Access can be started from a directory other than the current directory or the current directory can be changed with the ChDir statement, the CurDir$() function cannot be used to determine the startup directory.

The following sample program module makes use of the Windows API functions GetModuleHandle() and GetModuleFileName(). With the module handle, the path can be obtained with the GetModuleFileName() function.

Creating the Module

The following steps demonstrate how to create the sample Access Basic function that returns the startup directory:

1. From the File menu, choose New, and select Module.

2. Type the following lines in the Declarations section.

   NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         Option Explicit
         Declare Function GetModuleHandle% Lib "kernel" (ByVal FileName$)
         Declare Function GetModuleFileName% Lib "kernel" (ByVal hModule%, _
                             ByVal FileName$, ByVal nSize%)
   
   

3. Type the following function:

         Function StartUp_Dir ()
         Dim hModule%, Buffer$, Length%, Msg$
            hModule% = GetModuleHandle("MSACCESS.EXE")
            Buffer$ = Space$(255)
            Length% = GetModuleFileName(hModule%, Buffer$, Len(Buffer$))
            Buffer$ = Left$(Buffer$, Length%)
            Msg$ = "Startup path and filename: " & Buffer$
            MsgBox Msg$
         End Function
   
   

4. From the Run menu, choose Compile Loaded Modules in Microsoft Access version 1.x, or choose Compile All in version 2.0.

5. Save the module as Startup Directory.

6. From the View menu, choose Immediate window. Type the following line in the Immediate window, and then press ENTER:

         ? StartUp_Dir()
   

   Note that the Microsoft Access startup directory is displayed in the Immediate window.

Uses and Variations

The Startup directory is displayed when you type the following line in the Immediate window:

   ? StartUp_Dir()

NOTE: You can change the MSACCESS.EXE argument for the Windows API GetModuleHandle() function so that the function returns the startup directory of another program started in the Windows environment. Furthermore, you can pass a program name as a variable to the Windows API function, giving even more flexibility to the function.

SysCmd() Function

If all you need is the directory in which the Msacess.exe file is located, you can use the SysCmd() function, as in the following example:

   Function test()
      Dim AccDir As String
      AccDir = SysCmd(acSysCmdAccessDir)
      Debug.Print AccDir
   End Function

REFERENCES

"Microsoft Windows Software Development Kit," Microsoft Press, 1992

"Programming Windows: the Microsoft Guide to Writing Applications for Windows 3," Charles Petzold, Microsoft Press, 1990