Version 1.1 Approx 0.5 MB First Published 25 Sept 2022 Last Updated 8 Jan 2023
This article is the second part of a series of articles about creating databases using code
In the first article in this series, I explained how to Create ACCDB & ACCDE Files using Code
In this article, I will show how to list details of existing references in a table:
Alternatively the reference details can be exported to a text file.
I will also describe 2 methods of adding references in code. This can be useful if you are distributing files and want to ensure that all required references are loaded
For example, this form gives the details of all installed references:
Details of individual references can be displayed by filtering the form using the combo box
NOTE:
The code used requires the reference: Microsoft Visual Basic for Applications Extensibility 5.3
Listing references
Details of existing references can be saved to a table tblRefs with the following fields:
The code used to populate this table is as follows:
CODE
Sub PopulateVBAReferencesTable()
'===============================================
'Author: Colin Riddington, Mendip Data Systems
'Date: 22/09/2022
'Purpose: Populate table tblRefs with details of all installed references
'NOTE:
'This requires the use of the VBA reference library: 'Microsoft Visual Basic for Applications Extensibility 5.3
'==================================================
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim ref As VBIDE.Reference
Dim strRefDescription As String
Set db = CurrentDb
Set VBAEditor = Application.VBE
Set VBProj = VBAEditor.ActiveVBProject
Set ref = VBProj.Reference
'empty existing table
CurrentDb.Execute "DELETE * FROM tblRefs"
Set rs = CurrentDb.OpenRecordset("tblRefs")
For Each ref In VBProj.References
With rs
.AddNew
!RefName = ref.Name
!Description = ref.Description
!Path = ref.FullPath
!GUID = ref.GUID
!Type = ref.Type
!BuiltIn = ref.BuiltIn
!IsBroken = ref.IsBroken
!Major = ref.Major
!Minor = ref.Minor
.Update
End With
Next ref
Set rs = Nothing
FormattedMsgBox "The table tblRefs has been updated. " & _
"@There are " & DCount("*", "tblRefs") & " references installed @" & _
"Number of broken references = " & DCount("*", "tblRefs", "IsBroken=True"), vbInformation, "VBA References"
End Sub
Alternatively, the reference details can be saved to an external text file VBAReferenceLog.txt as shown above
CODE
Public Sub LogVBAReferences()
'==================================================
'Author: Colin Riddington, Mendip Data Systems
'Date: 08/05/2017
'Purpose: Log all references in text file
'Adapted from code by Dirk Goldgar/Tom van Stiphout(DevHut website)
'NOTE:
'This requires the use of the VBA reference library: 'Microsoft Visual Basic for Applications Extensibility 5.3
'==================================================
On Error Resume Next
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim objFSO As Object
Dim logStream As Object
Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim ref As VBIDE.Reference
Dim strFilename As String
Dim strRefDescription As String
Dim lngCount As Long
Dim lngBrokenCount As Long
Dim blnBroken As Boolean
strFilename = CurrentProject.Path & "\VBAReferenceLog.txt"
Set VBAEditor = Application.VBE
Set VBProj = VBAEditor.ActiveVBProject
Set ref = VBProj.Reference
Set objFSO = CreateObject("Scripting.FileSystemObject")
'check if VBA reference text file exists
If Dir(strFilename) <> "" Then
'Delete current log File
Kill strFilename
End If
'Create text file & enter version info
Set logStream = objFSO.OpenTextFile(strFilename, ForWriting, True)
logStream.WriteLine ""
logStream.WriteLine "VBA Reference Log File" & vbNewLine & _
"======================" & vbNewLine & vbNewLine & _
"Application Path: " & Application.CurrentProject.FullName & vbNewLine & _
"Date/Time: " & Date & " - " & Time() & vbNewLine & vbNewLine
'now loop through references collection and log info for each
logStream.WriteLine "REFERENCES"
logStream.WriteLine "-------------------------------------------------"
logStream.WriteLine ""
For Each ref In VBProj.References
lngCount = lngCount + 1
strRefDescription = vbNullString
Err.Clear
logStream.WriteLine " Description: " & vbTab & vbTab & ref.Description
If Err.Number <> 0 Then
logStream.WriteLine " Description: " & vbTab & vbTab & "(error " & Err.Number & ")"
blnBroken = True
End If
Err.Clear
logStream.WriteLine " Name: " & vbTab & vbTab & vbTab & ref.Name
If Err.Number <> 0 Then
logStream.WriteLine " Name: " & vbTab & vbTab & vbTab & "(error " & Err.Number & ")"
blnBroken = True
End If
Err.Clear
logStream.WriteLine " FullPath: " & vbTab & vbTab & ref.FullPath
If Err.Number <> 0 Then
logStream.WriteLine " FullPath: " & vbTab & vbTab & "(error " & Err.Number & ")"
blnBroken = True
End If
Err.Clear
logStream.WriteLine " Guid: " & vbTab & vbTab & vbTab & ref.GUID
If Err.Number <> 0 Then
logStream.WriteLine " Guid: " & vbTab & vbTab & vbTab & "(error " & Err.Number & ")"
blnBroken = True
End If
Err.Clear
logStream.WriteLine " Version(Major/Minor): " & vbTab & ref.Major & "." & ref.Minor
If Err.Number <> 0 Then
logStream.WriteLine " Version(Major/Minor): " & vbTab & "(error " & Err.Number & ")"
blnBroken = True
End If
Err.Clear
logStream.WriteLine " Type: " & vbTab & vbTab & vbTab & ref.Type
If Err.Number <> 0 Then
logStream.WriteLine " Type: " & vbTab & vbTab & vbTab & "(error " & Err.Number & ")"
blnBroken = True
End If
Err.Clear
logStream.WriteLine " BuiltIn: " & vbTab & vbTab & ref.BuiltIn
If Err.Number <> 0 Then
logStream.WriteLine " BuiltIn: " & vbTab & vbTab & "(error " & Err.Number & ")"
blnBroken = True
End If
Err.Clear
logStream.WriteLine " IsBroken: " & vbTab & vbTab & ref.IsBroken
If Err.Number <> 0 Then
logStream.WriteLine " IsBroken: " & vbTab & vbTab & "(error " & Err.Number & ")"
blnBroken = True
End If
If blnBroken Then
Debug.Print "Broken Reference: " & ref.Name
lngBrokenCount = lngBrokenCount + 1
strRefDescription = "*BROKEN* " & strRefDescription
End If
'Debug.Print strRefDescription
logStream.WriteLine "-------------------------------------------------"
logStream.WriteLine ""
blnBroken = False
Next ref
logStream.WriteLine lngCount & " references found, " & lngBrokenCount & " broken."
If lngBrokenCount > 0 Then
MsgBox "Broken References were found in the VBA Project!", vbCritical + vbOKOnly
End If
logStream.Close
If FormattedMsgBox("The VBA references data has been saved as 'VBAReferenceLogFile.txt' in the folder " & vbCrLf & _
vbTab & CurrentProject.Path & _
"@Do you want to view the text file now? @", vbInformation + vbYesNo, "Text file saved") = vbYes Then
Application.FollowHyperlink CurrentProject.Path & "\VBAReferenceLogFile.txt"
End If
'open log file
Application.FollowHyperlink strFilename
End Sub
Adding references
The standard method of adding references is using the Tools . . . References window in the Visual Basic Editor
If you want to do this using code, there are 2 methods available:
a) Using GUID - requires knowledge of the GUID e.g. from checking registry keys
CODE
Sub AddReferenceFromGUID(strGUID As String)
'Credits: Ken Puls
'Purpose: To add a reference to the project using the GUID for the reference library
Dim theRef As Variant, I As Long
Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim ref As VBIDE.Reference
Set VBAEditor = Application.VBE
Set VBProj = VBAEditor.ActiveVBProject
'Set to continue in case of error
On Error Resume Next
'Remove any missing references
For I = VBProj.References.Count To 1 Step -1
Set theRef = VBProj.References.Item(I)
If theRef.IsBroken = True Then
VBProj.References.Remove theRef
End If
Next I
'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear
'Add the reference
VBProj.References.AddFromGuid _
GUID:=strGUID, Major:=1, Minor:=0
'If an error was encountered, inform the user
Select Case Err.Number
Case Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine & _
"add or remove a reference in this file" & vbNewLine & "Please check the " & _
"references in your VBA project!", vbCritical + vbOKOnly, "Error!"
End Select
On Error GoTo 0
End Sub
Typical usage: 'This adds the Word 16.0 Object Library
AddReferenceFromGUID "{00020905-0000-0000-C000-000000000046}"
b) Using the file path – the path may change depending on Office version & bitness
CODE
Sub AddReferenceFromFile(strRefName As String, strFullPath As String)
'Purpose: To add a reference to the project using the file for the reference library
Dim VBAEditor As VBIDE.VBE
Dim VBProj As VBIDE.VBProject
Dim ref As VBIDE.Reference
Dim blnExists As Boolean
Set VBAEditor = Application.VBE
Set VBProj = VBAEditor.ActiveVBProject
'Check if specified reference is already installed
For Each ref In VBProj.References
If ref.Name = strRefName Then
blnExists = True
GoTo CleanUp
End If
Next
VBProj.References.AddFromFile strFullPath
CleanUp:
If blnExists = True Then
MsgBox "Reference already exists"
Else
MsgBox "Reference Added Successfully"
End If
Set VBProj = Nothing
Set VBAEditor = Nothing
End Sub
Typical usage:
AddReferenceFromFile "VBIDE", "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
Download:
Two example databases have been created with all code described in this article:
a) EditRefs.accdb - with the 4 standard references + Microsoft Office 16.0 Object Library + Microsoft Visual Basic for Applications Extensibility 5.3
b) EditRefs48.accdb - with details of 48 of the VBA reference libraries that can be used with Access.
This is FAR MORE than should ever be needed for a working database and has been created purely for demonstration purposes.
NOTE:
This database was created in 32-bit Access 365.
You may get MISSING reference errors depending on your Access version/bitness
Both databases are included in the zip file below together with a text file with details of the 48 references in b) above
Click to download: EditRefs_v1.1 (zipped)
The third article in this series discusses setting default database properties using a template file (.ACCDT) and using the little known Application Parts feature.
See Application Parts and Templates
I hope you found this information useful. If so, please consider giving a donation to help offset the costs in running this site.
If you have any questions, please contact me using the feedback form below.
Colin Riddington Mendip Data Systems Last Updated 8 Jan 2023
Return to Code Samples Page
Return to Top