(Updated 2009-09-06)
|
|
This concept is also known as "agile programming", maybe, although I probably take it further than most.
Philosophy: Work on big things but update/correct small things rapidly. When you need a break from the big thing work on the small things. If a user brings a problem to your attention fix it within a few hours or a day or so. Don't wait a week or a month to bring in a whole bunch of updates.
This way the users can test things in small chunks and give you immediate feedback. Also I don't spend so much time getting an extremely detailed set of requirements from the users. I get a good idea and only plan on getting it 80% correct. Then once they see how it works they can better visualize what more data they need to capture and report.The below techniques are very Microsoft Access specific although some could undoubtedly be adapted to your development environment.
1) Painless updates of the front end (FE). The Auto FE Updater utility is designed to automatically update the FE MDB/MDE and associated files on the client workstation. The utility will, if required, create the directory on the workstation, copy files to the workstation, create a shortcut on the desktop and update registry keys. It will then start the Access FE.
This utility uses the creation date in the server directory to see if the file(s) need to be copied from the server. It stores the creation date in an INI file on the workstation. This way the date/time of the file(s) on the workstation can changed, as is the case with Access MDB/MDEs, but the file(s) won't be copied from the server.
2) Lock new functions currently being worked on from use. Assuming the FE is shipped as an MDE then use the MDE property and make command buttons disabled or not visible. From the Access 97 online help:
Function IsItMDE() As Boolean
Dim strMDE As String
On Error Resume Next
strMDE = currentdb.Properties("MDE")
If Err = 0 AND strMDE = "T" Then
' This is an MDE database.
IsItMDE = True
Else
IsITMDE = False
End if
End Function
3) Kick folks out so BE tables can be updated after hours or at lunch by using ACC: How to Detect User Idle Time or Inactivity. This is also useful to ensure that folks have logged out at the end of the day for backup. If you have some long running process which may exceed the time interval set you will want to turn this flag off for the duration of the process.
Note that the Idle detect time out routine may make previewed reports unprintable. If so just right click on the report to reset the focus and try the print menu item again.
4) Track user login/logouts so if you need to kick people out you know who to contact. Also useful to see if someone is having consistent problems. Also see Determining the workstation which caused the Microsoft Access MDB corruption.
5) Use a drive letter for your BE share. Don't use UNC (\\ServerName\ShareName\FolderName\BE.MDB) to link tables from the FE to the backend. Instead link to a drive letter. Then use the SUBST and Net Use commands to alternatively access a local directory or a network share. This allows you to work using a copy of the BE on your own system. It will be faster and safer. (I, once, accidentally forgot to put the WHERE clause on a delete query and deleted several thousands of records. Fortunately it was on my own copy of the BE.) This means I can, with one click of a mouse switch from using the live backend to mucking with a test backend.
Thus you can use a two line batch file to remove the SUBST and setup a network share. Then you can easily access the live BE using queries, form and reports and find and fix problems. Then you can use another batch file to delete the network share and setup the SUBST again.
tsserver.bat
subst z: /D
net use z: \\server\TrackingSystem
tslocal.bat
net use z: /delete
subst z: d:\TSLocal
Copy the BE down from the server on a frequent basis or after making any changes to the database. Thus you can muck with live data on your own system. You can also copy down the BE even when in use. This has worked fine for us many times. Don't copy it back up of course as it was quite possible that parts of the BE MDB you downloaded were updated during the copying down to your workstation. And, of course, the users have been updating the live BE MDB.
A poor alternative to using the subst command is to use a partition. Then resize the partitions on your hard drive and create small partition. Set that partition that same drive letter. However this isn’t as convenient when wanting to do some work on the live BE on the server.
If you do choose to use UNC then you will need to ensure the user relinks the table to their UNC name rather than yours. You can store the live UNC name somewhere and use the Relink Access tables from code to update the links. Note that I prefer using INI files just because they are quick and easy for someone to view and change if required compared to the registry. Being the pessimist that I am I'd also double check that all the tables Connect String are pointing to the same BE MDB. I do this on app startup as this takes a very short period of time. Much less than a second.
6) Check to see what type of drive your BE resides. If its any kind of a drive besides local then display something very noticeable to warn you that you are working on live data. I choose to display the background colour of the menus a deep red rather than the default colour.
If you choose to use a different directory on a server for your test data then you could place a 0 byte “flag” file in your working directory and check for its existence.
I once was working on a form allowing users to change the inventory items on a purchase order. I was startled when I closed the popup form to view the purchase order to discover all the lines on the purchase order had the same item. In my code I forgot to add the WHERE clause thus updating all twenty two thousand purchase order lines to have the same inventory item. Fortunately I was working on my own copy at the time.
7) If the backend gets corrupted or requires extensive work you need to lock the users out from running the FE. One solution is to rename it while you’re working on it. When the users execute the FE they'll get an "<Path> isn't a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. (Error 3044)". You can trap for this message.
The Auto FE Updater routine checks for a flag in the INI file on the server and displays a message as keyed in by you or the IT staff. This way the FE never gets opened in the first place to cause such a message.
Note: Double check that all systems have the appropriate DLLs loaded such as Jet 3.5 SP3 for Access 97 or Jet 4.0 SP8 for Access 2000 and newer. Do note that the current logic uses just the date. If you want to verify the time of the DLLs as well and your app is going to be used in a different time zone you will want to use UTC time to check the dates. I didn’t bother to go to this level of detail.
My client has some good system administrators who are quite conscientious and careful. Even there though they discovered that they’d somehow missed loading Jet35SP3 on three of twenty workstations.
8) If you are not working on site and you need to update the database design use VBA code to add tables, fields, indexes and/or relationships. See Updating an Access Backend MDBs structure using VBA code.
For additional reading see the following links:
Big IT: Doomed