How to register library/reference from vba code?

amorosik

Active member
Local time
Today, 19:43
Joined
Apr 18, 2020
Messages
662
I have an Access procedure that searches for newer versions upon startup
If available, downloads them to the root program directory and creates a new icon on the desktop to launch them
Sometimes, newer versions require an additional library in the references; otherwise, the code won't work correctly
The question is: how can I verify that all the required libraries are loaded, and if not, how can I start the library registration process (using VBA code, I mean)?
 
Last edited:
OK, first... references are found via Application.References, which you can look up with a browser. If you have Google with Gemini, you can ask for it to tell you "vba to manipulate ms access references" and you'll get a list. You can do AddFromFile or AddFromGUID to add a reference, and Remove to remove a reference. Look up those items to get some example code.

Another good search string is "Add references programatically with VBA"
 
But when you see two libraries missing from your code, how do you figure out which ones you're talking about?
And more importantly, how do you register them from VBA?
 
Perhaps my articles will help?


Its unlikely you will need to register a missing reference library before adding it, but to do so, you would use regsvr32 from a command prompt
 
What results do you get when you try using Doc's suggested search?

All articles that show how to add a reference from codeAnd this is possible if you know which library you're talking about
But when you loop to check if a library is available, and the code doesn't find it, how do you figure out which library it is and register it in the operating system?
 
Am I understanding you correctly: you want to perform the COM registration of the library?
Like
Code:
regsvr32 "Y:\our\COM\File.dll"


.. how do you figure out which library it is
You can read the guid value:
Code:
vbe.ActiveVBProject.References(index of reference).Guid
 
Last edited:
All articles that show how to add a reference from codeAnd this is possible if you know which library you're talking about
But when you loop to check if a library is available, and the code doesn't find it, how do you figure out which library it is and register it in the operating system?
If Colin's links above don't help, then post the code you use and explain where it faults for you.

Are you saying your target machines do not have the required dll's registered on them?
 
you can Save all Reference to a Table (including the GUID).
when you copy and Open the new db, there will be a startup code.
to AddReference by GUID.
refTable.jpg


Code:
' Export all reference information to a table
Public Sub ExportReferencesToTable()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ref As Object
    Dim i As Integer
    Dim tableName As String
 
    tableName = "tblReferences"
    Set db = CurrentDb
 
    On Error Resume Next
    ' Delete table if exists
    db.TableDefs.Delete tableName
    On Error GoTo 0
 
    ' Create table
    db.Execute "CREATE TABLE " & tableName & " (" & _
        "RefID AUTOINCREMENT PRIMARY KEY, " & _
        "RefName TEXT(255), " & _
        "FullPath TEXT(255), " & _
        "GUID_ TEXT(50), " & _
        "MajorVersion INTEGER, " & _
        "MinorVersion INTEGER, " & _
        "IsBuiltIn YESNO, " & _
        "IsMissing YESNO)"
 
    ' Open recordset
    Set rs = db.OpenRecordset(tableName, dbOpenDynaset)
 
    ' Add all references
    For i = 1 To Application.References.Count
        Set ref = Application.References(i)
     
        rs.AddNew
        rs!refName = ref.Name
        rs!fullPath = ref.fullPath
        rs!GUID_ = ref.GUID
        rs!MajorVersion = ref.major
        rs!MinorVersion = ref.minor
        rs!IsBuiltIn = ref.BuiltIn
        rs!IsMissing = ref.IsBroken
        rs.Update
    Next i
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
    Debug.Print "References exported to table: " & tableName
    MsgBox "References exported to table: " & tableName, vbInformation
End Sub

' Reinstate all reference from tblReference
Public Sub ReInstateReference()

    With CurrentDb.OpenRecordset("tblReferences")
        .MoveFirst
        Do Until .EOF
            If Not ReferenceExists(!refName) Then
                Application.References.AddFromGuid !GUID_, !MajorVersion, !MinorVersion
            End If
            .MoveNext
        Loop
    End With
          
End Sub

' Check if a specific reference exists
Public Function ReferenceExists(refName As String) As Boolean
    Dim ref As Object
    Dim i As Integer
 
    For i = 1 To Application.References.Count
        Set ref = Application.References(i)
        If UCase(ref.Name) = UCase(refName) Then
            If Not ref.IsBroken Then
                ReferenceExists = True
                Exit Function
            End If
        End If
    Next i
 
    ReferenceExists = False
End Function
 
Last edited:
Am I understanding you correctly: you want to perform the COM registration of the library?
Like
Code:
regsvr32 "Y:\our\COM\File.dll"



You can read the guid value:
Code:
vbe.ActiveVBProject.References(index of reference).Guid

Ok, but once you know the guid, how do you know which library it corresponds to?
 
you can Save all Reference to a Table (including the GUID).
when you copy and Open the new db, there will be a startup code.
to AddReference by GUID.
View attachment 121907

Code:
' Export all reference information to a table
Public Sub ExportReferencesToTable()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ref As Object
    Dim i As Integer
    Dim tableName As String
 
    tableName = "tblReferences"
    Set db = CurrentDb
 
    On Error Resume Next
    ' Delete table if exists
    db.TableDefs.Delete tableName
    On Error GoTo 0
 
    ' Create table
    db.Execute "CREATE TABLE " & tableName & " (" & _
        "RefID AUTOINCREMENT PRIMARY KEY, " & _
        "RefName TEXT(255), " & _
        "FullPath TEXT(255), " & _
        "GUID_ TEXT(50), " & _
        "MajorVersion INTEGER, " & _
        "MinorVersion INTEGER, " & _
        "IsBuiltIn YESNO, " & _
        "IsMissing YESNO)"
 
    ' Open recordset
    Set rs = db.OpenRecordset(tableName, dbOpenDynaset)
 
    ' Add all references
    For i = 1 To Application.References.Count
        Set ref = Application.References(i)
    
        rs.AddNew
        rs!refName = ref.Name
        rs!fullPath = ref.fullPath
        rs!GUID_ = ref.GUID
        rs!MajorVersion = ref.major
        rs!MinorVersion = ref.minor
        rs!IsBuiltIn = ref.BuiltIn
        rs!IsMissing = ref.IsBroken
        rs.Update
    Next i
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
    Debug.Print "References exported to table: " & tableName
    MsgBox "References exported to table: " & tableName, vbInformation
End Sub

' Reinstate all reference from tblReference
Public Sub ReInstateReference()

    With CurrentDb.OpenRecordset("tblReferences")
        .MoveFirst
        Do Until .EOF
            If Not ReferenceExists(!refName) Then
                Application.References.AddFromGuid !GUID_, !MajorVersion, !MinorVersion
            End If
            .MoveNext
        Loop
    End With
         
End Sub

' Check if a specific reference exists
Public Function ReferenceExists(refName As String) As Boolean
    Dim ref As Object
    Dim i As Integer
 
    For i = 1 To Application.References.Count
        Set ref = Application.References(i)
        If UCase(ref.Name) = UCase(refName) Then
            If Not ref.IsBroken Then
                ReferenceExists = True
                Exit Function
            End If
        End If
    Next i
 
    ReferenceExists = False
End Function


Ah ok
A table with library <->guid
 
At some point you will have a library file reference that was valid on the machine that developed the app, and you can store the full file spec and/or GUID in a table. Then if you have the file spec, use some of the methods in FileSystemObject to parse out that spec, i.e. extract path and name and type to do a directory search for that file. There IS such a thing as doing a "LIKE" comparison of file names if, say, you WERE using MSADO20 and searched for FILENAME LIKE "MSADO*" to find MSADO21. Or something conceptually similar to that.

I'm having trouble understanding, though, because there is an apparent nuance of "if I've never used it before, how do I find it?" (At least, in a liberal reading of your posts, I think I see that.) For Windows and Office libraries,the locations are usually consistent and predictable. For 3rd-party libraries, the locations are usually predictable based on where the product was installed. But for any type of library, in the final analysis you found out what to reference by reading about it as the result of some web search article that would tell you about the library, and the article would also tell you where to find the files. You don't just try to reference something "out of the blue" so I'm trying to decide how you got into the situation of not knowing what you are looking for, yet knowing you need to look for it.

I may have been confused by trying to "read between the lines" to get at what you are asking, and if I have misread your intent, I apologize for any confusion.
 
you can Save all Reference to a Table (including the GUID).
when you copy and Open the new db, there will be a startup code.
to AddReference by GUID.
View attachment 121907

Code:
' Export all reference information to a table
Public Sub ExportReferencesToTable()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ref As Object
    Dim i As Integer
    Dim tableName As String
 
    tableName = "tblReferences"
    Set db = CurrentDb
 
    On Error Resume Next
    ' Delete table if exists
    db.TableDefs.Delete tableName
    On Error GoTo 0
 
    ' Create table
    db.Execute "CREATE TABLE " & tableName & " (" & _
        "RefID AUTOINCREMENT PRIMARY KEY, " & _
        "RefName TEXT(255), " & _
        "FullPath TEXT(255), " & _
        "GUID_ TEXT(50), " & _
        "MajorVersion INTEGER, " & _
        "MinorVersion INTEGER, " & _
        "IsBuiltIn YESNO, " & _
        "IsMissing YESNO)"
 
    ' Open recordset
    Set rs = db.OpenRecordset(tableName, dbOpenDynaset)
 
    ' Add all references
    For i = 1 To Application.References.Count
        Set ref = Application.References(i)
   
        rs.AddNew
        rs!refName = ref.Name
        rs!fullPath = ref.fullPath
        rs!GUID_ = ref.GUID
        rs!MajorVersion = ref.major
        rs!MinorVersion = ref.minor
        rs!IsBuiltIn = ref.BuiltIn
        rs!IsMissing = ref.IsBroken
        rs.Update
    Next i
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
    Debug.Print "References exported to table: " & tableName
    MsgBox "References exported to table: " & tableName, vbInformation
