I split my db last week and put both front end and back end files in a shared network folder. I backup the db every day to a different folder on the network server and also to my laptop. I usually backup right after a form update so that the latest version of the form is available to users who want to update their local copy of the front end. I've been sending email alerts to the users.
My question is this: What is the best way to (1) make sure my backup updates the front end on the network server and (2) how can I automatically alert users to update to the latest version of the front end?
There are several ways you can do this. Here is a post I started with a Front End Auto Updating Utility. There are some good posts later on, including OldSoftBoss' enhancement of my tool. I think there are some other suggestions also in that same thread.
At a now defunct energy company, with each front-end change we updated a front-end version number in the back-end so that when a user's program accessed the back-end it checked the front-end version number, if they didn't match the user downloaded a new front-end from a user server. It worked great.
Brute force more or less. Users had no excuse for not using the las=test version of the front-end. User's didn't always update, but they were on notice that not doing so was at their respective peril.
That's essentially what my tool does - adds a frontend version table to the front end and backend and also adds a table to keep your front end location. It also adds code to the database for doing the autoupdate and sensing it.
When you open your "auto-update enabled" database, the code checks to see if the front end matches the master front end version number. If not, it tells you that it needs to update and that it will and then automatically reopen. Then, it dynamically builds a batch file to kick off as the database closes and it deletes the old, copies the new and reopens the front end for the user. For most people it works great, but for a few they've had an issue where it closes, and either doesn't continue and leaves the batch file on the desktop or does the copy but doesn't reopen the database. I think someone posted a different solution in that same thread.
I had a similar issue as you. The way i got around it was to create a batch file.
Its a bit old school but we have a few restrictions on what we can and cant do where i work.
The batch file basically pointed to a shared drive and copied the Front end file to a set location on the users desktop. and then launched it.
This way i knew that as long as my updated Front End had the same file name, when ever i updated and saved it when the user next used the Database they would have the updated Front end.
as i said bit old school but really simple and easy to set up
There are several ways you can do this. Here is a post I started with a Front End Auto Updating Utility. There are some good posts later on, including OldSoftBoss' enhancement of my tool. I think there are some other suggestions also in that same thread.
However, I can't seem to get it to work:
I'm using Access_Front-End_AutoUpdating_Utility_rev06Sep2008 from the page linked above. I'm developing in Access 2010, but my users have Access 2007. My database is split and both "halves" are in .accdb format.
I created folders to test your utility as follows:
D:/update test/back end/MyDB_be.accdb
D:/update test/front end/MyDB_fe.accdb
D:/update test/master/MyDB_fe_master.accdb
After running the utility:
tbl-version_fe_master is present in the back end.
tbl-fe_version and tbl-version_master_location are both present in the master.
Both the back end and master have matching version numbers in their respective tables and the location for the master is correctly recorded.
However, when I open the master I receive a Run-time error '3078' saying that it can't find 'tbl-version_fe_master'. Debugging takes me to this line of the "cut and paste code" your utility has me add to the startup form: strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")
Looking at this, the thing that jumps at me is that the "missing" table resides in the back end, but the front end isn't linked to it and nothing in the "cut and paste code" creates a recordset to reference that table in the back end. Makes sense that it can't find it.
To deal with that I linked the master to that table in the back end. This seems to have solved that problem, but I'm not out of the woods yet.
My startup form already had code in it's Form_Load() event:
Code:
'check for logout flag
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("tblLogoutFlag")
If Rs!Logout = True Then
DoCmd.OpenForm "frmMaintenance"
Else
DoCmd.OpenForm "frmHome"
DoCmd.OpenForm "frmMaintenance", , , , , acHidden
End If
BUT, your "cut and paste code" contains this line:
Code:
If CurrentProject.Path = strMasterLocation Then
Exit Sub
This is preventing my code from running and causing my db to stall on the startup form.
My initial thought was to replace Exit Sub with my normal startup code:
Code:
If CurrentProject.Path = strMasterLocation Then
'check for logout flag
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("tblLogoutFlag")
If Rs!Logout = True Then
DoCmd.OpenForm "frmMaintenance"
Else
DoCmd.OpenForm "frmHome"
DoCmd.OpenForm "frmMaintenance", , , , , acHidden
End If
But, I realized that would only work when opening the master. Then it dawned on me that if I make a copy of the master in a different folder (as it would be in production) that copy will work as intended.
However, it feels wrong that the code will work in either the master or a copy of it, but not both.
So, how about this:
Code:
Private Sub Form_Load()
'----------auto update utility courtesy of Bob Larsen
'----------http://www.btabdevelopment.com/ts/
'----------minor changes have been made to original code
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 [COLOR="Red"]<>[/COLOR] strMasterLocation Then
' 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 auto update utility
'check for logout flag
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset("tblLogoutFlag")
If Rs!Logout = True Then
DoCmd.OpenForm "frmMaintenance"
Else
DoCmd.OpenForm "frmHome"
DoCmd.OpenForm "frmMaintenance", , , , , acHidden
End If
End Sub
The major change is in red. This should still bypass the check if you're working in the master while allowing existing code to run. And it works in both the master and copies in other locations.
Do see any flaws in my logic? I haven't actually tested new versioning yet, as I spent a good while just getting everything to open properly.
*please forgive any inconsistencies in tense as I was working through many of these issues while I was writing this post. The post started out as questions, but evolved into how I dealt with each issue I was encountering.
Now that I'm trying to test the actual updating process I've encountered more problems:
If I get a version mismatch, the message box notifying me that I need to update appears as expected. Clicking OK closes Access, creates the batch file and opens a cmd window (minimized). While watching the folder where the front end resides, I can see the original front end and the batch file.
Then I receive an error: "Windows cannot fine 'D:\update test\front end\NIS_test.accdb'. Make sure you typed,..blah blah blah
About a half second after this error appears I can see the original front end file get deleted. So while it's telling me it can't find it, it's deleting it a moment later.
Looking in the cmd prompt I see:
Code:
D:\Documents>Echo Off
Deleting old file
Pinging 1.1.1.1 with 32 bytes of data:
Request times out.
Ping statistics for 1.1.1.1:
Packets: Sent = 1, Received = 0, Lost = 1 (100% loss),
Copying new file
The system cannot find the file specified.
'CLICK' is not recognized as an internal or external command,
operable program or batch file.
And with that we've completely left the land of things I understand.
I did try to change the value of the ping to 4000 to buy more time after seeing that it was inserted for timing issues, but that didn't change anything so I put it back to 2000.
Echo Off
ECHO Deleting old file
ping 1.1.1.1 -n 1 -w 2000
Del "D:\update test\front end\NIS_test.accdb"
ECHO Copying new file
Copy /Y "D:\update test\master\NIS_test.accdb" "D:\update test\front end\NIS_test.accdb"
CLICK ANY KEY TO RESTART THE ACCESS PROGRAM
START /I "MSAccess.exe" "D:\update test\front end\NIS_test.accdb"
It looks like it's assuming that my master and the copies will have the same name. Is that a requirement for this utility to work?
update: I just tested it with the master and my front end both having the same name. It works. But what happens if (when!) my users change the name of their file?
I'd like to think that if I'm capable of deciphering most of the code contained within Bob's utility, reading a date is not beyond my ability. I'm aware of how old this thread is.
Using the forum's search function for "auto update utility" gave me this thread as having the most recent activity on the subject.
But, I do thank you for your insightful assistance in helping me to get this working in my database.
I've created a way to solve the name mismatch issue:
In my master front end, I added an additional table: tbl-version_master_name with a single field named s_mastername (this follows the convention used in tbl-version_master_location). I added the name of the master (complete with file extension) in that field.
In the basFEUpdate module, I modified
Code:
[COLOR="Lime"]' sets the file name and location for the file to copy[/COLOR]
strReplFile = g_strCopyLocation & "\" & [COLOR="Red"]CurrentProject.Name [/COLOR]
to read
Code:
[COLOR="Lime"]' sets the file name and location for the file to copy[/COLOR]
strReplFile = g_strCopyLocation & "\" &[COLOR="red"] DLookup("s_mastername", "tbl-version_master_name")[/COLOR]
Now when a version update happens, my user receives a copy of latest master that retains whatever name they've chosen for their front end.
Here is a summary of all the changes I made to make this work:
1 (required) Link the front end to tbl-version_fe_master
2 (optional, but allows pre-existing startup code to continue to function)
Change If CurrentProject.Path = strMasterLocation Then
to If CurrentProject.Path <> strMasterLocation Then
Then replace the original Exit Sub with the code from Else getting rid of Else altogether.
3 (optional, but allows for user's front end and master copy to have different names)
Changes detailed in this post
Sorry to keep resurrecting this old thread, but I was able to add some functionality to Bob's utility and wanted to share what I'd done.
My development environment is several times zones away from my customers and I have no access at all to their network. While this utility really helped with pushing new updates to end users, I still had to depend on someone on that end to re-link all tables to their production back end and update the version number stored in the back end that Bob's utility depends on.
I've solved both issues. Now when I send a new front end to my customer, they just put it place and open it once. When it opens, it reestablishes the back end links (that's another utility I'm not going to address in this post) and it then updates the stored version number in the back end.
my entire start up form's code:
New stuff is in red
Code:
Private Sub Form_Load()
'refresh table links if master copy of front end
If CurrentProject.Name = "Master_Front_End.accdb" Then
Dim strMsg As String
'Run the Procedure, getting any error messages.
strMsg = RefreshTableLinks()
'strMsg will be a zero-length string if there is no error message.
If Len(strMsg & "") = 0 Then
Debug.Print "All tables were successfully relinked."
Else
'Notify the user of the errors.
MsgBox strMsg, vbCritical
End If
End If
'----------auto update utility courtesy of Bob Larsen
'----------http://www.btabdevelopment.com/ts/
'----------minor changes have been made to original code
Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strFilePath As String
[COLOR="red"]'check for logout flag first
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Set db = CurrentDb()
Set Rs = db.OpenRecordset("tblLogoutFlag")
If Rs!Logout = False Then[/COLOR]
' 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 not the Master then perform version checking
If [COLOR="red"]CurrentProject.Name <> "Master_Front_End.accdb"[/COLOR] Then
' if the version numbers do not match run 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
[COLOR="red"] 'if it is the Master, update stored Version number to match Master Version number
Else
If strFE <> strFEMaster Then
Dim rs2 As DAO.Recordset2
Set rs2 = db.OpenRecordset("tbl-version_fe_master")
rs2.Edit
rs2!fe_version_number = DLookup("fe_version_number", "tbl-fe_version")
rs2.Update
End If[/COLOR]
End If
End If
'----------end auto update utility
'open db according to Logout Flag status
If Rs!Logout = True Then
DoCmd.OpenForm "frmMaintenance"
Else
DoCmd.OpenForm "frmHome"
DoCmd.OpenForm "frmMaintenance", , , , , acHidden
End If
End Sub
One change I ended up making was testing the file name instead of the file location. Being removed from my production environment, the stored Master File Location doesn't exist in my development environment. This meant I couldn't recognize the Master Front End locally since it would never be in the Master File Location. Changing to checking the file name solved that problem. With that change the only thing that doesn't work in my development environment is the actual copy of the new version. It tries to copy from the Master File Location and can't find it which causes the batch file to stall. Not a big deal since I can just delete the batch file and grab a new copy manually.
The business with the logout flag is something I have in place to facilitate automated backup and compact&repair. The flag alerts other front ends to get out of the way while the maintenance occurs.
If that maintenance is in process, the version checking might error out by looking for the back end while it's undergoing the c&r. Checking the logout flag status first prevents that.
It doesn't loop back though. I figured it would be fine if the user has an outdated front end for that session. Odds are they won't hit the maintenance window twice in a row since it's such a brief event in the middle of the night.
Anyway, the meat of what I wanted to share was the method to update version numbers automatically instead of having to manually do it in the table. All that other rambling is just me being exciting about what I've accomplished.
