Access Modules and API Code

(Note many code examples here are in Access 2 format some syntax adjustment may be required for Access 95 or 97)

Table Topics | Queries and Recordsets

FormTopics | Report Topics| General Access Topics

General

Pausing Running Code to Gather Input from the User

Code to Return the Date for the End of the Month, Beginning and End of the Quarter from a specific date

How to Determine if an Instance of the Application is Already Running on the Computer.

Add Pizzaz to your Message Boxes in Access 95/97

How to Hide the Access Window when Running a Form or Report ShortCut from the Windows Desktop

Save Memory When Writing VBA Code

More to Come.....

API

Pausing Code until a Shelled Process Finishes (Access 2)

Pausing Code until a Shelled Process Finishes (Access 7 & 8)

Show a List of Directories in Access 7 & 8 using the Windows Shell.

How to Get the Name and Directory Path for the Current Database.

How to Get the Network User Name and Computer Name in Access 95 & 97.

How to Get the Network User Name on a Novell Network

How Can I Check If a Network Drive Is Available?

How Do I Open My Application's Help File to the Contents or to a Specific Item in the Index/Search Section

© 1996-97 ATTAC Consulting Group










Back to the Tips List

Pausing Code to Wait Until a Shelled Process Is Finished in Access 2
If you need to shell to another program from Access, such as a zipping program, a communications or mail program, you'll often want to stop your code while the shelled process operates and then resume your code once the process is finished. However, once Access starts the other program, or process, it will continue on its merry way running your function unless you build in explicit code which causes your function to wait until the external program finishes. Here's an example of how to do this in Access 2 using the Win api:

1. Paste the following declaration into your module:

Declare Function GetModuleUsage% Lib "Kernel" (ByVal hModule%)

2. Try out this test function, which launches any app you want to and waits until it is finished to display a message box (Note an " _ " underscore means line continuation):
Function LaunchApp (MYAppname As String) as Integer

   Dim ModuleHandle As Integer



   LaunchApp=-1

   ModuleHandle = Shell(appname, 1)

   If (Abs(hMod) > 32) Then

        While (GetModuleUsage(hMod))

            DoEvents

        Wend

    Else

        MsgBox "ERROR : Unable to start " & MyAppname

        LaunchApp=0

    End If



MsgBox "This code waited to execute until " _ 

        & MyAppName & " Finished",64

End Function

Back to the Tips List

Pausing Code to Wait Until a Shelled Process Is Finished in Access 7 & 8
If you need to shell to another program from Access, such as a zipping program, a communications or mail program, you'll often want to stop your code while the shelled process operates and then resume your code once the process is finished. However, once Access starts the other program, or process, it will continue on its merry way running your function unless you build in explicit code which causes your function to wait until the external program finishes. Unlike using Access 2, the Win32 api doesn't have the function "GetModuleUsage" to measure whether the shelled process is still running. Instead in Win32, you must use the function "WaitforSingleObject", and "OpenProcess". Below we've provided a sample function to launch an application and wait for it to finish.

1. On the declarations page of your module, add the following functions:

Private Declare Function OpenProcess Lib "kernel32.dll" (ByVal _

     dwAccess As Long, ByVal fInherit As Integer, ByVal hObject _

     As Long) As Long

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _

      hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal _

      hObject As Long) As Long

2. Try out this test function, which launches any app you want to and waits until it is finished to display a message box (Note an " _ " underscore means line continuation):

Function LaunchApp32 (MYAppname As String) As Integer

 On Error Resume Next

 Const SYNCHRONIZE = 1048576

 Const INFINITE = -1&

 Dim ProcessID&

 Dim ProcessHandle&

 Dim Ret&



 LaunchApp32=-1

 ProcessID = Shell(MyAppName, vbNormalFocus)

   If ProcessID<>0 then

       ProcessHandle = OpenProcess(SYNCHRONIZE, True, ProcessID&)

       Ret = WaitForSingleObject(ProcessHandle, INFINITE)

       Ret = CloseHandle(ProcessHandle)

  

       MsgBox "This code waited to execute until " _ 

          & MyAppName & " Finished",64

   Else

        MsgBox "ERROR : Unable to start " & MyAppname

        LaunchApp32=0

   End If

End Function

3. It is important to note that your function must include the code to close the process handle after the shelled application is complete, otherwise you will have a memory leak until you shut down Windows.

Back to the Tips List

