I've found Microsoft's VBA strategy to be just about incomprehensible. I don't know how anyone gets Access VBA apps to run, particularly given the profusion of obsolete class libraries, obsolete and partly implemented object models, etc. (Does 2007 have a VBA action based code generator? I can't recall. If so that would help ..)
On the other hand, Access 2003 has an incredibly archaic, and non-VBA related, "macro" facility that does work -- though it's little changed from the 1980s. To my surprise, the same facility was retained in Access 2007, and Microsoft has an article on it: Automate applications with macros in Access 2007. I use Access 2007 for some work projects, so I'll probably try these out.