deploying to Runtime users (1 Viewer)

ellenr

Registered User.
Local time
Today, 01:54
Joined
Apr 15, 2011
Messages
397
I am running 365 on Win10. I have a db that I deploy to users running runtime. If I move to a different location on my own computer, vba won't run unless I set a trusted location for it (which I can do), or I click File and Enable Content (VBA Macros). Is there no way to do this programmatically? Or, conversely, is there a way for a Runtime user with no Office installations to set trusted locations?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:54
Joined
Oct 29, 2018
Messages
21,358
Hi. Trusted Locations are stored in the Registry. You can use any scripting language to automatically add a Trusted Location. However, be very cautious at how you modify the Registry. If you miss anything, it could break the entire Operating System.
 

ellenr

Registered User.
Local time
Today, 01:54
Joined
Apr 15, 2011
Messages
397
Registry modification isn't a solution for non-computer-literate users in a distant city. Just wishing for a solution that makes deployment more seamless. Is there a safe way to write a script to do this that would work for all users?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:54
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you saying non-computer literate users in a distant city don’t know how to double-click on a script file? There are a lot of script examples on how to modify the registry where the user doesn’t even have to do anything. Besides, I would actually recommend only the computer admin should modify the registry (even if using a script file). You can certainly use VBA to do it, but users will have to trust the file at least one time for the code to run and modify the registry.
 

ellenr

Registered User.
Local time
Today, 01:54
Joined
Apr 15, 2011
Messages
397
Thank you--how do I do it in vba code?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:54
Joined
Oct 29, 2018
Messages
21,358
Hi. You could check out this demo from UtterAccess to get an idea on how to do it.


PS. Please note the demo is a little old, so depending on your Access version, make sure you're using the correct registry hive that's applicable to your version.
 
Last edited:

Webbarr

New member
Local time
Today, 05:54
Joined
Mar 9, 2018
Messages
6
Hey, this is an old code I use for a user to set as a trusted location at run time.

It's been a very long time since I found it, I didn't write it. I have no idea where I got it from. But it's worked for a very long time!

Code:
Option Compare Database
Option Explicit

Public Sub AddTrustedLocation()
On Error GoTo err_proc
'WARNING:  THIS CODE MODIFIES THE REGISTRY
'sets registry key for 'trusted location'

  Dim intLocns As Integer
  Dim i As Integer
  Dim intNotUsed As Integer
  Dim strLnKey As String
  Dim reg As Object
  Dim strPath As String
  Dim strTitle As String
  
  strTitle = "Add Trusted Location"
  Set reg = CreateObject("wscript.shell")
  strPath = CurrentProject.Path

  'Specify the registry trusted locations path for the version of Access used
  strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(Application.Version, "##,##0.0") & _
             "\Access\Security\Trusted Locations\Location"

On Error GoTo err_proc0
  'find top of range of trusted locations references in registry
  For i = 999 To 0 Step -1
      reg.RegRead strLnKey & i & "\Path"
      GoTo chckRegPths        'Reg.RegRead successful, location exists > check for path in all locations 0 - i.
checknext:
  Next
  MsgBox "Unexpected Error - No Registry Locations found", vbExclamation
  GoTo exit_proc
  
  
chckRegPths:
'Check if Currentdb path already a trusted location
'reg.RegRead fails before intlocns = i then the registry location is unused and
'will be used for new trusted location if path not already in registy

On Error GoTo err_proc1:
  For intLocns = 1 To i
      reg.RegRead strLnKey & intLocns & "\Path"
      'If Path already in registry -> exit
      If InStr(1, reg.RegRead(strLnKey & intLocns & "\Path"), strPath) = 1 Then GoTo exit_proc
NextLocn:
  Next
  
  If intLocns = 999 Then
      MsgBox "Location count exceeded - unable to write trusted location to registry", vbInformation, strTitle
      GoTo exit_proc
  End If
  'if no unused location found then set new location for path
  If intNotUsed = 0 Then intNotUsed = i + 1
  
'Write Trusted Location regstry key to unused location in registry
On Error GoTo err_proc:
  strLnKey = strLnKey & intNotUsed & "\"
  reg.RegWrite strLnKey & "AllowSubfolders", 1, "REG_DWORD"
  reg.RegWrite strLnKey & "Date", Now(), "REG_SZ"
  reg.RegWrite strLnKey & "Description", Application.CurrentProject.Name, "REG_SZ"
  reg.RegWrite strLnKey & "Path", strPath & "\", "REG_SZ"
  
'Notify user
    MsgBox "Done!", vbInformation, "AddTrustedLocation"
  
exit_proc:
  Set reg = Nothing
  Exit Sub
  
err_proc0:
  Resume checknext
  
err_proc1:
  If intNotUsed = 0 Then intNotUsed = intLocns
  Resume NextLocn

err_proc:
  MsgBox Err.Description, , strTitle
  Resume exit_proc
  
End Sub
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 16:54
Joined
Jan 20, 2009
Messages
12,849
Hey, this is an old code I use for a user to set as a trusted location at run time.

Of course this code must be run from a Trusted Location so it can become a "chicken and egg situation".
 

ellenr

Registered User.
Local time
Today, 01:54
Joined
Apr 15, 2011
Messages
397
I don't feel comfortable making changes in another's registry. They will just have to live with the warning! Thank you for the suggestions. I will hang onto the code for a braver day.
 

missinglinq

AWF VIP
Local time
Today, 01:54
Joined
Jun 20, 2003
Messages
6,423
Registry modification isn't a solution for non-computer-literate users in a distant city.
Aside from your other problem...this statement is kind of troublesome. Is it being used over a WAN?

Linq ;0)>
 

Webbarr

New member
Local time
Today, 05:54
Joined
Mar 9, 2018
Messages
6
Of course this code must be run from a Trusted Location so it can become a "chicken and egg situation".

The Access application in question resides in %localappdata%\application, whenever a new user starts & we perform the set-up they just click the "Add Trust for Application" button that's available and it runs.

I've never had an issue with it, maybe there are no issues with running this from the %localappdata% directory, I'm not sure. But short of that, I don't have to do anything else to get this to work
 

isladogs

MVP / VIP
Local time
Today, 05:54
Joined
Jan 14, 2017
Messages
18,186
The chicken and egg situation is that you can't run code like this unless the location or application is trusted. Once anyone clicks the button in the yellow 'security bar', the code becomes superfluous as its already trusted.

To get around this, when I distribute Access apps, I use installer software to create an EXE file. This includes script to add the location as trusted so its done before the Access file is opened. This means the security bar is never seen.

Of course, the information supplied with the file makes it clear to the end user that this is being done.
 

Webbarr

New member
Local time
Today, 05:54
Joined
Mar 9, 2018
Messages
6
The chicken and egg situation is that you can't run code like this unless the location or application is trusted. Once anyone clicks the button in the yellow 'security bar', the code becomes superfluous as its already trusted.

To get around this, when I distribute Access apps, I use installer software to create an EXE file. This includes script to add the location as trusted so its done before the Access file is opened. This means the security bar is never seen.

Of course, the information supplied with the file makes it clear to the end user that this is being done.

Ahh I get you, yes the user gets the yellow bar & has to add trust manually the first time. Sorry I understand now!

I'm working on a new installer for it at the moment actually, that sounds like a good idea. I think I'll include something that adds the trust during that stage & remove the ability inside the Access application.

Thanks for explaining it to me!
 

isladogs

MVP / VIP
Local time
Today, 05:54
Joined
Jan 14, 2017
Messages
18,186
You're welcome. I can supply the registry script for the installer if it helps.
 

Users who are viewing this thread

Top Bottom