Presenting a List of Directories to a User in Access 7 & 8 using the Windows Shell
In Access 7 and 8 when run under Windows 95 or Windows NT 4, you can provide Users with a simple Directory dialog rather than using the standard File Open or File Save As dialogs from the common dialog suite which shows both files and directories. To do this you use the Directory dialog built into the Shell OLE container. Here's the code to do it:

In the declarations page of a module, add the following declares (an "_" means line continuation):

Type shellBrowseInfo

    hWndOwner      As Long

    pIDLRoot       As Long

    pszDisplayName As Long

    lpszTitle      As String

    ulFlags        As Long

    lpfnCallback   As Long

    lParam         As Long

    iImage         As Long

End Type



Const BIF_RETURNONLYFSDIRS = 1

Const MAX_PATH = 260



Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)

Declare Function SHBrowseForFolder Lib "shell32" (lpbi As shellBrowseInfo) As Long

Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, _

         ByVal lpBuffer As String) As Long

Then use the following function, supplying it the title you want to use for the dialog, and the handle of the calling form. (use the Me.hwnd property of the form):

Public Function GetFolder(dlgTitle As String, Frmhwnd as Long) As String



    Dim Nullchr As Integer

    Dim IDList As Long

    Dim Result As Long

    Dim Folder As String

    Dim BI As shellBrowseInfo



    With BI

        .hWndOwner = Frmhwnd

        .lpszTitle = dlgTitle

        .ulFlags = BIF_RETURNONLYFSDIRS

    End With



    IDList = SHBrowseForFolder(BI)

    If IDList Then

        Folder = String$(MAX_PATH, 0)

        Result = SHGetPathFromIDList(IDList, Folder)

        Call CoTaskMemFree(IDList)        'this frees the ole pointer to IDlist

        Nullchr = InStr(Folder, vbNullChar)

        If Nullchr Then

            Folder = Left$(Folder, Nullchr - 1)

        End If

    End If



    GetFolder = Folder



End Function
This function will return the path to the folder selected, so long as it is not a system folder such as the printers folder.

Back to the Tips List

How to Get the Name of the Currently Open Database.
To get the directory path of the currently open database in any version of Access, get the Name property of the database in VBA. Here's the simple code to do it:
Function GetDbPath() As String



    Dim MyDb as Database

    

    Set MyDb = CurrentDB()

    GetDbPath = MyDb.Name



Back to the Tips List

How to Get the Network Log In Name and Computer Name of the Currently Logged In User in Access 95 & 97
Two easy api calls can provide you with network log in name of the current user of the workstation and the network name of the computer itself for use in your Access application. Declare the following functions in your module and add the following function:
    Private Declare Function api_GetUserName Lib "advapi32.dll" Alias _

          "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Private Declare Function api_GetComputerName Lib "Kernel32" Alias _

          "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long



    Public Function CNames(UserOrComputer As Byte) As String

    

    'UserorComputer; 1=User, anything else = computer



    Dim NBuffer As String

    Dim Buffsize As Long

    Dim Wok As Long

        

    Buffsize = 256

    NBuffer = Space$(Buffsize)

   

    

    If UOrC = 1 Then

        Wok = api_GetUserName(NBuffer, Buffsize)

         CNames = Trim$(NBuffer)

    Else

        Wok = api_GetComputerName(NBuffer, Buffsize)

        CNames = Trim$(NBuffer)

    End If

End Function

Our System Information sample mdb in the Free file library contains this an other useful system api functions.

Back to the Tips List

How to Get the Network Log In Name of the Currently Logged In User on a Novell Network
If you need to get the network log in name of the workstation user on a Novell Network, the easiest way to do so is to set an environment variable in the Novell log in script and then call that variable using Access' "Environ" function. Here how to do it:

First add an environment variable to your Novell Log In script which captures the User Name as in:

    DOS SET USERNAME = LOGIN_NAME

Then add the following function to a module:

Function GetNetworkUser() as String

      GetNetworkUser = Environ("USERNAME")

End Function

While setting an environment variable is relatively easy to gather this information, it also exposes certain security holes. A more complete and secure approach is to use the Novell API's to gather this information. An extensive sample Access 2 database, contributed by Doug Steele is available in the ACG Free Files library which provides a complete example of using the Novell API's to get the user name, group of the user, servers available etc.

Back to the Tips List

How to Create your own Input Box forms and pause code for User input.
In your application you may need to get information or a selection from a user that an a standard Input Box isn't designed to handle.

