Best practices in Access programming [closed]

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.

Maillol

I just want to say that this is a great question. It takes a lot of discipline to avoid DRY in access but it can be done. But there is absolutely no preventing mixing ui and biz logic. But I think it is important to remember that THE point of Access is extremely rapid development. Access is the fastest development platform out there for windows app development. So it has a place. Seth – Ovate

Answer 1

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.

Taraxacum

I assume that your point about customer properties/methods is that you can't add them all in one place, i.e., that by default you have to add them in each form's class module? But this can be accomplished with a standalone class module wrapper around your forms, and it sounds like that's precisely what you've done. Can you clarify that? – Steroid

While I understand the value of the customForm() wrapper, I fail to see the value of your custom forms collection. I don't see what it's providing that's not already available with the existing Access collections. – Steroid

Some clarifications: the basic idea is to be able to add my own properties/methods to forms (meaning instances of Access forms). I have one unique procedure to open such an instance, with an "allMyForms.open myFormName, mySelectQuery (if any), etc" method. Each time this method is called, I will add one member to the o_AccessForms private collection, being a new instance of an existing form. I will also add one member to the o_CustomForms collection, being a new instance of my "customForm" object. By doing this I can access both standard properties and custom properties of my opened forms. – Taraxacum

Working with cascading windows, I have for example the allMyForms.customForm().master property which refers to the form instance that "opened" the current form. – Taraxacum

(o_AccessForms and o_CustomForms being two private collections of the AllMyForms object) – Taraxacum

the main role of the "AllMyForms" object is basically to hold the 2 "parallel" collections: creating and deleting instances, refering to these instances (through the .hwnd value instead of the standard form index or form name value. This allow the easy tracking of different instances of the same form) – Taraxacum

Answer 2

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

Stallard

Two of my all time favorite books. I had every edition of the first one. – Ovate

Answer 3

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.

Alliterative