Creating and Using Visual Basic for Applications Code Databases

Microsoft Corporation

July 1996

Overview

The release of Microsoft® Access version 7.0 afforded new functionality to application development in the form of reusable database code repositories. These databases are similar to other Microsoft Access application databases, except that they contain referenced objects.

A new model for accessing external objects exists by using the References option on the Tools menu. When you design a code database, you specifically expose the functions through the use of the Public keyword in all routines you want to access externally.

Take, for example, a function that you use in all of your database applications. Here's one that returns the file-name portion of an absolute path: 

   Public Function ParseFileName(strFName As String) As String
       On Error GoTo ParseFileName_Err
       Dim strChar As String
       Dim strTemp As String
       Dim i As Integer
   
    ' Get the file name portion of the passed string
       For i = Len(strFName) To 1 Step -1  
          strChar = Mid(strFName, i, 1)
          If strChar = Chr$(92) Then
             ParseFileName = strTemp
             GoTo ParseFileName_End
          End If
          strTemp = strChar & strTemp  
       Next i
      
    ParseFileName_End:
       Exit Function
    ParseFileName_Err:
   
       MsgBox Err.Description, vbCritical, "modMyReusableCode.ParseFileName
       Resume ParseFileName_End
   
    End Function

How To Create the Code Database

  1. Create a new blank database and name it MyCode.mdb.

  2. Create a new module.

  3. Create a new function exactly as shown in the previous code.

  4. Save and name the module modMyReusableCode.

  5. Re-open the modMyReusableCode in Design view.

  6. On the Run menu, click Compile All Modules.

  7. Close the module.

  8. Close the database.

Now you have created the needed components to reuse your functions. Let's use the ParseFileName() function from another database.

  1. Open the sample database Northwind.mdb (you should have installed this at setup).

  2. Create a new, blank form.

  3. In the Toolbox, click to clear the Wizard option.

  4. Add a button to the form.

  5. Add the following code to the OnClick event of the button. 
    Private Sub Command0_Click()
On Error GoTo Command0_Click_Err
MsgBox CurrentDb.Name      
MsgBox ParseFileName(CurrentDb.Name)
Command0_Click_End:
Exit Sub
Command0_Click_Err:
MsgBox Err.Description, vbInformation      
Resume Command0_Click_End    
End Sub
  6. On the Tools menu, click References.

  7. In the Add Reference dialog box, click the Browse button, and click the MyCode.mdb database containing the ParseFileName function.

  8. Ensure that the database appears under Available References and that it is checked.

  9. Close and save the form as Form1.

  10. Open the form.

  11. Click the button. Note that the following message boxes will appear in succession:
    c:\MSOffice\Access\Samples\Northwind.mdb 
Northwind.mdb

The first message box reflects the full path, and the second shows the database name only after passing through the use of the ParseFileName() function.

A more advanced example

The other objects in a referenced database, such as the forms and reports, may be accessed from the calling database, but the context may not always be applicable to the current database application. One useful aspect of the repository database concept is to create objects that you use over and over and bring them into the current database for customization. The next example takes a basic form design, in this case a company splash dialog, and imports the object into the current database.

  1. Open the MyCode.mdb database.

  2. Create a new, blank form.

  3. Save the form and name it frmMySplashForm.

  4. Open the modMyReusableCode in Design view.

  5. Create the function shown below. 
       Public Function GetMySplashForm(strDbName As String, _
                                Optional strNewName As Variant) _
                                As Boolean
   On Error GoTo GetMySplashForm_Err
       ' Export back the splash form to caller database.
       DoCmd.CopyObject strDbName, _
                    IIf(IsMissing(strNewName), "frmMySplashForm", strNewName), _
                    acForm, _
                    "frmMySplashForm"
                   
       GetMySplashForm = True
    GetMySplashForm_End:
       Exit Function
    GetMySplashForm_Err:
       MsgBox Err.Description, vbCritical, "modMyReusableCode.GetMySplashForm"
       Resume GetMySplashForm_End
    End Function
  6. On the Run menu, click Compile All Modules.

  7. Close the module.

  8. Close the database.

  9. Open the sample database Northwind.mdb (in shared mode).

  10. Press CTRL+G to display the Immediate window.

  11. Type the following line, and then press ENTER: 
              ? GetMySplashForm(CurrentDb.Name)

When you run the code, the frmMySplashForm form will be imported into the sample database Northwind.mdb.

Setting absolute paths to repository databases

If you set a reference to a type library from Microsoft Access and then move the file that contains that type library to a different directory, Microsoft Access will attempt to locate the file and re-establish the reference. If the RefLibPaths key exists in the registry, Microsoft Access will first search there. If there is no matching entry, Microsoft Access will search for the file first in the current directory, and then in all the directories on the drive. You can create the RefLibPaths key using the registry editor in Microsoft Windows® 95 or Windows NT®, under the registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0. For more information about using the registry editor, see the Windows 95 or Windows NT documentation.

To reference a specific repository database:

  1. Close Microsoft Access.

  2. Open the regisrtry editor applications (in Windows 95, Regedit.exe, and in Windows NT® 3.51, Regedt32.exe)

  3. Browse to the following branch and key: \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Access\7.0Software

  4. Create a new key named RefLibsPath.

    Let's assume that our database MyCode.mdb is located at c:\MSOffice\Access\Samples\MyCode.mdb

  5. Create a new string value named "MyCode.mdb."

  6. Set the value of the option to c:\MSOffice\Access\Samples\MyCode.mdb.

  7. Open Microsoft Access.

  8. From a module, note that MyCode.mdb is listed in the possible reference selection and that when highlighted, it will point to the proper absolute path location of the database.

Summary

The techniques described in this article provide you with examples of how powerful a development concept using repository databases can be. There are a few important considerations to keep in mind when you implement referenced databases.