As an example, you may want to offer the user only a selection of one of two choices. Since there's no way to restrict what the user can enter into an input box, an input box won't work well.

To get around this limitation you can design a form that acts as psudo input box, opening the form to get input, and then when closed, resuming your code.

Here's how:

  1. In the declarations section of the module which the running function is in, and for which you need to get the input from the user, dimension new variable(s) which will to hold the return values which the user selects or enters into your input form.

    The value of these variables will be "visible" to your running function after the user enters their selections. There's no need to make these variables global variables.

  2. Add a new sub procedure to the same module as your running function. Your custom input box form will call this sub procedure. The purpose of this sub procedure is solely to accept the values from your input form as parameters and to set the new module level variables equal to the value of the parameters.

  3. Design your own "input box" form as a dialog box form. When a dialog box is opened, it will cause your running code to "pause" until the dialog is closed. (You have to specifically specify opening it in your running function as "acDialog".)

  4. In the OnClose event of your input box form, now add a call to the sub procedure you just wrote. Specifying as parameter values, the values of the text boxes or option groups etc. from your form. This will send the data back to your module when the dialog is closed.

  5. You can also make your main code pause and wait for the user's input and for non-dialog style forms by using code like the following in you main procedure ( an " _ " means the line continues on the next line show here):
    DoCmd OpenForm "MyForm", A_Normal
    
    
    
    While SysCmd(SysCmd_GETOBJECTSTATE, A_FORM, _ 
    
                "MyForm Name") = OBJSTATE_OPEN
    
          DoEvents 'Do Nothing Wait for Closing
    
    Wend
    
    
    
    [Resume Code here]
    
    
  6. You can also include a parameter in your sub procedure to notify your running function if the user clicked a cancel button rather than an OK button to end your process.

Back to the Tips List

Code to Return the Last Day of the Month, the Beginning of a Quarter or End of a Quarter Based on a Specific Date
Access' Date Add and DateDiff functions generally fill most needs for date manipulation. However, there are a few instances where you need to return a date for comparison purposes which are not simple additive date result from a current date. Two examples are specifying the date for the end of the current month (e.g. for billing purposes), or gathering data which occurs, or is scheduled to occur based on whether it is after the beginning of a quarter, or before the end of a quarter. The following two functions provide the code to determine the end of the month from a specific date and the first or last day of any yearly quarter based on a specific date. (The second function below, providing the quarterly dates relys upon the end of the month function so both must be included in your module.) Here's the end of the month function:
Function EOMonth (Anydate)

'-------------------------------------------------------------------------------

'Purpose:  Returns the last day of the month for the date specified

'Accespts:  A Date or Date Variable.

'Returns:	VarType 7 Date 

'------------------------------------------------------------------------------

On Error GoTo Err_EOM

    Dim NextMonth, EndofMonth



       NextMonth = DateAdd("m", 1, Anydate)

       EndofMonth = NextMonth - DatePart("d", NextMonth)



       EOMonth = EndofMonth



Exit_EOM:

    Exit Function

Err_EOM:

    MsgBox "Error" & " " & Err & " " & Error$

    Resume Exit_EOM

End Function

Here's the Function to determine the beginning date or ending date of any quarter base on a date supplied:
Function BEQuarter (ByVal Anydate, BeginOrEnd As Integer) As Variant

'-------------------------------------------------------------------

'Purpose:  Returns the beginning or the end of a quarter

'Uses:     EOMonth() Function

'Input:     AnyDate: A date value, use of #'s to signify a date when variable

'                     from a query or the immediate window.

''              BeginOrEnd: 0 Finds Beginning of Quarter, -1 Finds End of Quarter

''Returns:  VarType 7 date

'---------------------------------------------------------------------

