Best practices in Access programming

Question

I'm wondering about some ideas which can improve process of designing solutions using Access and VBA programming language. Of course I'm not talking about best programming practices in general, but only these directly related to Access and VBA.

Everybody knows, that VBA has poor object-oriented programming support, there is no inheritance, polymorphism and so on. So how to ensure DRY and KISS at one time? There are some solutions how to implement common in other languages patterns and strategies in VBA, but frankly speaking, they are often overcomplicated. Which of those are worth to implement?

Before I start a new Access project (if any ;) ), I wish to gather collection of best practices, because from my experience I know that with VBA in Access (and with Access in itself) it's very challenging to avoid bad design concepts and to end with messy, unreadable and repeated multiple times code.

Answer 1

The definitive source for best practices in Access programming is this book:

Access 2002 Desktop Developer's Handbook
http://www.amazon.com/Access-2002-Desktop-Developers-Handbook/dp/0782140092

You should get a copy if you're serious about programming in Access. These guys are the experts.

I realize the book seems dated, but all of the information in it still applies. I suppose it never got updated because this kind of development is a bit of a niche area. But Access has not changed all that much internally (it's one of the only remaining software development tools left that still uses what amounts to a dialect of VB6), and most of the information in the book is still good.

The companion book that focuses on Client/Server development is here:

Access 2002 Enterprise Developer's Handbook
http://www.amazon.com/Access-2002-Enterprise-Developers-Handbook/dp/0782140106

Answer 2

One thing i always had to do when I did Access programming was the use of a lot of hidden fields for binding reasons. I made sure that i made the field invisible and also changed the color of the field to foreground white and background red so that people knew this was a hidden field.

Another best practice I used was using modules for all of my shared code. Get into the habit of putting a lot of your reusable code in modules.

Answer 3

I'd like to add here some other questions and answers related in a way or another to the same issue. The pointers might lead to my own answer to these questions, but do not hesitate to browse other's answers!

MS Access as enterprise software

Best way to test an MS-Access application

Working with multiple programmers on MS-Access

Recommendations on using SQL server GUIDS from MS-Access

I must admit that one of the main constraints of Access is the limited object model. I was specifically annoyed by the lack of possibilities to add my own properties and methods to the Form object. I recently found an efficient turnaround to this problem by creating 2 extra objects:

• the "AllMyForms" object, which in fact maintain 2 object collections: one is the standard Access forms collection, the other one is a collection of all the instances of the "customForm" object. Both collections are indexed with the hwnd property of an opened form (or, to be more specific, the hwnd property of the instance of a form, allowing me to open multiple instances of the same form).

• the "customForm" object, which lists my custom properties and methods of a form's instance

In this way, I can refer to properties such as:

accessForms:referring to the standard properties and methods

AllMyForms.accessForm(hwnd).name

refers to the .name property of the access form through its .hwnd value

By the way, the following debug.print will then give me the same result:

? screen.ActiveForm.name
? AllMyForms.accessForm().name   'default value for hwnd is screen.activeForm.hwnd'

Custom forms:properties

AllMyForms.customForm(hwnd).selectClause

will refer to the SELECT clause used to create the underlying recordset of the form's instance

Custom forms:methods

The .agregate method, available for a customForm object, will calculate the sum/min/max/avg of a form "column" (ie sum of values for a control in a continuous form):

AllMyForms.customForm().agregate("lineAmount","sum")

Will give me the sum of all "lineAmount" values displayed on the current/active instance of a form.