Setting References to Libraries Via VBA Code

[pd2004]

Hello,

I would like to know if there is a way to do the following in Access:

1) determine what libraries are currently referenced in the VBA programming environment

2) Set a reference if it is not already set.

I want to do this with the opening script in VBA so that when a user opens the database they will be assured to have the necessary references.

I appreciate all of the help given on this forum. It has been a real help when I cannot figure things out via the help file.

Thank you,

Pat

 

[HarleyQuinn]

I would have a look at the Application.References collection for this task.

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[fumei]

1. I do not know about Access, but in Word there is no Application.References. You may want to post your question to the Access VBA forum.

In Word, References are properties of documents - or more accurately the VBAProject associated with that document. Each document has its own VBAProject and therefore can have a different set of references from another document.

2. Using VBA to query References requires...a Reference. Microsoft Visual Basic for Applications Extensibility. Otherwise you can not declare a Reference object to use to ask about References. For example:

Sub TypeOutReferences()
Dim oRef As Reference
For Each oRef In ActiveDocument.VBProject.References
   Selection.TypeText oRef.FullPath & _
      vbCrLf & oRef.Description & vbCrLf & vbCrLf
Next
End Sub

produces the following in a document (as it is typed out as text).

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Visual Basic For Applications

C:\Program Files\Microsoft Office\Office10\MSWORD.OLB
Microsoft Word 10.0 Object Library

C:\WINNT\system32\stdole2.tlb
OLE Automation

C:\Program Files\Common Files\Microsoft Shared\Office10\MSO.DLL
Microsoft Office 10.0 Object Library

C:\WINNT\system32\FM20.DLL
Microsoft Forms 2.0 Object Library

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
Microsoft Visual Basic for Applications Extensibility 5.3

Again, that last one is required, otherwise:

Dim oRef As Reference

is invalid and fails with:

Compile error:

User-defined type not defined.

Gerry

 

[pd2004]

Thank you for all of your help. I am making good progress on this. I really appreciate it.

Pat

 

[SkipVought]

FYI, if you want to do this in *gasp* [small]Excel[/small]...

Sub DebugPrintReferences()
    Dim oRef As Object
    For Each oRef In ThisWorkbook.VBProject.References
       Debug.Print oRef.FullPath & _
          vbCrLf & oRef.Description & vbCrLf & vbCrLf
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

I do not know about Access, but in Word there is no Application.References

But there is in Access Gerry, that's why I recommended it

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[TonyJollans]

Some code to do exactly this was posted by long-time ex-member billpower several years ago. The thread is thread181-437632 (in forum181 ) but I am unable to access this at the moment. I reproduce his original code below - there was some discussion and slight enhancement that followed but this should give you a start:

CheckUsersReferences, compares a table of reference names and file names created at design time with a table of the same created at run time:

Table Name: tblReferences
RefName RefFileName
==================
VBA VBA332.DLL
Access MSACC8.OLB
DAO dao2535.tlb

In the above table,

RefName is the reference name as identified by Access, VBA would appear in the reference list in a module as “Visual Basic for Applications”, Access would appear as “Microsoft Access x.0 Object Library” etc.

RefFileName is the actual file name.

To create your own table of references before deployment, paste this into a module on a form:

[blue]Function CreateTableOfMyReferences()
Dim i As Integer, j As Integer  'counters
Dim intCurrPos As Integer   'used by InStr to get 1st "\" in path of file name
Dim intNextPos As Integer   'used by InStr to get subsequent "\" in path of file name
Dim intLength As Integer    'length of path to file name
Dim strRefName As String
Dim strRefFullPath As String
Dim strRefFileName As String
Dim strTable As String      'name of table to hold ref name and file name
Dim strSQL As String
Dim ref As Reference

