MS-Access and Reference Library
We are using several Object Libraries besides the default References Library during the course of 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 to create Data Processing Routines in VBA.
I have briefly touched this subject in my first Article in this site: Command Button Animation and advised to link these files to your Database manually before attempting to run the Program given in there. A List of essential Library Files and the procedures to attach them to the Database manually also explained there.
Some of these Library References like: Microsoft Access 9.0 Object Library, Visual Basic for Applications, OLE Automation are attached automatically when a new database is created and others are added manually. We add new reference Libraries to use interesting features extended by them, which are not available in Microsoft Access.
We have used Microsoft Office Library (Office) to create customized Message Boxes, Option Baloons and Check-Boxes etc. with Office Assistant to capture user responses in an interesting way. We have created a Wizard to select Office Assistant 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 Access Database.
If you have written some common Functions of your own then you can save them in a separate database on Server and attach it as a Library Database so that you don’t have to copy and paste those Functions in other Databases.
You can use Object Browser of VBA (View – – > Object Browser) to browse the Properties and Methods extended by a particular Library.
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 and it is useful to quickly restore the links if they are lost as well.
First let us look at the few steps that we need to take to accomplish this.
A. Prepare a list of Library References and save it in a Text File, on 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 them 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 what is 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 manually, open VBA Module Window (View – – > Code or Alt+F11), select Tools- – >References and find the item with 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; its Description, the physical File Pathname 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.
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 and 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:\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
The internal names (Project Names) of the above Library Files are different and it is important to know about it because if we need to remove any of this library items from the Current Project then you must use the Project Name in programs rather than using the above file names.
When you create 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 selecting 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.
Note: But, it is important that you should not use any names of your Standard Modules for the Project Name.
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 have 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 is also appearing in the list.
Let us inspect the Project Names of the first three files in the list given above.
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 it on your Server 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
B. The first stage of our preparation is over. The next thing what 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.
I will give the VBA Routines 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 little ground work 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 so far 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.
- Create a new Database with the name MyLib.mdb and save it to your Server Common Location. Let us take this location address same as the one we have saved 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 of the .mdb file and move the .mdb file to your local drive or in your private server location where access to others are forbidden. When you add more common routines into this Library file you can recompile it and replace the earlier .MDE file so that the new programs can be made available to all your Projects without making any changes in 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 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 with your own.
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 Enter key to do that:
You can now check the Reference Library List to re-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 Enter key to add all required Reference Libraries to your Project at once: