Open DB via batch file (1 Viewer)

Gismo

Registered User.
Local time
Today, 17:26
Joined
Jun 12, 2017
Messages
1,298
Hi all,

I have a DB which the users open on the remote desktop via a batch file
I my startup form I have the code to hide the toolbar and the Navigation Pane

So we need to enable macros first before the pane is hidden
everyone then has access to the table because it has not enable the hide function yet

DoCmd.ShowToolbar "Ribbon", acToolbarNo
DoCmd.RunCommand acCmdWindowHide 'Hide Navidation Pane

Please could you advise
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:26
Joined
Feb 19, 2013
Messages
16,553
you need to trust the location of the db to enable macro's
if this is a 'first time install' you can do it by modifying the registry rather than doing it through access - assuming the user has the appropriate rights to do so.

Not enough information provided to say how you might do this, it may be the batch file can do it or you need to use an installer app. And depends on the setup - remote desktop connecting to terminal server? citrix? something else? Using .accdb? ,.accde? .accdr? user has runtime or full access on the remote server? each user has their own profile, etc etc
 

Ranman256

Well-known member
Local time
Today, 11:26
Joined
Apr 9, 2015
Messages
4,339
you must add trusted location to EVERY pc. Its not stored in the database, it's the setting of the pc Access app.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
42,970
Also, when using RD or Citrix, you need to put the db in the user's personal folder. Make sure they are NOT all opening the same copy of the FE.

If you need the .bat file, I will post it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:26
Joined
Oct 29, 2018
Messages
21,358
Hi all,

I have a DB which the users open on the remote desktop via a batch file
I my startup form I have the code to hide the toolbar and the Navigation Pane

So we need to enable macros first before the pane is hidden
everyone then has access to the table because it has not enable the hide function yet



Please could you advise
Have you tried hiding the Nav Pane using the Options settings?
 

Gismo

Registered User.
Local time
Today, 17:26
Joined
Jun 12, 2017
Messages
1,298
Also, when using RD or Citrix, you need to put the db in the user's personal folder. Make sure they are NOT all opening the same copy of the FE.

If you need the .bat file, I will post it.
I have a very basic batch file
I would appreciate to see what your batch file looks like please

@ECHO OFF
md %USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP
del %USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP\DawSheet.accdb
copy "\\sjo2054\CAMO\DAW Sheet\Engineers\Daw Sheet.accdb" %USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP\DawSheet.accdb
start %USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP\DawSheet.accdb
 
Last edited:

Gismo

Registered User.
Local time
Today, 17:26
Joined
Jun 12, 2017
Messages
1,298
Have you tried hiding the Nav Pane using the Options settings?
I used to do it via settings but then reverted to VBA
I actually can not remember what is the impact of the setting when the user uses Runtime
I do know that you do not have any setting option when using runtime only

Using VBA just made more sense but now I have the enable macro first issue so anyone can access the table when the DB opns
 

Gismo

Registered User.
Local time
Today, 17:26
Joined
Jun 12, 2017
Messages
1,298
I found this on the web

Not sure where to add my specific location though as per the first part of my batch file

I do still get a security notice popup as per below screenshot

@ECHO OFF

md %USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP
del %USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP\DawSheet.accdb
copy "\\sjo2054\CAMO\DAW Sheet\Engineers\Daw Sheet.accdb" %USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP\DawSheet.accdb

set dbpath=%USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP\
set Description=Daw Sheet
set TrustedKey=HKEY_CURRENT_USER\Software\Microsoft\Office16\Access\Security\Trusted Locations

start %USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP\DawSheet.accdb

1644475392760.png
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:26
Joined
Oct 29, 2018
Messages
21,358
I found this on the web

Not sure where to add my specific location though as per the first part of my batch file

I do still get a security notice popup as per below screenshot



View attachment 98168
I've just learned to live with that screenshot as a part of life. I've trained my users to just click Open. Now, they don't even think about it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
42,970
MS has recently added extra security. If the folder is on your server, try trusting that folder and the folders below it.
 

Gismo

Registered User.
Local time
Today, 17:26
Joined
Jun 12, 2017
Messages
1,298
MS has recently added extra security. If the folder is on your server, try trusting that folder and the folders below it.
I am not sure how to add the trusted location to the batch file though

%USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP

I tried set path
set dbpath=%USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP\

But not sure if that is the correct method to se a trusted location
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
42,970
You have to open Access on the PC where the app will run and go to the OPtions. You can set the trusted locations there. You have to check the box to allow trusted locations on the server and the other to trust the subfolders also.

The other option is to add keys to the registry. I can't look for them now but if you search you will find the keys you need. you will have to change the paths.
 

Gismo

Registered User.
Local time
Today, 17:26
Joined
Jun 12, 2017
Messages
1,298
You have to open Access on the PC where the app will run and go to the OPtions. You can set the trusted locations there. You have to check the box to allow trusted locations on the server and the other to trust the subfolders also.

The other option is to add keys to the registry. I can't look for them now but if you search you will find the keys you need. you will have to change the paths.
You do not have these options access runtime