On Error GoTo Err_EOQ



  If BeginOrEnd <> 0 And BeginOrEnd <> -1 Then

     MsgBox "Error: BeginOrEnd must be 0 or -1"

     GoTo Exit_EOQ

  End If



  Dim EndofQuarter, BeginofQuarter, PrevQuarter

  Static MonthVar(12) As Integer



  If MonthVar(12) = 0 Then

    MonthVar(1) = 2

    MonthVar(2) = 1

    MonthVar(3) = 3

    MonthVar(4) = 2

    MonthVar(5) = 1

    MonthVar(6) = 3

    MonthVar(7) = 2

    MonthVar(8) = 1

    MonthVar(9) = 3

    MonthVar(10) = 2

    MonthVar(11) = 1

    MonthVar(12) = 3

  End If



    Anydate = Anydate - DatePart("d", Anydate)

    EndofQuarter = DateAdd("M", MonthVar(DatePart("M", Anydate)), Anydate)

    EndofQuarter = EOMonth(EndofQuarter)

    

    If DatePart("m", EndofQuarter) = 6 Then

        BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 2

    Else

        BeginofQuarter = DateAdd("q", -1, EndofQuarter) + 1

    End If

    

    If BeginOrEnd = -1 Then

       BEQuarter = EndofQuarter

    Else

       BEQuarter = BeginofQuarter

    End If



Exit_EOQ:

    Exit Function

Err_EOQ:

    MsgBox "Error" & " " & Err & " " & Error$

    Resume Exit_EOQ

End Function

Back to the Tips List

How to Test Whether an Instance of the Application is Already Running when the Application is Launched
Users being users may at times forget that they have a copy of the application running (hidden tool bar in Win 95/NT or a Maximized Program Manager in Win 3.1x) when they launch a second copy from the desk top. To prevent the second instance from loading, you can run code from your autoexec macro to test whether the app is already running and terminate the launch if a copy of it is already open.

To do this, you will need to incorporate the two following simple functions in your database and call the Function IsRunning below:

Function IsRunning() as integer

    Dim db As Database

    Set db = CurrentDB()

    If TestDDELink(db.Name) Then

        IsRunning = -1

    Else

        IsRunning =0

    End If

End Function



' Helper Function

Function TestDDELink (ByVal strAppName$) As Integer

    

    Dim varDDEChannel

    On Error Resume Next

    Application.SetOption ("Ignore DDE Requests"), True

    varDDEChannel = DDEInitiate("MSAccess", strAppName)

    

   ' When the app isn't already running this will error

    If Err Then

       TestDDELink = False

    Else

        TestDDELink = True

        DDETerminate varDDEChannel

        DDETerminateAll

    End If

    Application.SetOption ("Ignore DDE Requests"), False



End Function

This code works in all versions of Access.

Back to the Tips Index

Add pizzaz to your Message Boxes in Access 95 and 97 by varying the font weight.
Developer's since Access 1.x have used the combination of Chr(10) & Chr(13) & Chr(10) to break message boxes into paragraphs. In Access 95 and 97 you could use the intrinsic constants "vbCrlf" and "vbCr" to do the same thing.

There is a new feature of message boxes in Access 95 and 97 which allows you to not only break your message box into paragraphs, so that the action item is quickly identified, but also allows you to bold the first paragraph in the Message Box, just like Access itself does.

The "@" symbol added to your message text will break the message into paragraphs, with the first paragraph in bold. You are limited to two "@" symbols (three paragraphs,) and the "@" symbol should follow each of the first two paragraphs.

What this also allows you to do is that if you want your message box to have only bold text (a single paragraph), then add two "@" symbols at the end of the text separated by a space (e.g. @ @).

So if you had a Message Box statement like the following (note the " _ " means the line continues, remove when pasted into your app because the message box function can not span mutiple lines):

If MsgBox("You have just deleted the current record.@ _ 

     Click ""OK"" to confirm your delete or ""Cancel"" to undo your deletion.@ ", _

     vbOKCancel, "My AppName") = vbOK Then



         'Do somthing here



End If
The message box would bold the first paragraph "You have just deleted the current record" and then start a new non-bold paragraph for the action statement "Click OK to confirm your delete or Cancel to undo your deletion."

Back to the Tips Index

How to Hide the Main Access Window when Running a Form or Report Shortcut from the Desktop
If you have placed a short cut to an Access form or report on the Windows desktop, you may not want the main Access window to be displayed when your form or report is opened. There is no way to hide the window entirely if a form or report is launched from the desktop, Access will flash on the screen momentarily; but you can quickly hide using the following code and steps:
  1. If you don't want the Access spash screen to show, you can replace it with your own spash screen by creating a bitmap of your spash screen, naming it the same name as the database the form is in, and then placing it in the same directory as the db.

  2. Add the following code to your form or report's module in the declarations section:
    Const SW_HIDE = 0
    
    Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, _
    
         ByVal nCmdShow as Long) As Long

  3. In the form or report's OnOpen event add the following code:
    Dim dwReturn as Long
    
    dwReturn = ShowWindow(Application.hwndAccessApp, SW_HIDE)
  4. In the form or report's OnClose event add the code:
    Application.Quit
