References--Names and files (1 Viewer)

jdraw

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Jan 23, 2006
Messages
13,409
Does anyone have a link or information regarding MS Access references and the associated files? I have Access 2010 and an Office365 account. I don't use the Off365 very often.
I recently posted a database for a poster who is using 2007. I created the database, saved it as accdb. The poster has an issue, and when I open the database I see it has a reference to Office 16 Access database engine, which he likely does not have. (That may not be the issue but...)
I was trying to find what file(dll/tlb??) that that reference equates to. As you can see in the attached jpg, the graphic does not show the complete file name associated with the reference.
I don't believe there is anything special in that database that requires 2016 components.
I wanted to remove the reference to Office 16 Access database engine and replace it with something from 2010 or common so that the poster could use the database.

Does anyone have a solution/comment/link?? to resolve this?

TIA.
 

Attachments

  • ReferenceQuestion.jpg
    ReferenceQuestion.jpg
    40.6 KB · Views: 42
Last edited:

June7

AWF VIP
Local time
Today, 09:13
Joined
Mar 9, 2014
Messages
3,362
First give them a basic db to test with - one table, no code and no additional reference libraries, just the 4 created by default. Do they still have issue? What is the issue - can't open?
 

isladogs

CID VIP
Local time
Today, 18:13
Joined
Jan 14, 2017
Messages
14,038
Hi

I do wish MS would improve the references window so it can be resized & show the full path ....

Anyway, by pure chance I found this code earlier today at:
https://www.devhut.net/category/ms-access/ms-access-vba/

Code:
Sub ListVBAReferences()
'Authors: Dirk Goldgar
'   Contributor: Tom van Stiphout
    On Error Resume Next
 
    Dim ref As Access.Reference
    Dim strRefDescription As String
    Dim lngCount As Long
    Dim lngBrokenCount As Long
    Dim blnBroken As Boolean
 
    Debug.Print "REFERENCES"
    Debug.Print "-------------------------------------------------"
 
    For Each ref In Application.References
        blnBroken = False
        lngCount = lngCount + 1
        strRefDescription = vbNullString
 
        Err.Clear
        strRefDescription = strRefDescription & "Name: '" & ref.Name & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & "Name: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", FullPath: '" & ref.FullPath & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", FullPath: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Guid: " & ref.GUID
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Guid: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Kind: '" & ref.Kind & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Kind: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", BuiltIn: " & ref.BuiltIn
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", BuiltIn: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", IsBroken: " & ref.IsBroken
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", IsBroken: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Major: " & ref.Major
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Major: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Minor: " & ref.Minor
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Minor: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        If blnBroken Then
            lngBrokenCount = lngBrokenCount + 1
            strRefDescription = "*BROKEN* " & strRefDescription
        End If
 
        Debug.Print strRefDescription
 
    Next ref
 
    Debug.Print "-------------------------------------------------"
    Debug.Print lngCount & " references found, " & lngBrokenCount & " broken."
 
    If lngBrokenCount <> 0 Then
        MsgBox "Broken References were found in the VBA Project!", vbCritical + vbOKOnly
    End If
End Sub

This gives a full listing for each reference & indicates which, if any, are broken.
Using that I get the following info for that reference:

Name: 'DAO', FullPath: 'C:\Program Files\Microsoft Office\Root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\ACEDAO.DLL', Guid: {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}, Kind: '0', BuiltIn: False, IsBroken: False, Major: 12, Minor: 0

N.B. I have no idea what 'Kind' 'Major' & 'Minor' are!

The Office 2010 equivalent is:
C:\Program Files\Microsoft Office\Office14\ACEDAO.dll

Hope that helps

EDIT: Updated version below which includes the reference description
 
Last edited:

isladogs

CID VIP
Local time
Today, 18:13
Joined
Jan 14, 2017
Messages
14,038
Follow up to my last post ....
In my view, the only problem with the code from DevHut is that it doesn't contain the reference description.
This is because the Access.Reference collection doesn't include that as a collection member.

However if you use the 'Microsoft Visual Basic for Applications Extensibility 5.3' reference library, it also has a reference collection and that does include the description. Perfect!

So I've adapted the code to use that library as follows:

Code:
Sub ListVBIDEReferences()

'===============================================
'Author: Colin Riddington, MendipDataSystems
'Date: 08/05/2017

