Re: [MSAccessProfessionals] Access Runtime Issues: Warnings and Macros

I actually have scripts for both Access and Excel for different purposes. They are patched together from several sources on the web. Apparently I found the Excel version. 

Duane

Sent from my mobile

On Oct 13, 2020, at 6:47 PM, Arnelito Puzon <agpuzon@gmail.com> wrote:



Did you write the script or just frankenstein it from else where. You have an Excel instance there.

On Wed, Oct 14, 2020, 4:02 AM Doyce Winberry <doyce.winberry@xpo.com> wrote:

Thanks Duane! I'll give it a try.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPOLogistics

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Tuesday, October 13, 2020 2:55 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Access Runtime Issues: Warnings and Macros

 

[Warning: External sender, "Report Phish" for suspicious emails]

 

This is the script I have been using however the specific registry key depends on the version of MS Office. The script also downloads a copy of the front-end file and opens it.

 

'===================

' File: TrustCopyRun.vbs

'===================

   Const SystemFolder= 1

   Dim fso  'to be used for file related code

   Dim SysFolder

   Dim SysFolderPath

   dim strUserLogin

   Dim RegEdPath

   Dim strAppFolder

   Dim strAppName

   Dim strAppFile

   Dim strAppNetworkFile

'==============================================================

'  edit the values in the next three lines to match your application and network location

   strAppName = "MyAppName"

   strAppFile = "YourFrontEnd.accdb"

   strAppNetworkFile = "\\ServerName\shares\foldername\YourFrontEnd.accdb"

'==============================================================

   Set fso = wscript.CreateObject("Scripting.FileSystemObject")

   Set SysFolder =fso.GetSpecialFolder(SystemFolder)

   SysFolderPath= SysFolder.Path

 

   'initialize the script shell object

   Set WshShell = WScript.CreateObject("WScript.Shell")

   strUserLogin = WshShell.ExpandEnvironmentStrings("%USERNAME%")

   strAppFolder = "C:\users\" & strUserLogin & "\" & strAppName

   if not fso.FolderExists(strAppFolder) Then

      fso.CreateFolder strAppFolder

   End If

 

   'Office 12

   RegEdPath = "HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\"

   WshShell.RegWrite  RegEdPath  ,strAppName

   'Write the values into the registry

   WshShell.RegWrite  RegEdPath & strAppName & "\Path" , "C:\Users\" & strUserLogin & "\" & strAppName & "\"

   WshShell.RegWrite  RegEdPath & strAppName & "\AllowSubfolders" , 1, "REG_DWORD"

 

   'Office 14

   RegEdPath = "HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security\Trusted Locations\"

   WshShell.RegWrite  RegEdPath  ,strAppName

   'Write the values into the registry

   WshShell.RegWrite  RegEdPath & strAppName & "\Path" , "C:\Users\" & strUserLogin & "\" & strAppName & "\"

   WshShell.RegWrite  RegEdPath & strAppName & "\AllowSubfolders" , 1, "REG_DWORD"

 

   'copy the application file from the network location to the local folder

   fso.CopyFile strAppNetworkFile ,strAppFolder & "\", 1

   'run the application

   Set xlApp = CreateObject("Excel.Application")

   set xlBook = xlApp.WorkBooks.Open(strAppFolder & "\" & strAppFile)

 

 

 

wscript.Quit

 

 

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Doyce Winberry <doyce.winberry@xpo.com>
Sent: Tuesday, October 13, 2020 2:44 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Access Runtime Issues: Warnings and Macros

 

Duane,

 

The warnings thing makes sense. I'll try that. Would you happen to have a sample vbscript to set trusted folders. I did some registry edits to set trusted folders. I'm not sure if its that or the macro settings that are generating the security warning. I'd like to enable macros automatically and not get the warning.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPOLogistics

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Tuesday, October 13, 2020 2:40 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Access Runtime Issues: Warnings and Macros

 

[Warning: External sender, "Report Phish" for suspicious emails]

 

Hi Doyce,

I typically set warnings off and on immediately before and after running action queries. You can also use the Execute method.

 

You can run a vbscript to set/change your trusted folders.

 

Duane

 

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Doyce Winberry <doyce.winberry@xpo.com>
Sent: Tuesday, October 13, 2020 2:29 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Access Runtime Issues: Warnings and Macros

 

Hello friends, 
I'm using Office 2016 and my company is cutting back on Office E3 licenses so I'm starting to work with the latest Access Runtime testing my accde files. So far, my first issue is the security warning "A potential security concern has been identified" message when the accde file is open. I click open but would really like for it to go away. In full Access, I setup trusted locations and enable macros. Also, I turn off warnings for action queries. For the runtime version I put the following code in the load event of the switchboard, docmd.setwarnings false. However, I'm still getting messages like I'm about to delete 55 records out of a temp table. What do I need to do to make that go away also?
Doyce 

 

[Do not open attachments or click links unless you can verify the sender. Click "Report Phishing" if the email looks suspicious.]