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

Introduction

We are using several Object Libraries besides the default References Library while developing an Access Database. Microsoft Data Access Objects (DAO), Microsoft ActiveX Data Objects (ADODB), Visual Basic for Application (VBA), and so on. These are essential for creating Data Processing Routines in VBA.

I have briefly touched on this subject in my first Article on this site: Command Button Animation and advised you to link these files to your Database manually, before attempting to run the Program given there also. The List of Essential Library Files and the procedures to attach them to the Database manually are also explained there.

Some of these Library References like Microsoft Access 9.0 Object Library, Visual Basic for Applications, and OLE Automation are attached by default when a new database is created and other files are added manually. We add new reference Libraries for using the powerful features extended by them, which are not available in Microsoft Access.

We have used Microsoft Office Library (Office) to create customized Message Boxes, Option Balloons, Check Boxes, and other controls. The Office Assistant feature we have used to capture user responses interestingly. We have created a Wizard to select the Office Assistant character Clippy, the Cat, or any other of your choice in Access, without using the Option provided by Office Assistant.

Databases and Tables can be accessed in VBA only when we add Microsoft Data Access Objects (DAO) or Microsoft ActiveX Data Objects (ADODB) Library Files to the Access Database.

If you have written some common Functions of your own, then you can save them in a separate database on the Server and attach them as a Library Database so that you don't have to copy and paste those Functions into other Databases.

You can use the Object Browser of VBA (View - - > Object Browser) to browse the Properties and Methods extended by a particular Library Object.

Library Reference and Physical Files.

Most of the time we use a fixed set of Library References in our Database and these are attached manually (Tools - - >References) one by one after opening the VBA Editing Window. Even though this is only a one-time exercise we can automate this process. Attach the required Library Files in a new Project or restore the lost links, if that happens at any point in time, like during the recovery of a corrupted database.

First, let us look at the few steps that we need to take to accomplish this.

a). 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.

b). Write a VBA routine to read this list from the Text File and attach it to the Project.

a). To prepare the required Object Library References List we must know what are these files and where on the Disk 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 find out this, open the VBA Module Window (View - - > Code or Alt+F11), select Tools- - >References, and find the item in the description Microsoft DAO 3.6 Object Library among the list of other Microsoft Reference Library Files, see the image given below.

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:

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 are different and it is important to know them because if we need to remove any of these library files from the Current Project then you must use the Project Name in programs rather than using the above filenames.

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 Debug Window using the statement x = Application.GetOption("Project Name").

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

The List of Library Files given above; also has unique Project Names and these are the Internal Object Library Reference names that will appear in our database when linked. You can check their names by opening the Object Browser (View - -> Object Browser or F2) and clicking on the drop-down control. Among the list, you can see that your own current database Project Name abcd also appears in 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 the Reference Library File with the same Project Name then a conflict takes place. We have to check for the following two more References to avoid removing them before attaching 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

b). The first stage of our preparation is over. The next thing that we need is a VBA Program that can read the above Text File contents and link the Reference Library files shown in the second image above automatically to your new Project.

The VBA Routine is given below, but you have two choices to decide where to place the code so that it is easier to use in all of your Projects without duplicating the code. The second choice is to copy the code into your new Projects and run it from there, which I don't advise you to do. Instead, take the first option and do a little groundwork now by following the few steps given below. This will make your future development processes easier without duplicating common Programs in all your other Projects.

If you have not started to build a Reference Library Database of your own, then this is the time to start doing it and you will find how useful it is. It is not that hard to do it either.

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 take this location address the same as 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 may 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, if you would like to save a copy of the Library Database in a Compiled state you can create it by running Tools- ->Database Utilities - - > Make MDE File option and name the database as MyLib.MDE. You may save it at the same location as the .mdb file and move the .mdb file to your local drive or to your private server location where access to others is forbidden. When you add more common routines to this Library file, you may recompile it and replace it with the earlier file so that the new programs can be made available to all your projects without making any changes to them. You can attach your Library Database MyLib.mde, along with others to all of your future and current running Projects.

We have two programs; the second one is to validate the existing attached References and remove them except the essential ones mentioned above 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 itself and attach other Library Files List we have 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