This last piece of code, to quit the application, is required to be added to what ever form will last be visible, be it a switch board form, single form or just a report. If you don't add this code, the form or report will be closed, but Access will still be running in the background, hidden from view.

This tip submitted by Larry Christopher

Back to the Tips List

How Do I Open My Application's Help File to the Contents or to a Specific Item in the Index/Search Section
Often you may want to take specific actions when opening the help file for your application. Access provides the ability to open the help file to a specific topic. But if you want to make sure that when you open the file it opens the search index on a specific topic or that it opens to the Contents (under Win95/NT) then you need to use the WinHelp api function. Here's how to do it:

To open to a specific help search/index topic call your help file as follows, where "strHelpVal" is the name of an index topic:

Private Declare Function WinHelp Lib "user32" Alias _

       "WinHelpA" (ByVal hWnd As Long, ByVal lpHelpFile As String, _ 

           ByVal wCommand As Long, ByVal dwData As Any) As Long

Const HELP_PARTIALKEY = &H105&



Public Function WHPartialKey()

    Dim dwReturn%

    Dim strHelpVal$

    

    strHelpVal = "Active Window" & Chr$(0) 'vbnullstring in A 95 and 97

    dwreturn = WinHelp(Application.hWndAccessApp, "acmain80.hlp", _

           HELP_PARTIALKEY, strHelpVal)

End Function

To open the help file to the general contents use the following (note if the user last had the index showing this will open the file on the index tab, if the contents were showing it will open the contents):
Private Sub cmdHelp_Click()

On Error Resume Next

    Dim dwReturn&

    

    dwReturn= WinHelp(Me.hwnd, "acmain80.hlp", &HB, 0&)



End Sub

Back to the Tips List

How Can I Check If a Network Drive Is Available?
Your application may need to access a file on a network drive or attach to tables for a backend database on a network drive. Prior to actually performing that action you may want to check to see if the network connection is available. Here's a function to do just that (Note this is in Win32 format, Win16 apis for Access 2 work identically but you have to change the api declarations as noted below):
Private Declare Function prn_WNetGetConnection Lib "mpr.dll" _

          Alias "WNetGetConnectionA" (ByVal LocalName$, ByVal RemoteName$, _

          cbRemoteName&) As Long

Private Declare Function prn_WNetAddConnection Lib "mpr.dll" Alias _

          "WNetAddConnectionA" (ByVal NetPath$, Password, LocalName&) As Long



Const ERROR_NO_ERROR = 0

Const ERROR_ACCESS_DENIED = 5

Const ERROR_BAD_NET_NAME = 67

Const ERROR_ALREADY_ASSIGNED = 85

Const ERROR_INVALID_PASSWORD = 86

Const ERROR_MORE_DATA = 234

Const ERROR_INVALID_ADDRESS = 487

Const ERROR_BAD_DEVICE = 1200

Const ERROR_CONNECTION_UNAVAIL = 1201

Const ERROR_DEVICE_ALREADY_REMEMBERED = 1202

Const ERROR_NO_NET_OR_BAD_PATH = 1203

Const ERROR_NO_NETWORK = 1222

Const ERROR_NOT_CONNECTED = 2250



Public Function at_CheckNet%(DriveOrPrinter$)

'------------------------------------------------------------

'Purpose:  To check to see if a drive or printer on the network is available

'Accepts:   Drive as "C:","LPT3:" or "\\network_server\drive"

'Returns:   True (-1) on Success, False (0) on Failure

'-------------------------------------------------------------

