Getting the true location of VBA reference files in 365 ctr. (1 Viewer)

[NickCoe(UK)]

This is proving rather tricky using Application.References in a loop. It lists what we can already see in Tools/References which for 365 ctr is wrong for many libs.

If anyone's got any suggestions I'll be happy to hear them

I guess my next steps are diving in to the registry and/or searching system directories for name.

CODE:Sub ListTrueReferenceLibraryLocations()
    Dim ref As Reference
    Dim refList As String
    Dim truePath As String
   
    On Error Resume Next ' Handle any errors gracefully
   
    ' Loop through all references
    For Each ref In Application.References
        ' Attempt to resolve the true file path
        truePath = ref.FullPath
        If Err.Number = 0 Then
            refList = refList & "Name: " & ref.Name & vbCrLf
            'refList = refList & "Description: " & ref.Description & vbCrLf
            refList = refList & "True Location: " & truePath & vbCrLf
            refList = refList & "GUID: " & ref.Guid & vbCrLf
            refList = refList & "Built-in: " & ref.BuiltIn & vbCrLf
            refList = refList & "----------------------" & vbCrLf
        Else
            ' Error occurred; possibly a missing reference
            refList = refList & "Name: " & ref.Name & vbCrLf
            refList = refList & "Error retrieving location (missing or invalid reference)." & vbCrLf
            refList = refList & "----------------------" & vbCrLf
            Err.Clear
        End If
    Next ref
   
    ' Output the reference list to the Immediate Window
    Debug.Print refList
   
    On Error GoTo 0 ' Restore error handling
End Sub

Here's a paste of the last efforts results. As you can see it's just repeating what's in tools/references in the editor. It just can't report what I've heard called 'virtualised folders'

Immediate:

Name: VBA
True Location: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Built-in: True
----------------------
Name: Access
True Location: C:\Program Files\Microsoft Office\root\Office16\MSACC.OLB
GUID: {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
Built-in: True
----------------------
Name: ADODB
True Location: C:\Program Files\Common Files\System\ado\msado15.dll
GUID: {B691E011-1797-432E-907A-4D8C69339129}
Built-in: False
----------------------
Name: Office
True Location: C:\Program Files\Common Files\Microsoft Shared\OFFICE16\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Built-in: False
----------------------
Name: VBIDE
True Location: C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
GUID: {0002E157-0000-0000-C000-000000000046}
Built-in: False
----------------------
Name: SHDocVw
True Location: C:\Windows\System32\ieframe.dll
GUID: {EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}
Built-in: False
----------------------
Name: DAO
True Location: C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
GUID: {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}
Built-in: False
----------------------
Name: bpac
True Location: C:\Program Files\Common Files\Brother\b-PAC\bpac.dll\1
GUID: {90359D74-B7D9-467F-B938-3883F4CAB582}
Built-in: False
----------------------

 

[NickCoe(UK)]

I'm going to try and code a recursive search through system directories for the library file names. If I can figure that out it could be useful.

At the moment I'm knocking up a test accdb for users to run and report references, options, office ver/build and so on. Removes ambiguity.

Most of it's pretty standard stuff and working ok. At the moment it's just the actual l;ib locations that are an issue. Why do I want them? Some user machines are coughing on resolving standard functions like Date(), didn't used to, last week was msft update and if I know where the libs are then I can regsrv them if needed.

Actually, I just had a thought drift through my decrepit retired brain, what if I tried Application.vbe so I was examining the editor...

 

[sonic8]

This is proving rather tricky using Application.References in a loop. It lists what we can already see in Tools/References which for 365 ctr is wrong for many libs.

If anyone's got any suggestions I'll be happy to hear them

I guess my next steps are diving in to the registry and/or searching system directories for name.

What exactly do you thing is "wrong"?
If the correct information about the location of a library file is in the Registry, Access should automatically update the reference to that file or still be able to find and use without updating the path.

 

[sonic8]

Why do I want them? Some user machines are coughing on resolving standard functions like Date(), didn't used to, last week was msft update and if I know where the libs are then I can regsrv them if needed.

I would do some more research on the cause of the problem.
It might (rarely!) happen that an Microsoft update breaks references, but then it is usually a problem that cannot be solved by simply registering a library.

 

[The_Doc_Man]

Some user machines are coughing on resolving standard functions like Date()

This says that the culprit, whatever it is, appears BEFORE the VBA library in the References list. You SHOULD be able to open the VBA window then use Tools >> References to scroll through the list of checked libraries to see if any show "broken" or "missing" in the frame below the check list that shows the path to the particular file.

 

[isladogs]

In case it helps, see my articles with example apps plus code:

Reference List & Locations

This article describes two simple routines giving a list of VBA references & their locations.

isladogs.co.uk

Add References Using Code

This article explains how to list references in a table or save these to a text file. It also describes 2 methods of adding references in code - using GUID or file name

isladogs.co.uk

 

[NickCoe(UK)]

What exactly do you thing is "wrong"?
If the correct information about the location of a library file is in the Registry, Access should automatically update the reference to that file or still be able to find and use without updating the path.

The actual location of the lib files does not match what is reported in vbe/tools/references.
I'm running 365 Ent 64bit. Access build ver2410. With MZTools, V-Tools, Colin's SQL to VBA, Total Access Analyzer and so on.

Been meddling with Access since v1. Arthritis in hands means brief typed messages.

View attachment 117129
Back to refs - For example if I go look at this supposed location above...View attachment 117131
After 'C:\Program Files\Common Files\microsoft shared' the trail disappears...
So I've found where the lib is using search. But I don't want to have to do this manually on a bunch of users machines, or have it done by someone in the office since I wfh and am semi-retired.

I'm taking a step back and rebuilding the app FE. Import into blank new db. decompile and all that good stuff.

If I come up with any sane results I'll report back for the sake of completeness.

 

[NickCoe(UK)]

The actual location of the lib files does not match what is reported in vbe/tools/references.
I'm running 365 Ent 64bit. Access build ver2410. With MZTools, V-Tools, Colin's SQL to VBA, Total Access Analyzer and so on.

Been meddling with Access since v1. Arthritis in hands means brief typed messages.

View attachment 117129
Back to refs - For example if I go look at this supposed location above...View attachment 117131
After 'C:\Program Files\Common Files\microsoft shared' the trail disappears...
So I've found where the lib is using search. But I don't want to have to do this manually on a bunch of users machines, or have it done by someone in the office since I wfh and am semi-retired.

I'm taking a step back and rebuilding the app FE. Import into blank new db. decompile and all that good stuff.

If I come up with any sane results I'll report back for the sake of completeness.

Hmmm Looks like I screwed up the screenshots.

Attached are vbe\tools\references and the result of searching for vba7* in file manager from the root of c:

 

[NickCoe(UK)]

And doing a registry search gets:

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.6\0\Win64

C:\Program Files\Microsoft Office\Root\VFS\ProgramFilesCommonX64\Microsoft Shared\VBA\VBA7.1\VBEUI.DLL

Which matches the results from the file manager search but NOT what's reported in the vbe or when using vba to loop through references.

I've also run Colin's excellent references viewer which confirms my findings regarding the references collection in vba.

OK so, the implication is that to derive the actual location of a library will require a) knowing the file name or GUID and b) using that in code to search the registry.

I'll address this later right now I need to figure out why some machines are hanging on Date() and others are not. I still suspect references so I'm going down that route after importing all objects into a new blank template db, decompiling and so on and so forth...

 

[isladogs]

And doing a registry search gets:

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.6\0\Win64

C:\Program Files\Microsoft Office\Root\VFS\ProgramFilesCommonX64\Microsoft Shared\VBA\VBA7.1\VBEUI.DLL

Which matches the results from the file manager search but NOT what's reported in the vbe or when using vba to loop through references.

I've also run Colin's excellent references viewer which confirms my findings regarding the references collection in vba.

OK so, the implication is that to derive the actual location of a library will require a) knowing the file name or GUID and b) using that in code to search the registry.

I'll address this later right now I need to figure out why some machines are hanging on Date() and others are not. I still suspect references so I'm going down that route after importing all objects into a new blank template db, decompiling and so on and so forth...

Three things
1. why are you referencing the VBEUI.dll library? I have never seen anyone use that. Perhaps there is a conflict with the built in VBE7.dll library?

There are multiple copies of library files provided as a backup in case of problems.
The registry keeps a store of these file locations in case of emergency

You should use the VBE7.dll file in C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1
. . . NOT backup copies in root folders such as C:\Program Files\Microsoft Office\root\vfs\ProgramFilesCommonX64\Microsoft Shared\VBA\VBA7.1

3. You are missing the standard library OLE Automation c:\Windows\System32.stdole2.tlb

These 4 references should be in all ACCDB databases as a minimum

You should use the default locations of each in all your databases

Also to save time analysing other users' computers, you could install my Access Version Checker add-in which includes a references check:

Access/Windows Version Check Add-In

Use this add-in to obtain the full Access/Windows/Office 365 version and bitness information. It also provides details of the VBA references used by the host application

www.isladogs.co.uk

 

[sonic8]

C:\Program Files\Microsoft Office\Root\VFS\ProgramFilesCommonX64\Microsoft Shared\VBA\VBA7.1\VBEUI.DLL

