Access developers come in a wide range of skill sets, but what set apart those who are good at Access development? I’ve put together a rather subjective list of 7 habits for your review, let me know what you think in the comments!
Habit 1: Using error handlers in all procedures
Errors generated by Access are too vague or meaningless to users, that’s why good developers add error handlers to all of their procedures. Doing so can be a real pain, but if you download and install MZTools it’s as simple as a mouse click. I’ve customized my install to include Erl in the auto generated code.
Habit 2: Comments in your code
Have you ever looked at your code six months after you wrote it and have it look alien to you? Get into the habit of commenting your code so that you will avoid head scratching in the future.
Habit 3: Use SQL statements instead of queries
Sure you could use queries as the source of forms and reports, but you are just asking for trouble. Either you or a user may alter the query and make your form or report not work right. It’s for this reason I embed the SQL into my objects instead.
Habit 4: Backup frequently
Access can be a finicky beast, corrupting just at the moment you’ve spent countless hours optimizing your application. Do yourself a favor and backup the way I do: at the beginning of day, four hours into my day or right after some important milestones. I also develop using my DropBox folder, so if something does go wrong I can pull up an older copy.
Habit 5: Use Split Databases
Good developers always split your databases in two files: Front End and Back End, the former has all of your code, forms, queries and reports, the later only data.
Habit 6: Code closest to the object
It can be tempting to create code repositories in your app but it would make it easier to troubleshoot issues and problems if you place your code on the object that’s using it. For example: The order invoice should have the procedure that will calculate totals in the form’s module and not on another module. Exception to this habit: When you need to call the same code from different parts of your app.
Habit 7: Push the Access envelope
Another great habit is to push the envelope with your Access coding. Consider these scenarios: Using SQL Server and calling stored procedures in your code; controlling other Office apps such as Word or Excel from Microsoft Access and developing amazing features in your Access application.
Those are my 7 Habits, what say you about the list? What other great habits do successful Access developers have?
Great list! Appreciate it very much. Please keep up the great publications.
RE: Habit 9: Using TS for hosted Access apps ready to rumble the cloud.
We use Citrix to deploy Access FE with SQL Server back-end for national deployment to PC, Mac and other platforms. Talk up the Cloud!
All reports are generated with Excel Object Model Programming to provide rich features in Excel instead of just a transfer to Excel in text format.
We have experienced less than 3 hours of unscheduled down-time over the last two years. The backups on SQL Server are also tested on a “test DB” deployed by Citrix to insure backups actually work.
A.K.A. Validate the Backup Plan – often
Thanks to all who left comments! It makes it worthwhile to us and mean a lot to me.
Juan
Hi Juan,
Wonderful Points and totally true.
I recently developed a Access 2007 based ERP Solution with 4 locations and more than 20 simultaneous users at each location.
In addition to the above points , we used versioning of code.
We developed our own method of putting version numbers and documenting the modified code with version remarks.
This helped us a lot since if the code breaks, we can roll back to earlier working version anytime.
Anyways, Thanks
Ulhas Joshi
Miracle Software Solutions
Thane,India
I use the following VBA code to backup current Microsoft Access objects and database project. Uses late and early binding:
Private Const mconModuleName As String = “basModuleName”
Public Function BackupAccessObjects(Optional ByVal strSaveToFolder As String) As Boolean
On Error GoTo ErrorHandler
Dim dbs As Object
Dim aob As Access.AccessObject
Dim intLoopCount As Integer
Dim intFormCount As Integer
Dim strNewFileName As String
Dim strOldFileName As String
Dim varReturn As Variant
Set dbs = Application.CurrentProject
If DirExists(strSaveToFolder) = True Then
DoCmd.Hourglass True
intLoopCount = 0
intFormCount = CInt(CurrentProject.AllMacros.Count)
varReturn = SysCmd(acSysCmdInitMeter, “Backup Access macros, please wait…”, intFormCount)
For Each aob In dbs.AllMacros
Application.SaveAsText acMacro, aob.Name, strSaveToFolder & “\Macros\” & aob.Name & “.txt”
intLoopCount = (intLoopCount + 1)
Call SysCmd(acSysCmdUpdateMeter, intLoopCount)
DoEvents
Next aob
intLoopCount = 0
intFormCount = CInt(CurrentProject.AllForms.Count)
varReturn = SysCmd(acSysCmdInitMeter, “Backup Access forms, please wait…”, intFormCount)
For Each aob In dbs.AllForms
Application.SaveAsText acForm, aob.Name, strSaveToFolder & “\Forms\” & aob.Name & “.txt”
intLoopCount = (intLoopCount + 1)
Call SysCmd(acSysCmdUpdateMeter, intLoopCount)
DoEvents
Next aob
intLoopCount = 0
intFormCount = CInt(CurrentProject.AllReports.Count)
varReturn = SysCmd(acSysCmdInitMeter, “Backup Access reports, please wait…”, intFormCount)
For Each aob In dbs.AllReports
Application.SaveAsText acReport, aob.Name, strSaveToFolder & “\Reports\” & aob.Name & “.txt”
intLoopCount = (intLoopCount + 1)
Call SysCmd(acSysCmdUpdateMeter, intLoopCount)
DoEvents
Next aob
intLoopCount = 0
intFormCount = CInt(CurrentProject.AllModules.Count)
varReturn = SysCmd(acSysCmdInitMeter, “Backup Access modules, please wait…”, intFormCount)
For Each aob In dbs.AllModules
Application.SaveAsText acModule, aob.Name, strSaveToFolder & “\Modules\” & aob.Name & “.txt”
strOldFileName = strSaveToFolder & “\Modules\” & aob.Name & “.txt”
If InStr(1, strOldFileName, “bas”, vbTextCompare) > 0 Then
strNewFileName = Trim$(Replace(strOldFileName, “txt”, “bas”, 1, , vbTextCompare))
Else
strNewFileName = Trim$(Replace(strOldFileName, “txt”, “cls”, 1, , vbTextCompare))
End If
If FileExists(strNewFileName) = True Then
Call VBA.Kill(strNewFileName)
End If
Name strOldFileName As strNewFileName
intLoopCount = (intLoopCount + 1)
Call SysCmd(acSysCmdUpdateMeter, intLoopCount)
DoEvents
Next aob
End If
BackupAccessObjects = True
ExitHere:
Call SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
If Not aob Is Nothing Then
Set aob = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
ErrorHandler:
BackupAccessObjects = False
Call LogError(Err.Number, Err.Description, mconModuleName, “BackupAccessObjects”)
Resume ExitHere
End Function
Public Function DirExists(ByVal strDirectory As String) As Boolean
On Error GoTo ErrorHandler
DirExists = (Dir$(strDirectory, vbDirectory) vbNullString)
ExitHere:
Exit Function
ErrorHandler:
DirExists = False
Call LogError(Err.Number, Err.Description, mconModuleName, “DirExists”)
Resume ExitHere
End Function
Public Sub BackupAccessProject()
On Error GoTo ErrorHandler
Dim fso As Object
Dim intResponse As Integer
Dim strDBBackup As String
Dim strDBPath As String
Dim strOldDBName As String
strDBPath = CStr(Application.CurrentProject.Path)
strOldDBName = CStr(Application.CurrentProject.Name)
strDBBackup = Mid$(strOldDBName, 1, Len(strOldDBName) – 4) & “_” & Format$(Now, “ddmmyyhhmm”) & “.adp”
intResponse = MsgBox(“Do you want to make a backup copy of this project named ” _
& vbCrLf & “‘” & strDBBackup & “‘”, vbQuestion + vbYesNo, gstrProgramTitle)
If intResponse = vbYes Then
Set fso = CreateObject(“Scripting.FileSystemObject”)
Call fso.CopyFile(strDBPath & conBackSlash & strOldDBName, strDBPath & conBackSlash & strDBBackup)
Call fso.MoveFile(strDBPath & conBackSlash & strDBBackup, strDBPath & conBackSlash & “Backups\” & strDBBackup)
Call MsgBox(strDBPath & conBackSlash & “Backups\” & strDBBackup & ” has been created!”,vbInformation+vbOKOnly,”Backup Database Project”)
End If
ExitHere:
If Not fso Is Nothing Then
Set fso = Nothing
End If
Exit Sub
ErrorHandler:
Call LogError(Err.Number, Err.Description, mconModuleName, “BackupAccessProject”)
Resume ExitHere
End Sub
Habit 3: Use SQL statements instead of queries
Why allow users to touch your query objects? Maybe I can add ; Habit 8: Bulletproofing your finish Access apps ready for deployment
Habit 9: Using TS for hosted Access apps ready to rumble the cloud.
I think the above is a must and great additions for Access developers habits.
Juan, I like this list.
Here’s a real life scenario that reveals habits to consider when addressing your users:
Director/Mgr wants projects tracking db. Scheduling personnel will create individual project records. Project CSRs will edit and update project records. Supervisors will monitor proj progress via form based queries & realtime reports.
*) Solicit regular input from your end users during development.
*) Keep UI simple and take advantage of access key shortcuts.
*) Provide plenty of training to those who drive the db usage ie: Superviors
*) Refine, Refine, Refine – show users you’re always willing to make their experience better.
Nicely said, Brother Lavery.
Juan – you are a real treasure. I just wanted you to know how much I appreciate what you do to help the rest of us become better application developers. Just purchased MZ Tools after seeing how poorly I have been creating VBA code (I’m not a “True Programmer”, so there’s a LOT I don’t know).
Really glad I found you. Thanks again for all you do.
George