strTable = "tblReferences"                    'Rename table if name conflicts
DoCmd.SetWarnings False
For Each ref In References
intCurrPos = 1
strRefFullPath = ref.FullPath
intLength = Len(strRefFullPath)
    For i = 1 To intLength Step intCurrPos
        intNextPos = InStr(intCurrPos + 1, strRefFullPath, "\")
        If intNextPos = 0 Then
           strRefFileName = Mid(strRefFullPath, intCurrPos + 1, intLength)
              If j = 0 Then   'if this is th 1st reference make a table with the 1st ref
                   strSQL = "SELECT '" & ref.Name & "' AS RefName, '" & strRefFileName & _
                            "' AS RefFileName INTO " & strTable & ";"
              Else           'table already created, add next ref
                   strSQL = "INSERT INTO " & strTable & " ( RefName, RefFileName ) " & _
                            "SELECT '" & ref.Name & " '" & "  AS RefName, '" & _
                             strRefFileName & " ' AS RefFileName;"
              End If
           DoCmd.RunSQL (strSQL)
           j = j + 1
           Exit For
        End If
        intCurrPos = intNextPos
    Next i
Next
DoCmd.SetWarnings True
End Function[/blue]

Paste this behind a button on the form:

[blue][tt] CreateTableOfMyReferences[/tt][/blue]

CreateTableOfMyReferences will make a table listing all libraries, ocx’s etc used in your app.

========================================

Briefly, when the user of the deployed app logs on the following procedure, CheckUsersReferences, makes a table of references locally on the user’s PC, compares this table to the table above, if there are any missing or broken references, attempts to find the ref, if it’s found attempts to reference it. If the file exists (hasn’t been deleted for some reason), this should be a trouble free exercise.

To use this function paste the following into a global module:

[blue]Declare Function GetSystemDirectory Lib "kernel32.dll" Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long[/blue]

Paste the following into the declarations section of the form that loads at startup:

[blue]Dim strRef As String
Dim strFoundRef As String
Dim strTable As String
Dim strTempTable As String
Dim strTempTableCount As String[/blue]

Paste the following into the module of the form that loads at startup:

[blue]Function CheckUsersReferences()
Dim ref As Reference
Dim strSQL As String
Dim i As Integer, x As Integer
strTable = "tblReferences"                    'Rename table if name conflicts
strTempTable = "tblTempReferences"            'Rename table if name conflicts
strTempTableCount = "tblTempReferencesCount"  'Rename table if name conflicts
   DoCmd.SetWarnings False
   For Each ref In References
      If i = 0 Then
         If ref.IsBroken = False Then
            strSQL = "SELECT '" & ref.Name & "' AS RefName INTO " & strTempTable & ";"
         End If
      Else
         If ref.IsBroken = False Then
            strSQL = "INSERT INTO " & strTempTable & " ( RefName ) SELECT '" & _
                     ref.Name & "' AS RefName;"
         End If
      End If
      DoCmd.RunSQL (strSQL)
      i = i + 1
   Next
   strSQL = "SELECT " & strTable & ".RefFileName INTO " & strTempTableCount & " FROM " & _
            strTable & " LEFT JOIN " & strTempTable & " ON " & strTable & ".RefName = " & _
            strTempTable & ".RefName WHERE (((" & strTempTable & ".RefName) Is Null));"
   DoCmd.RunSQL (strSQL)
   DoCmd.SetWarnings True
   i = DCount("*", strTempTableCount)
   If i = 0 Then
      GoTo ExitCheckMyReferences
   Else
      For x = 1 To i
         strRef = DFirst("[RefFileName]", strTempTableCount)
         strSQL = "DELETE " & strTempTableCount & ".RefFileName FROM " & strTempTableCount & _
         " WHERE (((" & strTempTableCount & ".RefFileName)=" & "'" & strRef & "'));"
         DoCmd.SetWarnings False
         DoCmd.RunSQL (strSQL)
         DoCmd.SetWarnings True
         If SearchForReferencesFileLocation = False Then
            GoTo ExitCheckMyReferences
         End If
         ReferenceThisControl
      Next x
   End If
ExitCheckMyReferences:
   DoCmd.DeleteObject acTable, strTempTable
   DoCmd.DeleteObject acTable, strTempTableCount
End Function

Function SearchForReferencesFileLocation()
Dim i As Integer
Dim sysdir As String
Dim slength As Long
sysdir = Space(255)
slength = GetSystemDirectory(sysdir, 255)
sysdir = Left(sysdir, 3)
SearchForReferencesFileLocation = False
MsgBox "File " & strRef & " is not referenced correctly." & vbCrLf & _
       "MS Access will now attempt to find " & strRef & vbCrLf & _
        "This might take a few minutes.", vbInformation     'display your own message
        With Application.FileSearch
            .NewSearch
            .LookIn = sysdir      'look in system directory, add your own proc to search multiple directories
            .SearchSubFolders = True
            .FileName = strRef
            If .Execute() > 0 Then
                strFoundRef = .FoundFiles(1)
                SearchForReferencesFileLocation = True
            Else
                MsgBox "File " & strRef & " reference was not found." & vbCrLf & _
                             "Please take a note of " & strRef & ", then inform the " & _
                             "System Administrator"    'display your own message
                SearchForReferencesFileLocation = False
            End If
        End With
End Function

Function ReferenceFromFile(strFileName As String) As Boolean
Dim ref As Reference
On Error GoTo Error_ReferenceFromFile
    Set ref = References.AddFromFile(strFileName)
    ReferenceFromFile = True
    Exit Function
    
Error_ReferenceFromFile:
    MsgBox Err & ": " & Err.Description  'display your own message
    ReferenceFromFile = False
End Function

Function ReferenceThisControl()
    If ReferenceFromFile(strFoundRef) = True Then
        MsgBox "File " & strRef & " reference set successfully." & vbCrLf & _
                     "Next time you log on, MS Access will remember this setting" 'display your own message
    Else
        MsgBox "File " & strRef & " reference was not set successfully." & vbCrLf & _
                     "Please take a note of " & strRef & ", then inform the " & _
                     "System Administrator"                                  'display your own message
    End If
End Function[/blue]

Paste the following into the on load event of the form that loads at startup:

[blue][tt] CheckUsersReferences[/tt][/blue]

Though this looks like a lenghty procedure, as long as the deployed app’s references match the design time references, loading time is unnoticably affected and loading continues as normal after the check. Where there is a reference problem, user participation has been kept to a minimum, with just a message telling the user that a search is about to be done.

To test this Function, create a new database, add a form, follow the instructions above, in a module add a reference to it. Run CreateTableOfMyReferences, delete the reference, close and open the database. The deleted reference should be automatically attached. Close the database, open it again, it should open instantly, because all the references agree.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

Hi Andy. I should learn to keep my big mouth shut over area that I am ignorant of. Will I never learn?

Gerry

 

[HarleyQuinn]

Hi Gerry. It's worked out well though, we've now got a thread detailing how to work with references using Access, Word and Excel

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com