Which matches the results from the file manager search but NOT what's reported in the vbe or when using vba to loop through references.

Please note: I'm not qualified to properly explain App virtualization. - The terminology I use might not be entirely correct and I might miss relevant details.

The Office 365 C2R Apps are running in a partially virtualized ("Sandboxed") environment. When running these MS Office applications, the sub folders of the "VFS" folder are "merged" with the actual corresponding folders of the operating system for the running application (this happens virtually, the folders are not physically merged).
E.g. ...\VFS\ProgramFilesCommonX64\Microsoft Shared\VBA\VBA7.1\ is merged with C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1. with files from the VFS folder taking precedence in case there is one and the same file in both folders.
So, for an Office 365 application the folder ...\VFS\ProgramFilesCommonX64\Microsoft Shared\VBA\VBA7.1\ actually is C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1.

To verify this, ....

1. Create a file "Afile.txt" in your temp folder and then copy it to: ...\VFS\ProgramFilesCommonX64\Microsoft Shared\VBA\VBA7.1\ (Admin permission required)
2. Run an Office 365 application, e.g., Access, and open the VBA environment
3. Run: ? FileSystem.FileLen("C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\Afile.txt") in the Immediate Window
4. Observe: The output will show the size of the file in bytes. Even thought, the file does not actually exist in C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1

Bottom line: The libraries, which are in the VFS subfolder, are in their correct location!

I'll address this later right now I need to figure out why some machines are hanging on Date() and others are not. I still suspect references so I'm going down that route after importing all objects into a new blank template db, decompiling and so on and so forth...

Exactly that needs to be figured out and broken references are the most likely culprit. However, the libraries in the VFS folders are (most likely) the wrong tree to bark up.

 

[sonic8]

There are multiple copies of library files provided as a backup in case of problems.
The registry keeps a store of these file locations in case of emergency

You should use the VBE7.dll file in C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1
. . . NOT backup copies in root folders such as C:\Program Files\Microsoft Office\root\vfs\ProgramFilesCommonX64\Microsoft Shared\VBA\VBA7.1

@isladogs, the above statements are incorrect. The files in the VFS folder are not "provided as a backup", as explained in my previous post, which I started to write before seeing yours.

3. You are missing the standard library OLE Automation c:\Windows\System32.stdole2.tlb

IIRC the stdole2.tlb is optional. The library is added by default to new database projects, but it is not strictly mandatory.

 

[isladogs]

I stand corrected re: the vfs folder item.
I had missed the point of that because the Everything app doesn't list anything in the vfs folders (presumably for the reasons you explained)
This is what it lists for me

OLE Automation (stdol22.tlb) is one of the 4 libraries always installed by default on a new db.
Whilst it can be removed, it does contain some important functionality related to fonts and pictures

 

[NickCoe(UK)]

I stand corrected re: the vfs folder item.
I had missed the point of that because the Everything app doesn't list anything in the vfs folders (presumably for the reasons you explained)
This is what it lists for me

View attachment 117192

OLE Automation (stdol22.tlb) is one of the 4 libraries always installed by default on a new db.
Whilst it can be removed, it does contain some important functionality related to fonts and pictures

Agreed and I apologise to all and each for not being clear. Some of the screenshots came from my experimental/Dev machine which do not match the Production copy.
All User client m/c's have the same refs, 365 build, W11 build and so on. There MUST be something different since some work and some throw the error I initially mentioned.

I'm currently just gathering evidence from those that work and those that don't. Right now Access Options using some code from Dan Pineault on DevHut.

I did try your add in for refs and so on but it crashed due, I suspect, to our hardened security. I've made a note to address that and contact you with a report once the pressure of fixing this issue is off.

This is what refs are on both a working m/c and one that fails. They are the same. Which originally led me to think about lib registration et al.

Thanks.

 

[sonic8]

I had missed the point of that because the Everything app doesn't list anything in the vfs folders (presumably for the reasons you explained)

That's weird. From a file system point of view those folders are just completely normal folders.
On my end Everything shows the expected result:

 

[NickCoe(UK)]

That's weird. From a file system point of view those folders are just completely normal folders.
On my end Everything shows the expected result:
View attachment 117197

And my dev m/c has to be different running as admin user too...

 

[sonic8]

And my dev m/c has to be different running as admin user too...

No need to frown. The search results on your machine look perfectly fine to me.
I've got multiple versions of MS Office installed on my computer. Therefor there are more occurrences of the VBE7.dll in my screenshot.

 

[NickCoe(UK)]

No need to frown. The search results on your machine look perfectly fine to me.
I've got multiple versions of MS Office installed on my computer. Therefor there are more occurrences of the VBE7.dll in my screenshot.

Doh! Of course! I'm slow this morning