A macro is, essentially, a simplified VBA script. In other words, everything that one can do with a macro, one can also do it using VBA. However, as per Microsoft’s own words, “To help ensure the security of your database, you should try to use macros whenever possible and resort to VBA programming only for operations that cannot be accomplished by using macro actions.”
What this means is that because VBA is far more flexible than macros, a malicious developer can write VBA scripts that may pose a security risk to a system. As a result, Access 2007 (and other Microsoft Office application programs) by default disable VBA scripts from a document that is not signed.
On the other hand, because macros are less flexible, Microsoft thinks it is safe to let macros remain active when a document is not signed.
Which one should you choose? Well, that depends. For simpler operations, macros are easier to construct due to the lack of syntactic contraints. However, for more complex operations, macros can “run out of gas”. Fortunately, Microsoft Access offers a method to convert a macro to the equivalent VBA code. This makes it possible for someone to start development with macros due to the gentler learning curve, then transition to VBA code as the complexity increases.