'Adapted from code by Dirk Goldgar/Tom van Stiphout

'NOTE:
'This requires the use of theVBA reference library:
'Microsoft Visual Basic for Applications Extensibility 5.3
'==================================================

    On Error Resume Next
 
    Dim VBAEditor As VBIDE.VBE
    Dim VBProj As VBIDE.VBProject
    Dim ref As VBIDE.Reference
    
    Dim strRefDescription As String
    Dim lngCount As Long
    Dim lngBrokenCount As Long
    Dim blnBroken As Boolean
    
    Set VBAEditor = Application.VBE
    Set VBProj = VBAEditor.ActiveVBProject
    Set ref = VBProj.Reference
 
    Debug.Print "REFERENCES"
    Debug.Print "-------------------------------------------------"
 
    For Each ref In VBProj.References
        blnBroken = False
        lngCount = lngCount + 1
        strRefDescription = vbNullString
        
        Err.Clear
        strRefDescription = strRefDescription & "Description: '" & ref.Description & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & "Description: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Name: '" & ref.Name & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Name: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
         
        Err.Clear
        strRefDescription = strRefDescription & ", FullPath: '" & ref.FullPath & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", FullPath: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Guid: " & ref.GUID
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Guid: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
    'OMIT 'Kind' - not a member of the VBIDE reference collection
      '  Err.Clear
      '  strRefDescription = strRefDescription & ", Kind: '" & ref.Kind & "'"
      '  If Err.Number <> 0 Then
      '      strRefDescription = strRefDescription & ", Kind: " & "(error " & Err.Number & ")"
      '      blnBroken = True
      '  End If
      
        Err.Clear
        strRefDescription = strRefDescription & ", Type: '" & ref.Type & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Type: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", BuiltIn: " & ref.BuiltIn
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", BuiltIn: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", IsBroken: " & ref.IsBroken
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", IsBroken: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Major: " & ref.Major
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Major: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Minor: " & ref.Minor
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Minor: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        If blnBroken Then
            lngBrokenCount = lngBrokenCount + 1
            strRefDescription = "*BROKEN* " & strRefDescription
        End If
 
        Debug.Print strRefDescription
 
    Next ref
 
    Debug.Print "-------------------------------------------------"
    Debug.Print lngCount & " references found, " & lngBrokenCount & " broken."
 
    If lngBrokenCount <> 0 Then
        MsgBox "Broken References were found in the VBA Project!", vbCritical + vbOKOnly
    End If
End Sub

This gives me the following output:

REFERENCES
-------------------------------------------------
Description: 'Visual Basic For Applications', Name: 'VBA', FullPath: 'C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL', Guid: {000204EF-0000-0000-C000-000000000046}, Type: '0', BuiltIn: True, IsBroken: False, Major: 4, Minor: 1
Description: 'Microsoft Access 14.0 Object Library', Name: 'Access', FullPath: 'C:\Program Files\Microsoft Office\Office14\MSACC.OLB', Guid: {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}, Type: '0', BuiltIn: True, IsBroken: False, Major: 9, Minor: 0
Description: 'OLE Automation', Name: 'stdole', FullPath: 'C:\Windows\System32\stdole2.tlb', Guid: {00020430-0000-0000-C000-000000000046}, Type: '0', BuiltIn: False, IsBroken: False, Major: 2, Minor: 0
Description: 'Microsoft Office 16.0 Access database engine Object Library', Name: 'DAO', FullPath: 'C:\Program Files\Microsoft Office\Root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\ACEDAO.DLL', Guid: {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}, Type: '0', BuiltIn: False, IsBroken: False, Major: 12, Minor: 0
Description: 'Microsoft Office 14.0 Object Library', Name: 'Office', FullPath: 'C:\Program Files\Common Files\Microsoft Shared\OFFICE14\MSO.DLL', Guid: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}, Type: '0', BuiltIn: False, IsBroken: False, Major: 2, Minor: 5
Description: 'Microsoft ActiveX Data Objects 6.1 Library', Name: 'ADODB', FullPath: 'C:\Program Files\Common Files\System\ado\msado15.dll', Guid: {B691E011-1797-432E-907A-4D8C69339129}, Type: '0', BuiltIn: False, IsBroken: False, Major: 6, Minor: 1
Description: 'Microsoft HTML Object Library', Name: 'MSHTML', FullPath: 'C:\Windows\System32\mshtml.tlb', Guid: {3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}, Type: '0', BuiltIn: False, IsBroken: False, Major: 4, Minor: 0
Description: 'Microsoft Internet Controls', Name: 'SHDocVw', FullPath: 'C:\Windows\System32\ieframe.dll', Guid: {EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}, Type: '0', BuiltIn: False, IsBroken: False, Major: 1, Minor: 1
Description: 'Microsoft Forms 2.0 Object Library', Name: 'MSForms', FullPath: 'C:\Program Files\Microsoft Office\Root\VFS\SystemX86\FM20.DLL', Guid: {0D452EE1-E08F-101A-852E-02608C4D0BB4}, Type: '0', BuiltIn: False, IsBroken: False, Major: 2, Minor: 0
Description: 'Microsoft Visual Basic for Applications Extensibility 5.3', Name: 'VBIDE', FullPath: 'C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB', Guid: {0002E157-0000-0000-C000-000000000046}, Type: '0', BuiltIn: False, IsBroken: False, Major: 5, Minor: 3
Description: 'Microsoft XML, v6.0', Name: 'MSXML2', FullPath: 'C:\Windows\System32\msxml6.dll', Guid: {F5078F18-C551-11D3-89B9-0000F81FE221}, Type: '0', BuiltIn: False, IsBroken: False, Major: 6, Minor: 0
-------------------------------------------------
11 references found, 0 broken.

