Re: [MS_AccessPros] What is the best way to update front end automatically when a new version is available?

 

Everyone - You might like my VBScript that does the same thing as the batch file mentioned by Doyce. I've uploaded the instructions and sample VBScript file to our Files>Articles_Excerpts folder.

The article was scraped from my dormant website that I hope to bring back up some day. Obviously, any link in the article that  points to somewhere on my site will fail but I can give you whatever you need if you let me know.

-Bill

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@hotmail.com> wrote :

Thanks Doyce. Bill's solution is much more elegant than my brute force push a new copy. 

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@con-way.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, February 7, 2019 12:26 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] What is the best way to update front end automatically when a new version is available?

 

Duane and Kevin,

Bill Mosca created a great way to do this using a batch file. I believe the example and instructions are included on this group site but I haven't found it yet. However, I use it all the time and it works great for my users. They just click a desktop shortcut and it runs the batch file and if there is a new front end on the server, it is downloaded and then opens. Otherwise it just opens. Here is an example of one of my batch files.

::::This batch file checks for latest version of Inspection.accdb
::It will copy the latest version if there is one, else it opens the existing version.
::
::The batch file checks for the existance of a blank text file.
::The name of the text file reflects the date of the latestversion on the server. The only maintenance for the batch file
::is updating the date in the name of the text file in the line
::   set T=Inspection_20190129.txt
::
::If the text file name does not match or does not exist
::the proper folder is created if needed. If it is OK
::the batch file skips to the line that opens the MDE.
::
::Otherwise, the next step is to copy the shortcut that actually opens the
::MDE. An extra set is added to check if Access 2007 is installed
::because the path to Access is different in that case so a different
::shortcut is necessary.
::
::Then the latest text file is copied, the MDE is copied from the server.
::Last step is to trigger the shortcut that opens the FE.
::
::Note - Lines starting with : are labels for goto. Lines starting with rem
::are remarked out.

::***Begin Code***
@ECHO OFF
set T=Inspection_20190129.txt
if not exist C:\Databases\%T% goto updatedb:
goto startdb

:updatedb

md "C:\Databases" >nul
rem - get shortcut. /Y switch forces overwrite.
copy /Y "\\rsiprfp001\rsicommon\Access Databases\Front End Databases\StartInspection.lnk" "C:\Databases" >nul
rem - Overwrite StartMyDatabase with the Access 2007 shortcut if user has Office 2007 installed.
if exist "C:\Program Files\Microsoft Office\OFFICE12\msaccess.exe" copy /Y "\\rsiprfp001\rsicommon\Access Databases\Front End Databases\StartInspection.lnk" "C:\Databases" >nul
goto getdb

:getdb
rem - delete old files and get copy of latest ones
del "C:\Databases\Inspection_*.txt" >nul
echo Installing latest version of MyDatabase
echo Please wait while new MyDatabase loads.
echo This may take 2 or 3 minutes depending on network traffic...
echo DO NOT CLOSE THIS WINDOW!

copy "\\rsiprfp001\rsicommon\Access Databases\Front End Databases\%T%" "C:\Databases" >nul

copy /Y "\\rsiprfp001\rsicommon\Access Databases\Front End Databases\Inspection.accde" "C:\Databases" >nul
goto startdb:

:startdb
"C:\Databases\startInspection.lnk"

:shutdown
@CLS
@EXIT

The file references an empty file on server as well that checks the latest version. When I update an FE, I rename the empty file with the current date, update the filename in the batch file and copy the updated FE to the server. Hope this helps.

Doyce 

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I think if you change just some of the final lines to:

   'Set xlApp = CreateObject("Excel.Application")

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

   wshshell.Run strAppFolder & "\" & strAppFile

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of qingqinga qingqinga@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, February 7, 2019 10:15 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] What is the best way to update front end automatically when a new version is available?

 

Dear Duane,

Thank you so much. I'm going to try it tomorrow.

Best Regards,

Kevin

发自 WPS邮箱客戶端

在 "Duane Hookom duanehookom@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2019年2月7日 22:19写道：

 

This is the script file that is saved with a vbs extension. This is the only copy I could find and it is used for an Excel file. I don't have the time now to change the lines near the bottom to make it Access.

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

' 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 = "YourAppTitleHere"

   strAppFile = "YourAppFileNameHere"

   strAppNetworkFile = "YourFullAppNameHereWithNetworkPathIncluded"

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

   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)

   'wshshell.Run strAppFolder & "\" & strAppFile

wscript.Quit

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Kevin qingqinga@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, February 6, 2019 11:23 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] What is the best way to update front end automatically when a new version is available?

 

Dear Duane,

Would you please give an example of the batch file? So I can make some changes to suit my case? Thanks a lot.

Best Regards,

Kevin

On 07-Feb-2019 12:11 PM, Duane Hookom duanehookom@... [MS_Access_Professionals] wrote:

�

Hi Kevin,

If you search the web you will find several free utilities. I generally create a script or batch file that

- creates a directory on the user�s hard drive

- copies the front end to the directory

- sets the directory to trusted

- opens the file

Duane

Get Outlook for iOS

�

From: ms_access_professionals@yahoogroups.com on behalf of Kevin qingqinga@... [MS_Access_Professionals] <ms_access_professionals@yahoogroups.com>
Sent: Wednesday, February 6, 2019 10:00 PM
To: ms_access_professionals@yahoogroups.com
Subject: [MS_AccessPros] What is the best way to update front end automatically when a new version is available?

�

Dear All,

What is the best way to update front end automatically when a new
version is available? Thanks in advance.

Best Regards,

Kevin



------------------------------------
Posted by: Kevin <qingqinga@...>
------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

__._,_.___

---

Posted by: wrmosca@comcast.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (9)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___