Auto FrontEnd Utility (1 Viewer)

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
I have been trying to install the Bob Larsen’s AutoUpdater to my FE Database.
https://btabdevelopment.com/free-access-tools/

I would like the database users to store a copy of the database on their desktop, but seem to keep getting an error. When the database opens, I get an error saying “ could not find file\\Servername\folder redirection\Tom\Desktop\filename.accdb”. In the ‘select location where you will store your Front-End Master’ on the setup utility I have entered: %UserProfile%\Desktop
Is there something better I should add to this line? Any ideas what is wrong?:confused:
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,338
I've never used that FE Auto Updater as I have my own.
However I recommend only using the Windows desktop for shortcuts.
If you place large files in that location it will slow down loading Windows as each will need to be loaded whenever you logon.

Instead suggest you place the FE in a consistent location on each workstation with a desktop shortcut
 

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
I'm really confused now. I was told in a previous forum that I should not use shortcuts for the database as it could cause the database to become corrupt. At the moment I have a shortcut to the FE on everyone's desktop. I was told that I needed to have a copy of the FE on everyone's desktop.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
I'm really confused now. I was told in a previous forum that I should not use shortcuts for the database as it could cause the database to become corrupt. At the moment I have a shortcut to the FE on everyone's desktop. I was told that I needed to have a copy of the FE on everyone's desktop.
That was probably because the shortcut was to the *SAME* FE. Not recommended.
You need to copy the FE to each users computer and then place a shortcut on their desktop to the FE on *THEIR* computer.

I just used something simple like C:\DB for each user.

I used that utility in my last job.
Create a desktop shortcut yourself with the correct path to the FE on your computer. Copy that shortcut to a known location.
Have an initial install routine that copies the FE and shortcut one time, then just use the autoupdater from then on.

HTH
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,338
Agree with Gasman's interpretation of the advice you received previously.
Each user needs their own copy of the FE on their own computer hard drive but not on the desktop itself.
My comments about not using the desktop apply to any files (not just Access).
You will notice the difference in Windows loading speed if the desktop has shortcuts ONLY.

Shortcuts in themselves cannot cause corruption.
However, several users 'sharing' the same copy of the FE will definitely lead to corruption.

Make sure you place the FE in a designated folder and not the root C:\ drive itself.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
Here is some VBS I knocked up to do the initial install
Amend to suit if you use it.

Code:
Const DESKTOP = &H10&
'DIM strDBPath as string, strLnk as string, strFilePath as string
strDBPath = "C:\DB\"
strLnk = "\\srvph\jagph\DB\Employee DB.lnk"
strDB = "\\srvph\jagph\DB\Employee.accde"
Set objFSO      = CreateObject("Scripting.FileSystemObject")
Set objAppshell = CreateObject("Shell.Application")
Set objFolder = objAppShell.Namespace(DESKTOP)
strFilePath = objFolder.Self.Path & "\"
objFSO.CopyFile strlnk,strFilePath,True
MSGBOX "Shortcut copied to " & strFilePath,0,"Install Employee Shortcut"
IF NOT (objFSO.FolderExists(strDBPath)) then
	strPath = objFSO.CreateFolder("C:\DB")
End if
objFSO.CopyFile strDB,strDBpath,True
MSGBOX "Employee DB copied to " & strDBPath,0,"Install Employee DB"
set objFSO = Nothing
Set objAppShell = Nothing
Set objFolder = Nothing
HTH
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,705
Hi DT. Pardon me for jumping in but Bob's utility comes with a form to help you do the setup. Did you not use it? Just curious...
 

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
Thanks Gasman! Code scares me! I need to learn more about code before I start to use it I think. That is why I am trying to use the auto updater. Where is a good place to start with learning code like that? I will set up some C:\ folders today and see if that solves the problem. Thanks

Thanks Isladogs! That makes sense! I will try setting up C:\ folders for everyone and shortcuts and see how it goes. Thanks

Yes theDBguy. I did use the form.

You guys are an awesome help thank you.
 

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
I seem to be getting a little further. :D
I am just stuck on this part of the code though.
It is highlighting the LastInStr. I tried changing the file path, but it still errors and I can't find any info about LastInStr.