End Sub

' Reinstate all reference from tblReference
Public Sub ReInstateReference()

    With CurrentDb.OpenRecordset("tblReferences")
        .MoveFirst
        Do Until .EOF
            If Not ReferenceExists(!refName) Then
                Application.References.AddFromGuid !GUID_, !MajorVersion, !MinorVersion
            End If
            .MoveNext
        Loop
    End With
        
End Sub

' Check if a specific reference exists
Public Function ReferenceExists(refName As String) As Boolean
    Dim ref As Object
    Dim i As Integer
 
    For i = 1 To Application.References.Count
        Set ref = Application.References(i)
        If UCase(ref.Name) = UCase(refName) Then
            If Not ref.IsBroken Then
                ReferenceExists = True
                Exit Function
            End If
        End If
    Next i
 
    ReferenceExists = False
End Function

Sweet.... Consider adding a timestamp field to tblReferences to track when the export occurred:

SQL: ExportedOn DATETIME DEFAULT Now()

This will help with versioning and audit trails.

Code:
Public Sub ExportReferencesToTable_DAO()
    Const tableName As String = "tblReferences"
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rs As DAO.Recordset
    Dim ref As Reference

    Set db = CurrentDb

    ' Delete existing table if it exists
    On Error Resume Next
    db.TableDefs.Delete tableName
    On Error GoTo 0

    ' Create new table definition
    Set tdf = db.CreateTableDef(tableName)

    ' Add fields
    With tdf
        .Fields.Append .CreateField("RefID", dbLong)
        .Fields("RefID").Attributes = dbAutoIncrField

        .Fields.Append .CreateField("RefName", dbText, 255)
        .Fields.Append .CreateField("FullPath", dbText, 255)
        .Fields.Append .CreateField("GUID_", dbText, 50)
        .Fields.Append .CreateField("MajorVersion", dbInteger)
        .Fields.Append .CreateField("MinorVersion", dbInteger)
        .Fields.Append .CreateField("IsBuiltIn", dbBoolean)
        .Fields.Append .CreateField("IsMissing", dbBoolean)
        .Fields.Append .CreateField("ExportedOn", dbDate)
        .Fields("ExportedOn").DefaultValue = "=Now()"
    End With

    ' Append table to database
    db.TableDefs.Append tdf

    ' Open recordset for writing
    Set rs = db.OpenRecordset(tableName, dbOpenDynaset)

    ' Loop through references
    For Each ref In Application.References
        With rs
            .AddNew
            !RefName = ref.Name
            !FullPath = IIf(ref.FullPath <> "", ref.FullPath, "(Built-in or missing)")
            !GUID_ = ref.GUID
            !MajorVersion = ref.Major
            !MinorVersion = ref.Minor
            !IsBuiltIn = ref.BuiltIn
            !IsMissing = ref.IsBroken
            !ExportedOn = Now
            .Update
        End With
    Next ref

    ' Cleanup
    rs.Close
    Set rs = Nothing
    Set tdf = Nothing
    Set db = Nothing

    Debug.Print "References exported to table: " & tableName
    MsgBox "References exported to table: " & tableName, vbInformation
End Sub
 
Last edited:
For 3rd-party libraries, the locations are usually predictable based on where the product was installed. But for any type of library, in the final analysis you found out what to reference by reading about it as the result of some web search article that would tell you about the library, and the article would also tell you where to find the files. You don't just try to reference something "out of the blue" so I'm trying to decide how you got into the situation of not knowing what you are looking for, yet knowing you need to look for it.

I may have been confused by trying to "read between the lines" to get at what you are asking, and if I have misread your intent, I apologize for any confusion.

I understand
These are libraries I created, not the standard ones provided with Access or already present in Windows
For socket tcp, Mqtt, Firebird push message, etc...
 
you need these steps.
1. copy the MySockectConnector.dll to an Attachment field
2. on opening the db, check if the Library is already registered
if already registered, exit the sub/function.
3. if not registered, copy it from Attachment content to any folder (or temp folder)
4. using an Elevated right script, copy the library from temp folder to Windows folder or to Windows\System32 folder.
5. using Elevated right script, run Regsvr32 to register the library.

you can ask Copilot or ChatGPT for those VBA steps.
 
With the standard Windows permissions an ordinary user should also be able to register a DLL just for himself without elevated permissions. (Not in the System directory of course)

Do you say that it is enough to run regasm.exe on a batch file even without logging in as admin?
 

Users who are viewing this thread

Back
Top Bottom