I need to do this via a batch file but not sure on how to se a trusted location
We have 15 PC's in the hanger locally and a few remotely, I can not set them all manually
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:26
Joined
Sep 21, 2011
Messages
14,044
Think about it.?
If you could do it via a batch process, what is to stop some person with nefarious intentions, not doing the same thing? -(

Have you never heard of remote access?
 

Gismo

Registered User.
Local time
Today, 17:26
Joined
Jun 12, 2017
Messages
1,298
Think about it.?
If you could do it via a batch process, what is to stop some person with nefarious intentions, not doing the same thing? -(

Have you never heard of remote access?
As mentioned, the remote computers run access runtime, it does not allow for any option settings
It has to be code either via a batch file or VBA
The batch file copies a new front end every time the run the batch file so i need to test for trusted location every time the open the DB
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:26
Joined
Sep 21, 2011
Messages
14,044
Well I'd be setting a trusted location and putting the DB into that location each time.?
When I had to supply updated FE, that was C:\DB

However everyone had a full copy of Access, as it was only a few users.
 

Gismo

Registered User.
Local time
Today, 17:26
Joined
Jun 12, 2017
Messages
1,298
Well I'd be setting a trusted location and putting the DB into that location each time.?
When I had to supply updated FE, that was C:\DB

However everyone had a full copy of Access, as it was only a few users.
That is what I am trying to do
I want the below location to be a trusted location, the same location I copy the DB too

%USERPROFILE%\AppData\Local\Temp\Eng_DAW_Sheet\EngDAWSHEETTEMP
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:26
Joined
Sep 21, 2011
Messages
14,044
Better wait for Pat to post the registry code then, if you do not want to google for it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
42,970
You need to set this one to allow a network location:
Code:
[HKEY_CURRENT_USER\Software\Microsoft\Office\17.0\Access\Security\Trusted Locations]
"AllowNetworkLocations"=dword:00000001

Here is what a local path looks like: for C:\Data
Code:
[HKEY_CURRENT_USER\Software\Microsoft\Office\17.0\Access\Security\Trusted Locations\Location0]
"Path"="C:\\Data\\"
"AllowSubfolders"=dword:00000001
"Description"=""
"Date"="5/30/2011 3:06 AM"

Here is what a UNC path looks like: for \\Pat-pc\pat-pc\data
Code:
[HKEY_CURRENT_USER\Software\Microsoft\Office\17.0\Access\Security\Trusted Locations\Location1]
"Path"="\\\\Pat-pc\\pat-pc\\Data\\"
"AllowSubfolders"=dword:00000001
"Description"=""
"Date"="06/28/11 2:06 PM"

This one you should have. Access installs it when it is installed:
Code:
[HKEY_CURRENT_USER\Software\Microsoft\Office\17.0\Access\Security\Trusted Locations\Location2]
"Path"="C:\\Program Files (x86)\\Microsoft Office\\Office17\\ACCWIZ\\"
"Description"="Access default location: Wizard Databases"

For the individual locations, you need to supply a unique name. Above you see Location0, Location1, Location2. Doesn't matter what the name is but it must be unique.

Type RegEdit in the search box for windows. It will open the registry. Drill down using the path structure above. 17.0 is my version of office. Yours might be different so change it as necesary. If you have multiple versions in the list, add the keys to the newest version.

To prevent having to constantly add new folders to trust, create a folder pretty high up in the hierarchy and trust it as well as its subfolders and always put your access databases somewhere in that hierarchy. So you need a minimum of three keys
1. to trust folders on the network
2. to trust a high level folder on your C:\ drive
3. to trust a high level folder on your server drive - you could use a mapped drive letter but I prefer to use UNC name for this.

If you name the text file with .rga as the extension, Windows will know it is a registry update and will run the update if you double click it. Sooooooooo be careful to not double click if you want to open it but not run it. Right click and choose Edit.

Your Network Administrator can (and should) do this for you. He can distribute to all the user computers. You just have to give him the keys you need to add.
 
Last edited:

JonXL

Active member
Local time
Today, 10:26
Joined
Jul 9, 2021
Messages
153
I'm going to post a different approach...

Instead of requiring VBA to hide your stuff, set it up to require VBA to continue into the DB. What you do is create a modal popup form that says you must enable scripts/content or whatever and set that as the form to load on startup in the settings. Also in settings, you turn off the navigation pane.

In the form's load event, you put code to close it and open the form you really want users to interact with.

When folks load the app, they get that form and nothing else until they allow scripts. Once scripts are allowed, the form goes away and they get the form they need. If they previously marked the file as trusted then scripts run as soon as it's opened meaning that form flashes quickly (usually too fast to even be noticed) and the proper form shows right away.

Alternatively, most of what I've released is in ACCDE format which cannot be open at all until the scripts are enabled, so this is rarely a problem.

And I just realized that the ACCDE approach is basically the same as the form approach but more solid and with Access doing the hard work for you.
 

Users who are viewing this thread

Top Bottom