Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-13-2019, 05:37 PM   #1
ellenr
Newly Registered User
 
Join Date: Apr 2011
Posts: 310
Thanks: 46
Thanked 0 Times in 0 Posts
ellenr is on a distinguished road
deploying to Runtime users

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?

ellenr is offline   Reply With Quote
Old 02-13-2019, 05:40 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 732 Times in 715 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: deploying to Runtime users

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 02-13-2019, 05:44 PM   #3
ellenr
Newly Registered User
 
Join Date: Apr 2011
Posts: 310
Thanks: 46
Thanked 0 Times in 0 Posts
ellenr is on a distinguished road
Re: deploying to Runtime users

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?

ellenr is offline   Reply With Quote
Old 02-13-2019, 05:50 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 732 Times in 715 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: deploying to Runtime users

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 02-13-2019, 06:09 PM   #5
ellenr
Newly Registered User
 
Join Date: Apr 2011
Posts: 310
Thanks: 46
Thanked 0 Times in 0 Posts
ellenr is on a distinguished road
Re: deploying to Runtime users

Thank you--how do I do it in vba code?
ellenr is offline   Reply With Quote
Old 02-13-2019, 06:12 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,063
Thanks: 36
Thanked 732 Times in 715 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: deploying to Runtime users

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 02-13-2019 at 06:16 PM. Reason: added caution
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
ellenr (02-13-2019)
Old 02-19-2019, 02:19 PM   #7
Webbarr
Newly Registered User
 
Join Date: Mar 2018
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
Webbarr is on a distinguished road
Re: deploying to Runtime users

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

Webbarr is offline   Reply With Quote
Old 02-19-2019, 04:54 PM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,547
Thanks: 88
Thanked 1,473 Times in 1,390 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: deploying to Runtime users

Quote:
Originally Posted by Webbarr View Post
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".
Galaxiom is online now   Reply With Quote
Old 02-19-2019, 04:59 PM   #9
ellenr
Newly Registered User
 
Join Date: Apr 2011
Posts: 310
Thanks: 46
Thanked 0 Times in 0 Posts
ellenr is on a distinguished road
Re: deploying to Runtime users

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.
ellenr is offline   Reply With Quote
Old 02-19-2019, 05:53 PM   #10
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,275
Thanks: 11
Thanked 710 Times in 660 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: deploying to Runtime users

Quote:
Originally Posted by ellenr View Post
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)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 02-20-2019, 12:02 AM   #11
Webbarr
Newly Registered User
 
Join Date: Mar 2018
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
Webbarr is on a distinguished road
Re: deploying to Runtime users

Quote:
Originally Posted by Galaxiom View Post
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
Webbarr is offline   Reply With Quote
Old 02-20-2019, 12:56 AM   #12
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,448
Thanks: 106
Thanked 2,535 Times in 2,328 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: deploying to Runtime users

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Webbarr (02-20-2019)
Old 02-20-2019, 12:59 AM   #13
Webbarr
Newly Registered User
 
Join Date: Mar 2018
Posts: 6
Thanks: 3
Thanked 0 Times in 0 Posts
Webbarr is on a distinguished road
Re: deploying to Runtime users

Quote:
Originally Posted by isladogs View Post
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!
Webbarr is offline   Reply With Quote
Old 02-20-2019, 01:11 AM   #14
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,448
Thanks: 106
Thanked 2,535 Times in 2,328 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: deploying to Runtime users

You're welcome. I can supply the registry script for the installer if it helps.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deploying a Database without users seeing the Back end McObraz General 4 08-28-2013 10:06 PM
Deploying a Runtime Database scotthutchings Theory and practice of database design 0 06-29-2010 07:44 AM
Question Deploying Access 2007 DB in Runtime EchoMe General 4 06-04-2010 08:27 AM
Deploying DB using FE and BE with runtime version buratti General 5 03-20-2010 01:34 AM
Deploying Database to users PC by CD-ROM cocoonfx General 7 01-22-2007 06:34 AM




All times are GMT -8. The time now is 03:16 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World