Go to Top

7 Habits of successful Access developers

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

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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 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?

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He’s a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

8 Responses to "7 Habits of successful Access developers"

  • Rx_
    September 17, 2013 - 8:11 pm Reply

    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

  • Juan Soto
    August 5, 2013 - 1:45 am Reply

    Thanks to all who left comments! It makes it worthwhile to us and mean a lot to me.

    Juan

  • Ulhas Joshi
    August 2, 2013 - 5:04 pm Reply

    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

  • Anthony Latham
    July 27, 2013 - 7:29 am Reply

    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

  • ronnie valero
    July 24, 2013 - 9:07 pm Reply

    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.

  • Joe Lavery
    July 23, 2013 - 8:06 pm Reply

    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.

    • George Moore
      July 23, 2013 - 8:11 pm Reply

      Nicely said, Brother Lavery.

  • George Moore
    July 23, 2013 - 7:43 pm Reply

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]