How to be productive in Access VBA + SQL development?

Question

I'm a 80% ruby on rails developer, but still need to do some Access VBA work.

Some of them are very shit systems, been build ages ago , used by the big enterprise globally , so that most of the works are just enhance the old system.

The techniques are basically MS Access as front-end, linked-table which link to SQL server via ODBC as back end.

Now, I really think I need help , just want to know is there anybody can build the elegant VBA application follow the object-oriented patten?

Even better if you can show me a snippet of code to demonstrate how good it can be, thanks.

Answer 1

Well the first issue to keep in mind is that there’s no magical shortcut to learning MS access. Over the years I’ve learned a lot of development platforms ranging from mainframe systems, databae systems, all the way down to hand coded assembler on a PC. I written two payroll systems from scratch (with the revenue Canada formulas for taxes included in those systems). One system was from scratch written Pascal where I even wrote my own data engine.

Make no mistake, ms-access is a complex developmet system.

You can build gorgeous looking drop dead applications in access. take a look at these screen shots:

http://www.fairsoftware.com/screenshots.aspx

Note the cool ribbons in the above screen shots.

The problem is you can’t learn Unix in a day, and you can’t learn Oracle in a day. You also can’t learn MS access in a day. If those applications you been given to maintain are complex, then hiring a developer with 4-5 years of experience is what you need here. The idea that somehow you going to get up to speed in ms-access faster then say vb.net, or c# is really a false concept here.

In fact I would go so far as to say that you can Learn Oracle quicker than you can learn MS access. While the learning curve in MS access is not so steep, it is very long.

VB6 is a walk in the park compared to access. VB6 forms are dead simple, but the forms in access are very complex (we have about 3 times the number of events and properties for the given form). For example, in access we have two events that fire when an form loads (on-open, and on-load). VB6 forms (and even .net forms) only have one event. The on-open event has a cancel option. If you set cancel = true then the form will not load and will not be displayed.

Logically, this means that the form has two distinct events for two distinct purposes when you call the form. The on-open event will thus have code used for verification and testing of certain conditions of data (and allow you to cancel). If the on-open event is not canceled, then the on-load event then fires and the form loads.

Logically, at this point this means that code that sets up variables or initial values of controls on the form needs to be placed in the on-load event ( in fact Controls cannot be modified but only examined in the on open event). So there’s a very nice granularity and distinction between the two processes that occur in a typical form load. It’s also interesting to note that most products in the marketplace don’t have these two separate events.

As a developer, thus you place the appropriate code and use the correct event for a given purpose. It will take some experience in having used access to figure out which event to use for these things. You could ask if there’s a book that explains this problem, but that’s like asking is there a book that tells you when to use a combo box over that of a list box? I don’t think there is such a book.

The documentation for a combo box will explain what a combo box is, and how to use it. The same goes for the documentation access has for the on open event. You can read what on-open does, but then you as a developer will have to figure out when it’s appropriate to use that event. The same goes for when it’s appropriate use a combo box or that of a list box. At the end of the day, the only solution and how to know these issues is going to be your experience as a developer with the product.

I have an article that talks about using class objects in MS access, and when to use them here:

http://www.members.shaw.ca/AlbertKallal/Articles/WhyClass.html

If you’re looking for code samples from everything to forms to reports to using windows API, a great reference is here:

http://www.mvps.org/access/

Answer 2

You have my sympathy - Access VBA is not object-oriented in any sense like Ruby. You will have to change your mindset when tackling development in Access; such applications are almost always geared around the concept of data rows and sets rather than objects. The user interface is often bound to data rows and sets in a way that hides a lot of plumbing.

Having said that it is possible to build pefectly decent maintainable applications in Access with care and attention. Good luck.

Answer 3

One thing that just futzing around in Access won't teach you is how to create and use standalone class modules. These have some aspects of object orientation, but not a whole lot, but they can be extremely useful in making your code more manageable, as you can wrap a lot of operations in a standalone class module and then treat it as an object that can have multiple instances. You don't get inheritance and polymorphism and a lot of the other buzzwords that go with the OO gospel, but it's worth taking a look at what they can do if you've never used them extensively.

Answer 4

This article is old but make a great case for considering Access as part of the enterprises long-term application strategy.

http://www.fmsinc.com/MicrosoftAccess/Strategy/index.asp

Seth