MS-Access and Reference Library ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

When developing an Access database, we often use several object libraries in addition to the default reference libraries. These include Microsoft Data Access Objects (DAO), Microsoft ActiveX Data Objects (ADODB), and Visual Basic for Applications (VBA), among others. They are essential for creating data processing routines in VBA.

I briefly touched on this topic in my first article on this site, Command Button Animation, where I recommended linking these libraries manually before attempting to run the sample program provided there. The same article also listed the essential library files and the procedure to manually attach them to your database.

Some libraries—such as Microsoft Access 9.0 Object Library, Visual Basic for Applications, and OLE Automation—are attached by default when a new database is created. Additional libraries can be added manually as needed. We add new reference libraries to take advantage of the powerful features, which are not included in Access by default.

For example, we have used the Microsoft Office Library to create customized message boxes, option balloons, check boxes, and other controls. The Office Assistant is an interesting feature, which we used to capture user responses. We even created a wizard that allows users to select an Office Assistant character—such as Clippy, the Cat, or others—directly from Access, without the built-in Office Assistant options.

Databases and tables can be accessed in VBA only when either the Microsoft DAO or Microsoft ADODB library is referenced in your database.

If you have written your own custom functions, you can store them in a separate database on the server. Attach that database as a Library Database, eliminating the need to copy and paste those functions into other databases.

You can use the Object Browser in the VBA editor (View → Object Browser) to explore the properties, methods, and classes provided by each referenced library.

Library Reference and Physical Files.

Most of the time, we work with a fixed set of library references in our databases. These libraries are typically attached manually through Tools → References in the VBA editor, one by one. Although this is usually a one-time setup task, it can become tedious when starting a new project—or necessary again if references are lost, such as after recovering a corrupted database.

Fortunately, this process can be automated. With a simple VBA routine, we can programmatically attach all required library files to a new project or restore missing links automatically whenever needed.

Before we look at the VBA code, let’s review the few preparatory steps required to accomplish this.

Prepare a list of essential Library References and save it in a Text File, on the Server if you are developing databases to use on a Network.
Write a VBA routine to read this list from the Text File and attach it to the Project.
To prepare the required Object Library References List, we must know the details of these files and the Folder on the Disk where they can be located. Assume that we need to know the physical file name and location representing the Reference Library Description Microsoft DAO 3.6 Object Library in the Available References list.

To verify this, open the VBA Module Window by selecting View → Code or pressing Alt + F11. Then, from the menu, choose Tools → References. In the References dialog box that appears, scroll through the list and locate the item Microsoft DAO 3.6 Object Library among the other available Microsoft reference libraries. Refer to the image below for guidance.

Right-click to open Large Image in New Tab/Window.

When you select the file Description, the physical File path name and Language are displayed. You can write down the Pathname to prepare the list if you want to. But we will do half of the task differently. We can attach the required files manually and prepare the list with a small VBA Routine.

Display List of Library Files with VBA

Following is a list of References that I use regularly to start with my Projects, and we will use them as an example:

Right-click to open Large Image in New Tab/Window.

The first three items will be automatically selected by MS Access when you create a new Database. Others must be added manually.

Attach the above list of Library References manually.

Copy and paste the following lines of code into a Standard Module of your Project and save it.

Public Sub ReferenceList()
Dim Ref As Reference
For Each Ref In Application.References
   Debug.Print Ref.FullPath
Next
End Sub

Press Ctrl+G to display the Debug Window (Immediate Window) if it is not already visible.
Click in the middle of the Code, press F5 to run it, and print the following Path Names of the selected Reference Library Files in the Debug Window.

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
C:\WINDOWS\system32\stdole2.tlb
C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
C:\Program Files\Common Files\system\ado\msado15.dll
C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
D:\MDBS\aprRefLib.mde

Project Names of Library Files.

The internal names (Project Names) of the above library files differ from their file names, and it is essential to know the details about them. If you ever need to remove any of these libraries from the current project through code, you must reference them by their Project Name rather than by the file name.