The VBA references for this database are shown in the screenshot:
 

Attachments

  • VBAReferences.PNG
    VBAReferences.PNG
    18 KB · Views: 31
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Jan 23, 2006
Messages
13,409
Error message is attached. It occurs immediately when click frmStart button when working with the posters database.

My original version -the sample application - works fine on my machine.
Could be I'm missing something basic with his database???

June,
The thread involved is
https://www.access-programmers.co.uk/forums/showthread.php?t=293497

I'm guessing it is related to references, but I'm not certain.

Ridders,

Thanks for the sub. I have seen it before. I am not getting a missing references, but an immediate error re failure to communicate with OLE server etc.
 

Attachments

  • ReferenceOterError.jpg
    ReferenceOterError.jpg
    33.5 KB · Views: 40
Last edited:

isladogs

CID VIP
Local time
Today, 18:13
Joined
Jan 14, 2017
Messages
14,038
Hi

Ah sorry - must have misunderstood your post ...

I've seen that message myself.
However, it was a long time ago so can't help tell you how to fix.
That is apart from the usual decompile, recompile, compact but I expect this has already been tried.

Anyway, I think the 2nd version of my post which includes the reference description could be really useful to anyone who ever needs to create a fresh copy of their database.

When I've done this in the past, I've taken a screenshot of the reference window and then searched the hard drive for obscure references. This will make life easier
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Jan 23, 2006
Messages
13,409
I've seen it before, but can't recall any specific solution either.

I again just downloaded the posters file and did a debug compile,nothing problematic, but when i went to the form and clicked the button got the ole ...error ???
 

isladogs

CID VIP
Local time
Today, 18:13
Joined
Jan 14, 2017
Messages
14,038
Just a thought...
Could it be due to a missing add-in?
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Jan 23, 2006
Messages
13,409
Ridders,

Possible, but I doubt it. I provided a zip with database and a write up (Word) this link. Poster said he used it and it worked fine. Then he added his data/created a database to use the audit with his data. It is this second database (the poster's where the issue is).

Thanks for looking and the responses.
 

isladogs

CID VIP
Local time
Today, 18:13
Joined
Jan 14, 2017
Messages
14,038
For info, I get the same error as you reported
 

isladogs

CID VIP
Local time
Today, 18:13
Joined
Jan 14, 2017
Messages
14,038
No but I have now.
It runs without error.

I wasn't following this thread so haven't actually found out what its all about
 

isladogs

CID VIP
Local time
Today, 18:13
Joined
Jan 14, 2017
Messages
14,038
In case you're interested I'm still playing with the references code I posted earlier.

I've now modified it to create a log file which is much easier to read - see attached

May upload final version to code repository in next day or so
 

Attachments

  • VBAReferenceLog.txt
    3.6 KB · Views: 25
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Jan 23, 2006
Messages
13,409
Thanks for reviewing the database.

I looked at your VBAReferenceLog --very nice format.
 

Users who are viewing this thread

Top Bottom