Using Macros
Pros
- 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
Cons
- 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
Pros
- 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)
Cons
- 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.