Code:
' Notice that I'm using a custom function, LastInStr(), to find
' the last instance of a character. Newer versions of Access expose
' the InstrRev() function to accomplish this, but Access 97 does not.
strPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, ""))
strPath = strPath & "\\IS-SBS001\Shared Folders\Resources\Databases\Survey central database and backups\Update.accdb"
' Enclose the file path in quotes to avoid problems with spaces
' in file and/or folder names.
strUpdateTool = "MSAccess.exe " & q & strPath & q
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
That would be a function included in the code?
I don't have a DB using the facility on this laptop. I'll need to boot up my old work laptop.
 

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
Does this help? This is all of the code:

Option Compare Database
Option Explicit

Private strVerClient As String
Private strVerServer As String
Private Sub Form_Load()
On Error Resume Next

' Populate module level variables when form loads.
strVerClient = Nz(DLookup("[VersionNumber]", "[tblVersionClient]"), "")
strVerServer = Nz(DLookup("[VersionNumber]", "[tblVersionServer]"), "")

Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strFilePath As String

' looks up the version of the front-end as listed in the backend
strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")

' looks up the version of the front-end on the front-end
strFE = DLookup("fe_version_number", "tbl-fe_version")

' looks up the location of the front-end master file
strMasterLocation = DLookup("s_masterlocation", "tbl-version_master_location")

' checks for the existence of an updating batch file and deletes it if it exists
strFilePath = CurrentProject.Path & "\UpdateDbFE.cmd"

If Dir(strFilePath) <> "" Then
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile (strFilePath)
Set fs = Nothing
End If


' if the current database opened is the master then it bypasses the check.
If CurrentProject.Path = strMasterLocation Then

Exit Sub

Else

' if the version numbers do not match and it is not the master that is opened,
' the database will do the update process
If strFE <> strFEMaster Then
MsgBox "Your program is not the latest version." & vbCrLf & _
"The front-end needs to be updated. The program will " & vbCrLf & _
"now close and then should reopen automatically.", vbCritical, "VERSION NEEDS UPDATING"

' sets the global variable for the path/name of the current database
g_strFilePath = CurrentProject.Path & "" & CurrentProject.Name

' sets the global variable for the path/name of the database to copy
g_strCopyLocation = strMasterLocation

' calls the UpdateFrontEnd module

UpdateFrontEnd

End If

End If

End Sub

Private Sub Form_Timer()
On Error Resume Next

Dim strMsg As String
Dim strPath As String
Dim strUpdateTool As String
Const q As String * 1 = """"

Me.TimerInterval = 0

' If versions match, then proceed with opening of main form.
If strVerClient = strVerServer Then
Me.Visible = False
DoCmd.OpenForm "Switchboard"

' ... if not, then offer the user the option to download latest.
Else
strMsg = "You do not have the correct version." & vbCrLf & vbCrLf & _
"Please download the latest version of Survey Central?"
If MsgBox(strMsg, vbExclamation + vbOKCancel, "Update") = vbOK Then

' Notice that I'm using a custom function, LastInStr(), to find
' the last instance of a character. Newer versions of Access expose
' the InstrRev() function to accomplish this, but Access 97 does not.
strPath = Left(CurrentDb.Name, LastInStr(CurrentDb.Name, ""))
strPath = strPath & "\\IS-SBS001\Shared Folders\Resources\Databases\Survey central database and backups\Update.accdb"
' Enclose the file path in quotes to avoid problems with spaces
' in file and/or folder names.
strUpdateTool = "MSAccess.exe " & q & strPath & q

' This is where the real work happens.
' Use SHELL command to open the UPDATE.ACCDB utility
' ... then quit this client so it may be overwritten.
Shell strUpdateTool, vbNormalFocus
DoCmd.Quit
End If
End If
End Sub
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
My version does not have a timer event, and as DBGuy asked, the utility comes with a form for file paths.?

This is the version I used I believe, as it was some time ago I installed it and once working was left alone. The only thing I changed was this
Code:
If strFE < strFEMaster Then ' Amended to less than rather than not equals
HTH
 

Attachments

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
It appeared as if it needed a timer to switch to the switchboard. Before I put the timer in it would just stay at the splash screen.

I think I will have to start from scratch again tomorrow. I might delete out the code and start with the utility again.

Thanks
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
It appeared as if it needed a timer to switch to the switchboard. Before I put the timer in it would just stay at the splash screen.

I think I will have to start from scratch again tomorrow. I might delete out the code and start with the utility again.

Thanks
I think that would be best. Regardless though, you should not have any hard coded paths in there, they should be in their respective tables.
 

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
Okay I have started from scratch. I have followed the instructions, but it is not updating. The splash form is no longer coming up on open either. The only thing I'm not doing from the instructions is changing to an MDB file. Would this cause the problems?

I'm assuming that the location where you will store your FE Master is referring to the folder in C: that the user's copy is kept.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
No, the FE master is referring to where the the FE master is stored.?
The utility would copy from that location to the users FE location, which it would get from the dB being opened.

Put a break point in the code and walk through it line by line.

I have to go out now for the rest of the morning.
Will see how you have got on when I get back.

You will only see the splash screen if the version has changed.?
 

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
Oh that makes sense now! It is asking for the folder, where as it is asking for the file in the first line of the form.

SO I have got a little further. I haven't been able to get it to update though. When it says in the instruction about adding it to the main start up form, I think that is where I might of messed up.

How do I have the frmSplash open first, run the script and then open the switchboard main menu?
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
I do not know what you are using, but there is no frmSplash in the utility.
You put the code in the first form that opens in the DB.? You can find that out from the Access Options/Current Database,
The code does not allow to display the form if the DB needs to be updated, so there is no switchboard to call.?

In my case it is a Form called Login.
I tweaked mine a bit so that I could easily copy to any other databases where I wanted it.
In the form load of the first form I call CheckFE, if all is well, my form loads and the users enters their details and clicks the Login button. That will then call the switchboard form if user details are correct.

If you just open to the switchboard then that is where you put your code.

My basFEUpdate module now looks like this
Code:
Option Compare Database
Option Explicit
' global variable for path to original database location
Public g_strFilePath As String
' global variable for path to database to copy from
Public g_strCopyLocation As String
Public Sub CheckFE()
' Used for updating FE
Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strFilePath As String
Dim strReqPath As String

' looks up the version of the front-end as listed in the backend
strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")

' looks up the version of the front-end on the front-end
strFE = DLookup("fe_version_number", "tbl-fe_version")

' looks up the location of the front-end master file
strMasterLocation = DLookup("s_masterlocation", "tbl-version_master_location")

' checks for the existence of an updating batch file and deletes it if it exists
    strFilePath = CurrentProject.Path & "\UpdateDbFE.cmd"
    
    If Dir(strFilePath) <> "" Then
        Dim fs As Object
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.DeleteFile (strFilePath)
        Set fs = Nothing
    End If
        

' if the current database opened is the master then it bypasses the check.
If CurrentProject.Path = strMasterLocation Then

    Exit Sub
    
Else

' if the version numbers do not match and it is not the master that is opened,
' the database will do the update process
    If strFE < strFEMaster Then ' Amended to less than rather than not equals
        MsgBox "Your program is not the latest version." & vbCrLf & _
        "The front-end needs to be updated.  The program will " & vbCrLf & _
        "now close and then should reopen automatically.", vbCritical, "VERSION NEEDS UPDATING"
        
        ' sets the global variable for the path/name of the current database
        g_strFilePath = CurrentProject.Path & "\" & CurrentProject.Name
        
        ' sets the global variable for the path/name of the database to copy
        g_strCopyLocation = strMasterLocation
        
        ' calls the UpdateFrontEnd module
        
        UpdateFrontEnd
        
    End If
    
End If
End Sub

Public Sub UpdateFrontEnd()
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim TestFile As String
Dim strKillFile As String
Dim strReplFile As String
Dim strRestart As String

' sets the file name and location for the file to delete
strKillFile = g_strFilePath
' sets the file name and location for the file to copy
strReplFile = g_strCopyLocation & "\" & CurrentProject.Name
' sets the file name of the batch file to create
TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
' sets the restart file name
strRestart = """" & strKillFile & """"
' creates the batch file
Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Deleting old file"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "Del """ & strKillFile & """"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
Close #1
'Exit Sub
' runs the batch file
Shell TestFile

'closes the current version and runs the batch file
DoCmd.Quit



End Sub
and all I do on any form that loads first in the DB is place

Code:
Call CheckFE
in the form's load event.

HTH
 

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
Okay. I thought it had to be loaded onto a Splash form. I was worried that if there are no updates available it wouldn't display the main switchboard menu. I will try adding it to the switchboard then. Thank you!
 

DatabaseTash

Registered User
Joined
Jul 23, 2018
Messages
95
Gasman you are a legend!!!!!!!!!!!

Thank you soooo much for your time and patience!!

It is working beautifully. I have tested it on a couple of PCs now.
:):) THANK YOU!:) :)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom