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.

No comments: