|
Automatically Update the Front End of a Split Database
By Tom Wickerath 
Make updating the front end virtually painless.
A multiuser database should be split into a front end and back end. (For reasons why, please see the gem tip: Split the Database.) The back end will contain the tables and relationships. The front end of your database will contain the application objects: queries, forms, reports, macros, modules and any local tables. The front end will include the links to the tables contained in the back end database.
Each user should have a copy of the front end installed on the user's local hard drive. There are several benefits to doing this, including:
1. The user can open a copy of the front end in exclusive mode, without affecting anyone else's use of the application. This allows you to continue making design changes to the front end, without requiring all users to exit the application.
2. You avoid clashing references, in the event that not everyone has the same version of Office.
3. You avoid clogging the network with lots of data that defines each query, form, report, etc., especially if there are any embedded images in forms and reports.
4. Temporary tables and queries do not clash with other users' temporary tables and queries, since these temporary objects exist in each user's local copy only.
5. If a given user's PC crashes, there is much less chance of bringing others' simultaneous use of the application to a grinding halt.
One of the problems developers encounter with split databases is updating every user's copy of the front end whenever there's a new version. Tony Toews (Access MVP) has a free utility on his Web site, the Auto FE Updater, that will automatically distribute the new front end whenever there's a new version on the networked server.
For the situation where installing an executable file from outside sources is not allowed on the network, other options are available. One of these options is to call a function in the front end database, which will copy the update to the user's hard drive. This is usually done by comparing a version number stored in a local table in the front end with a version number stored in the back end database. If the front end version is less than the current back end version, then the UpdateFEVersion( ) function is called. The code shown below currently includes a hard-coded path to the new front end database (strSourceFile). A more flexible method would involve storing the path to the new front end in a local table.
The following procedure makes automatically updating the front end virtually painless.
Option Compare Database
Option Explicit
Declare Function apiCopyFile Lib "KERNEL32" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long
Public Function UpdateFEVersion()
On Error GoTo ProcError
Dim strSourceFile As String
Dim strDestFile As String
Dim strAccessExePath As String
Dim lngResult As Long
'Create the source's path and file name.
strSourceFile = "\\server\share\YourFEDatabase.mde"
strDestFile = CurrentProject.FullName
'Determine path of current Access executable.
strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "
If Dir(strSourceFile) = "" Then 'Something is wrong and
' the file is not there.
MsgBox "The file:" & vbCrLf & Chr(34) & strSourceFile & _
Chr(34) & vbCrLf & vbCrLf & _
"is not a valid file name. Please see your Administrator.", _
vbCritical, "Error Updating To New Version..."
GoTo ExitProc
Else 'copy the new version of app over the existing one.
lngResult = apiCopyFile(strSourceFile, strDestFile, False)
End If
'Modify strDestFile slightly so that it can be used
' with the Shell function.
strDestFile = """" & strDestFile & """"
MsgBox "Application Updated. Please wait while the application" & _
" restarts.", vbInformation, "Update Successful"
'Load new version, then close old one.
Shell strAccessExePath & strDestFile & "", vbMaximizedFocus
DoCmd.Quit
ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in UpdateFEVersion event procedure..."
Resume ExitProc
End Function
Please note that this code is derived from Scott Barker's June 2004 presentation at the Pacific Northwest Access Developers Group (PNWADG) meeting. These meetings provide helpful and useful presentations on topics specifically for Access developers. Should you ever be in the Seattle, Washington area, you are cordially invited to our meetings. For more information, please see the following Web page:
http://www.pnwadg.org/meetings.asp
Created: July 9, 2004
Last Updated: Sept. 17, 2005
Copyright © 2005 - 2006 Tom Wickerath. All rights reserved. Reprinted by permission.
 Top
|
|
About the author:
Tom Wickerath is a chemist at The Boeing Company in Seattle, Washington, USA. He works in the Analytical Chemistry Group of Boeing's Materials & Process Technology (M&PT) organization. In the early 1990's, Tom became interested in the use of database technology to avoid many of the inefficiencies involved in using spreadsheets for the analysis of large amounts of data.
Tom has taught Microsoft Access courses to college students for three years at Bellevue Community College in Bellevue, Washington. He has also been an active member of the Pacific Northwest Access Developer's Group (PNWADG) since the days of Access 2.0 and served as an officer of this group from 1998 to 2002. As a longtime Access expert, Tom has given presentations on complex Access subjects, including tools designed for Access developers, to the Seattle Access Group, the PNWADG and to Boeing Company personnel.
Tom has provided his expertise to Microsoft Access developers and users for years in the Microsoft Access Newsgroups in UseNet, and he was awarded MVP for Microsoft Access in April, 2006. Check the Microsoft.Public.Access Newsgroup for Tom's latest expert advice and feel free to post any questions in these Newsgroups.
Special Note From The Author: "If you find that any of these tips save you a lot of time and frustration with your database development, please consider making a tax deductible contribution to the Northwest Kidney Center, located in Seattle, Washington, USA. I've seen first hand how valuable their work is. With your help, they can do more research in the treatment and prevention of kidney disease, provide increased financial assistance to patients in need, and generally make kidney patients' lives more comfortable. You can even use PayPal to make a quick and easy donation online, and you'll feel better knowing that you have contributed to a better quality of life for those in need. Thanks."
-- Tom Wickerath
http://www.nwkidney.org/ways2help/donatenow/
For questions regarding this tutorial, please contact Tom at:
|
Tom does not accept unsolicited requests for help. Contact Tom only if you have questions or feedback on one of his articles or tips, or you have been specifically invited by Tom in a newsgroup posting to contact him. If your question references a question in the newsgroups, please include the URL to the post
Top
Visitors since 17 Sept. '05:
|
