[X]My Assistant

This topic is designated for discussion of the article: AddTrustedLocation.

[josed]

Thanks for the code posted which applies to MS-Access.

I was able to use it for an Excel desktop application under development.
I verified the Registry and it was added with all arguments as coded.

The only changes I had to make to the code were:

a) Change the word 'Access' for 'Excel' in the strLnKey, as in
strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(Application.Version, "##,##0.0") & _
"\Excel\Security\Trusted Locations\Location"

b) Instead of [ strPath = CurrentProject.Path ], I used:
strPath = Application.Workbooks("my filename here").Path



The code (a function) was inserted in the ThisWorkbook object (module), General section.
But the function is called from the Workbook_Open event, and it's the first action taken:

Private Sub Workbook_Open()
'Comments...

On Error GoTo Err_Mgr

Call AddTrustedLocation
.
.
more code...

End Sub


Thanks again, this function will save me time and work since now I don't have
to include Trusted Location code on the installation file .inf... phew

Josed

[josed]

..cont'd

the desktop runs Windows 8.1, Excel 2010.


Thanks,
Josed

[brucedodds]

Hello,

This article contains two versions of AddTrustedLocation(). One is recommended for a Win8 64 Bit / Access 2013 64 Bit environment, the other for other Windows/Access combinations.

I don't see the difference between them, apart from the fact that the 64 bit version includes a prompt to ask the user if the application folder should be added as a trusted location.

What am I missing?

thanks,

Bruce

[ttoews]