When you create the MS-Access database with the name abcd.mdb, by default, the same name abcd will be inserted into the Project Name control in the database. You can check the Project Name of your database by selecting the Tools Menu in the VBA Editing Window. You will find a Menu Option like abcd Properties. You can open this option and set a different name in the Project Name control if needed. If you create a Standard Module with the name abcd, you will run into Error: Name Conflicts with Existing Module, Project, or Object Library.

Note: You may change the Project Name, but you are not allowed to use any of the Standard Modules' Names.

You can read this Property Value in programs or in the Debug Window using the statement x = Application.GetOption("Project Name").

Or modify the Project Name with a statement like Application.SetOption "Project Name", "Myabcd"

The list of library files mentioned above also includes their unique Project Names, which are the internal object library reference names that will appear in your database once linked. You can verify these names by opening the Object Browser (View → Object Browser or F2) and clicking the drop-down list. You will also notice that the Project Name of your current database (for example, abcd) appears among the list.

Let us inspect the Project Names of the first three files in the list given above.

VBA
Access
Stdole

When you create a new database, MS Access attaches the above three references by default, and they are important too. So we will exclude them from our add/remove operations.
These names are unique irrespective of which version of Access you are using, and the same goes for the other Library References as well.
If you attempt to attach a different version of a reference library that shares the same Project Name, a conflict may occur. To prevent this, we need to pay special attention to two additional references and ensure they are not removed before attaching the items from our list.
abcd (current database project name)
aprRefLib (you can read this as your own Function Library Project Name, and I will come to that later in this article.)

Leaving aside the above five items, we are left with the following four Reference Libraries selected out of the eight items listed above to attach to our new Projects automatically:

C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll

C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL

C:\Program Files\Common Files\system\ado\msado15.dll

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

Open Notepad.exe, copy and paste the above File Names, and save them on your Server's common location where all your MS-Access Projects are installed.
Let us call the Server PathName (the Location and File Name) of the target text file as

\\hosfs03\InhouseSys\CommonLib\RefLib.txt

The first stage of our preparation is now complete. The next step is to create a VBA program that can read the contents of the text file above and automatically link the reference library files (as shown in the second image) to your new project.

The VBA routine is provided below, but you have two options for where to place the code. The second option—copying the code into each new project and running it from there—is not recommended, as it leads to unnecessary duplication. Instead, choose the first option and do some ground work now by following the steps below. This approach will make future development easier, allowing you to reuse common routines across all projects without duplicating code.

If you haven’t already started building a Reference Library Database of your own, now is the perfect time. It’s straightforward to create, and you’ll quickly see how useful it can be.

Attaching Missing Library Files with VBA

Create a new Database with the name MyLib.mdb and save it to your Server's Common Location. Let us insert this location address, like the one we have saved in our Text File with the list.
Open the VBA Editing Window (Alt+F11).
Create a Standard Module (Insert -> Module) to create an empty Code Module with the name Module1. You can change the name of the Module after displaying its Property Sheet (View ->Properties Window).
Copy and paste the following Code into the Module; save and close the VBA Editing Window.

Public Function AddReferences()
Dim j As Integer, i As Integer, msg As String
Dim Ref As Reference, RefObj As Object
Dim RefPath As String, LibName As String
Dim LibPath As String, libAttached() As String
Dim refcount As Integer, chk_flag As Boolean
Dim lib_Attached As String, validate As Boolean

Const LibraryList As String = "\\hosfs03\InhouseSys\CommonLib\RefLib.txt"

On Error GoTo AddReferences_Err

validate = Ref_Retain_Remove()
If validate = False Then
   msg = "Errors Encountered in Validation check, Program aborted. "
   Exit Function
End If

Set RefObj = Application.References
refcount = Application.References.Count
ReDim libAttached(1 To refcount) As String
i = 0
'Prepare list of exiting attached Library Files
For Each Ref In RefObj
    i = i + 1
    libAttached(i) = Ref.FullPath
