Monday, October 22, 2007

Macros or Visual Basic in Access?

If you are starting out with a database project and just learning how to create Microsoft Access solutions, one key question is: Should I use macros or Visual Basic (VBA)? We'll just go over some pro's and con's, you make the right choice for your situation.

Using Macros
  • Easy to learn
  • Support for conditional statements (if this is true, then perform this action, otherwise do this other thing)
  • Easy for users to launch directly from the database window

  • Unable to support advanced functions (writing to files, reading files, manipulating form objects, complex conditional statements, recordset manipulation)
  • Cumbersome to migrate to VB if you choose to eventually using VB instead of macros (converting to VB is initially a pain, and so is maintaining both macros and VB simultaneously in many cases)

Using Visual Basic
  • Ability to offer many powerful features in your forms and database (including access to Windows APIs for things like retrieving the NT user name, manipulating window appearance, position, etc)
  • Ability to create extensive error handling to reduce user frustration and handle exceptions (e.g, when a user can't access a given network file share, taking an alternate course action instead of just stopping your processing)
  • Manipulate strings and other variables (e.g., change a name like "Johnson, William" to "William Johnson")
  • Easier to debug by inserting breakpoints, seeing what the current value of a variable is, and adding complex conditional statements (if...then)

  • More upfront time required to learn how to use VB
  • If you don't get formal training, more upfront frustration until you master the language

Certainly there are additional items that could be added to each of the categories above (feel free to comment!), but this condensed list is all I could muster, and should give you a decent foundation for making the right decision.

Wednesday, October 3, 2007

A new twist on databases?

I've been waiting for this to happen, I just expected it to come from Google rather than a startup. But here it is, hosted databases similar to what we're seeing come out of Google and Microsoft for their respective documents/spreadsheet apps online.

I've just started playing around with it, I'll post more as I come to conclusions.

First reactions:

-great concept

-offline access/synchronization is a big need

-security will be an interesting issue...databases generally hold more important information than just term papers