Just an FYI you do not need to use 1, 2, 3 or any such numeric key in the registry key. Oh, Microsoft Access does but you can use anything you want. In the Auto FE Updater code I used "AutoFEUpdater-<name of config file>" as that was unique enough for my purposes. I just tested this on an A2013 runtime install and using some text worked fine. (Interestingly enough the ' in Tony's database file did not appear to work but that's not important for our discussion.)

So really the code that loops etc shouldn't even be used. There is some API registry editor code that will loop through all subkeys of a given registry key which should and not just using 1,2,3 ... 999

https://support.microsoft.com/en-us/kb/267908 probably has the code required but I never tested it myself.

(Excuse any typos or missing words as the retina is my good eye is dying.)

[BananaRepublic]

Tony makes good point. On the other hand, I can see the convenience of not having to think up a location name.

So to achieve both goals, I made some changes, using StdRegProv WMS class instead of WScript.Shell's built-in registry methods because StdRegProv can explicitly enumerate subkeys, thus totally eliminating problems associating with blindly looping for a number appended to "Location". As a bonus, we now can specify which hive we want to write, with default still being HKCU as this is generally the best place to create a trusted location. But if you wanted to do a machine-wide installation and you can start Access as administrator, then you have the option of using HKLM hive instead.

Before we make the change, I'd like to get others to test this -- it works for me but would have to make sure this works well on different hardware configuration. I believe this should work both on 32-bit and 64-bit.

The only point I'm not 100% sure about is whether we still want to enumerate even if we specify a Trusted Location name. The code for checking that database already isn't trusted makes sense as we probably don't want to create trusted locations that overlaps needlessly but then again, overlapping trust locations won't hurt anything.

CODE

Public Enum RegistryHives
    HKEY_CLASSES_ROOT = &H80000000 '(2147483648 (0x80000000))
    HKEY_CURRENT_USER = &H80000001 '(2147483649 (0x80000001))
    HKEY_LOCAL_MACHINE = &H80000002 '(2147483650 (0x80000002))
    HKEY_USERS = &H80000003 '(2147483651 (0x80000003))
    HKEY_CURRENT_CONFIG = &H80000004 '(2147483653 (0x80000005))
End Enum

Public Function AddTrustedLocation( _
    Optional Hive As RegistryHives = HKEY_CURRENT_USER, _
    Optional TrustedLocationKeyName As String = vbNullString _
) As Boolean
On Error GoTo err_proc
'WARNING:  THIS CODE MODIFIES THE REGISTRY
'sets registry key for 'trusted location'
    
    Dim i As Long
    Dim Reg As Object
    
    Dim varSubKeys As Variant
    Dim varSubKey As Variant
    Dim varValue As Variant
    
    Dim lngLastValue As Long
    Dim lngTest As Long
    
    Dim intLocns As Integer
    Dim intNotUsed As Integer
    Dim strLnKey As String
    Dim strPath As String
    Dim strTitle As String
    
    strLnKey = "Software\Microsoft\Office\" & Format(Application.Version, "##,##0.0") & _
               "\Access\Security\Trusted Locations\"
    strPath = CurrentProject.Path & "\"
    strTitle = "Add Trusted "
    
    Set Reg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    Reg.EnumKey Hive, strLnKey, varSubKeys
    
    If Not IsNull(varSubKeys) Then
        For Each varSubKey In varSubKeys
            
            'Determine if the database is already trusted at this path
            If Reg.GetStringValue(Hive, strLnKey & varSubKey, "Path", varValue) = 0 Then
                If InStr(1, varValue, strPath) = 1 Then
                    'The database is already trusted, no need to add a new key
                    Exit Function
                End If
            End If
            
            'If we didn't get a specific location name, try to find an unused location name
            If Len(TrustedLocationKeyName) = 0 Then
                If varValue Like "Location*" Then
                    If IsNumeric(Mid$(varValue, 9)) Then
                        lngTest = Mid$(varValue, 9)
                        If lngLastValue < lngTest Then
                            lngLastValue = lngTest
                        End If
                    End If
                End If
            End If
        Next
    End If
    
    If Len(TrustedLocationKeyName) Then
        strLnKey = strLnKey & TrustedLocationKeyName
    Else
        strLnKey = strLnKey & "Location" & (lngLastValue + 1)
    End If
    
    If Reg.CreateKey(Hive, strLnKey) = 0 Then
        Reg.SetDWORDValue Hive, strLnKey, "AllowSubfolders", 1
        Reg.SetStringValue Hive, strLnKey, "Date", CStr(Now())
        Reg.SetStringValue Hive, strLnKey, "Description", Application.CurrentProject.Name
        Reg.SetStringValue Hive, strLnKey, "Path", strPath

        AddTrustedLocation = True
    Else
        AddTrustedLocation = False
    End If
    
exit_proc:
    Set Reg = Nothing
    Exit Function
  
err_proc:
    AddTrustedLocation = False
    MsgBox Err.Description, , strTitle
    Resume exit_proc
    Resume 'for debugging
End Function

[fizzy1]

Hi BR,

I'm having a crack at running this, but I can't get it to compile. I copied the code verbatim, and didn't change anything. I'm running it on Access2010 on Windows 7 Pro (and I have Admin rights).

I generally use Option Explicit in the header for my modules, and having that throws an error on HKEY_CURRENT_USER (in your opening declaration) as "Variable not defined".

If I remove Option Explicit, then compiling it fails with the entire opening declaration highlighted with the message "User-defined type not defined".

Any thoughts?

Thanks,
Toby.

[fizzy1]

Aaaand stop. I think I found the problem. I had another Function inside the module, so pasting your code in after that caused the following block to not be in the module's header.

CODE

Public Enum RegistryHives
    HKEY_CLASSES_ROOT = &H80000000 '(2147483648 (0x80000000))
    HKEY_CURRENT_USER = &H80000001 '(2147483649 (0x80000001))
    HKEY_LOCAL_MACHINE = &H80000002 '(2147483650 (0x80000002))
    HKEY_USERS = &H80000003 '(2147483651 (0x80000003))
    HKEY_CURRENT_CONFIG = &H80000004 '(2147483653 (0x80000005))
End Enum

Moving it to the header got rid of the problem. it seems to run fine and create a registry key where you'd expect it to be.

Awesome!

[jbrian31]

I am confused about the implementation of this. Do I paste the code into a module or class module, where do I paste the code essentially? Then what would be the code to run that function in the autoexe macro?