Next'Open text file with List of required Reference Library files
Open LibraryList For Input As #1
msg = ""
Do While Not EOF(1)
    Input #1, LibPath
    chk_flag = False
    For j = 1 To i'check for missing cases
        lib_Attached = libAttached(j)
        If libAttached(j) = LibPath Then
            chk_flag = True
            Exit For
        End If
    Next
    If chk_flag = False Then 'Reference found missing, add to the Project
        Set RefObj = Application.References.AddFromFile(LibPath)
        msg = msg & LibPath & vbCr
    End If
Loop
Close #1

If Len(msg) <>  0 Then
    msg = "Following References Attached: " & vbCr & vbCr & msg
End If

MsgBox msg, , " AddReferences()"

AddReferences_Exit:
Exit Function

AddReferences_Err:
MsgBox Err.Description, , "AddReferences()"
Resume AddReferences_Exit
End Function

Public Function Ref_Retain_Remove() As Boolean
Dim Ref As Reference, Reflist(), exclusion(1 To 5) As String
Dim ref_count As Integer, strRefName As String
Dim i As Integer, j As Integer, chk_flag As Boolean

On Error GoTo Ref_Retain_Remove_Err

exclusion(1) = "VBA"
exclusion(2) = "Access"
exclusion(3) = "stdole"
exclusion(4) = Application.GetOption("Project Name")
'Replace this line with your own Reference Library Name
exclusion(5) = "aprRefLib"

ref_count = Application.References.Count

If ref_count > 4 Then
    ReDim Reflist(1 To ref_count)
    i = 0
    For Each Ref In Application.References
       strRefName = Ref.Name
       chk_flag = False
       For j = 1 To 5
          If strRefName = exclusion(j) Then
              chk_flag = True
              Exit For
           End If
        Next
        If chk_flag = False Then
            i = i + 1
       'Collect the Reference Library Project Names, if any, other than
       'the Names in the exclusion list to remove them
       'before attaching the new ones, to avoid Project Name conflict.
            Reflist(i) = Ref.Name
        End If
    Next
    ReDim Preserve Reflist(1 To i)'Remove the collected Reference Libraries
    For j = 1 To i
        Set Ref = References(Reflist(j))
        References.Remove Ref
    Next
End If

Ref_Retain_Remove = True

Ref_Retain_Remove_Exit:
Exit Function

Ref_Retain_Remove_Err:
MsgBox Err.Description, , "Ref_Retain_Remove()"
Ref_Retain_Remove = False
Resume Ref_Retain_Remove_Exit
End Function

Note: Before closing the database, you can save a compiled copy of your Library Database by selecting Tools → Database Utilities → Make MDE File and naming it, for example, MyLib.MDE. Save it in the same location as the .mdb file, and you can move the .mdb file to a private location where others cannot access it.

Whenever you add new common routines to the library, you can recompile and replace the existing .MDE file. This makes the updated programs available to all your projects without modifying them. You can attach your Library Database (MyLib.MDE) along with others to all current and future projects.

We have two programs: the second one validates existing attached references and removes unnecessary ones, leaving only the essential references mentioned earlier to avoid conflicts.

The Demo Run from a new Database

The preparations are in place, and we are going to do a Trial Run.

Create a new Database.
Open the VBA Editing Window (Alt+F11).
Open the Debug Window (Immediate Window) . . . Ctrl+G.
Type the following line in the Debug Window and press Enter so that we can attach your own Common Library File MyLib.mde and run the main program AddReferences() from there:

Note: Change the Server Location address to match your own.

Application.References.AddFromFile("\\hosfs03\InhouseSys\CommonLib\MyLib.mde")

If you select Tools -> Reference, you can see that your own Library File is now attached to your Project.

We can now call the AddReferences program from your Function Library from the Debug Window and attach the other Library Files List we created in the Text File.

Type the following line in the Debug Window and press the Enter key to do that:

AddReferences

You can now check the Reference Library List to confirm that all the required files are in place. In your next project, all you need to do after creating a new Database is to type the following two lines in the Debug Window and press the Enter key to add all required Reference Libraries to your Project at once:

Application.References.AddFromFile("\\hosfs03\InhouseSys\CommonLib\MyLib.mde")

AddReferences