On Error GoTo Err_CN

    Dim dwError&

    Dim RemoteNamesz&

    Dim RemoteName$

    

    at_CheckNet = True

    If Instr(DriveOrPrinter, "\\") < 1 Then  'local named resource

        RemoteName = String(255, 0)

        RemoteNamesz = Len(RemoteName)

        dwError = prn_WNetGetConnection(DriveorPrinter, RemoteName, RemoteNamesz)

        If dwError = ERROR_CONNECTION_UNAVAIL or _

                      dwError = ERROR_NOT_CONNECTED Or _

                      dwError = ERROR_NO_NETWORK Then

            at_CheckNet = 0

            GoTo NetMsg

        End If

    Else   'a network address is supplied to the function

            'we supply a null password, which may be required & a null connection name 

            'since we're not actually connecting, just checking the connection

            'will return ERROR_DEVICE_AREADY_REMEMBERED if available

        dwError = prn_WNetAddConnection(DriveOrPrinter, Null, 0&)

        If dwError = ERROR_NO_NETWORK Or _

                     dwError = ERROR_NOT_CONNECTED Or _

                     dwError = ERROR_CONNECTION_UNAVAIL Or_

                     dwError = ERROR_NO_NET_OR_BAD_PATH Or _

                     dwError = ERROR_ACCESS_DENIED Or _

                     dwError = ERROR_BAD_NET_NAME Then

            at_CheckNet = 0

            GoTo NetMsg

        End If

    End If

    GoTo Exit_CN



NetMsg:

    MsgBox "The required network device is not currently available.", 16, "Check Net"

Exit_CN:

    Exit Function

Err_CN:

    MsgBox "Error: " & Err & " " & Error$, 16, "Check Net"

    Resume Exit_CN



End Function
The 16 bit Access 2 declarations are as follows for Windows 95/98, some of the consts also change (Note: the _ is a line continuation and it should be removed in Access 2):
Const ERROR_NO_NET_OR_BAD_PATH = 2

Const ERROR_BAD_PASSWORD = 6

Const ERROR_ACCESS_DENIED = 7

Const ERROR_NOT_CONNECTED = 48

Const ERROR_BAD_NET_NAME = 50

Const ERROR_BAD_LOCAL_NAME = 51

Const ERROR_DEVICE_ALREADY_REMEMBERED =52



Declare Function WNetGetConnection _

     Lib "User" (ByVal LocalName$, ByVal RemoteName$, _

     cbRemoteName As Integer) As Integer

Declare Function WNetAddConnection _

     Lib "User" (ByVal netpath$, Password as Any,  _

     LocalName%) As Integer

Note: If you want to actually create a network connection using WNetAddConnection, change the last parameter call in the declarations to ByVal LocalName as String, rather than a long or integer, and pass your local drive or port connection like "c:".

Back to the Tips List

How to Save Memory When Writing VBA Code
Access and VBA generally do a good job of "cleaning up" memory when they are done running your code. But there are a number of common coding methods used by developers which hinder Access' release of memory. Here's some steps to take in writing functions to save memory (or how to become a good C++ programmer without learning C++):
  1. Often you see constants declared in modules in the general declarations section, even when they are used only by one function; especially with api calls. Move your constants to the function which uses them when at all possible so that Access will automatically release their memory when the function ends. Constants declared at the module level will remain in memory for the life time of the application.

    (Avoid where at all possible global level variables. They are bad programming. Rather create a function or sub which can be called to set a variable in a particular module when necessary, or write your functions to use the "call back" capability inherent in VBA when you declare parameters by reference rather than by value. i.e. if you need to retrieve the value of a variable after a function is run, pass that variable as a parameter to the function by reference. When the called function changes the value of the parameter as it runs then the code that calls that function can reference the new value of the the parameter it specified after the called function finishes.)

  2. If you declare variables at the module level so that they are visible to multiple functions, de-initialize them at the end of the last function which uses them. This is especially true for string variables which have tremendous overhead associated with them (Mystring = "" sets the string to a zero length). Be sure your code re-dimensions arrays to zero (ReDim myarray(0)) when done.

  3. If you are using Class modules in Access 97, be sure to de-initialize variables in the class terminate function.

  4. Be sure to set your object and DAO variables to be equal to "Nothing" once they are closed. This releases the pointer to the object. On a related note, don't wait until the end of the function to close DAO variables which your code uses when the function no longer needs them. Close them and release the memory. Not closing DAO variables when repeated tables or queries are opened is a major source of the errors "Out of Memory" and "Can't open any more databases [or tables]" etc. Failing to close open DAO references can cause your application to minimize, but not close when you try to shut it down.

  5. If you know the length of a string to be allocated to a variable, then set the variable's length by using the notation "MyStringVar as String * X" where X equals the length of the string in characters. Access allocates more memory to variable length strings.

  6. Avoid using variables which are variants (There, we said it.)

  7. If you only use certain functions on rare occations in your app for specific actions, separate these functions into a unique and separate module. Access will only load the functions into memory when they are used (in Access 95 and 97) saving memory and load time.

Back to the Tips List