There is definately an advantage to using VB over macros. There is a very HUGE performance increase from what I have heard.
I do know that it does stabalize your database. I had a lot of trouble using macros to do complex procedures inside access, once I moved over to VBA the problems went away.
The one problem with converting a macro to VBA is that Access doesn't allways code it in the most efficient way. For intance DLookups will use the forms!YourForm![YourControl] instead of the more efficient me![YourControl].
I asked this same question on another board and a Access guru named YSB responded with this reply to each of my questions. Here is the cut and paste from that post:
If I have 5 macros, does that translate into 5 modules or do all the macros go into one module?
First a word about modules. There are two types of modules for our purposes. Modules that are connected to a form and independent modules. If you created macros in an independent section of Access (which I think is always the case) then when converted they will go into an independent module. One module can technically hold as many functions as you want. Therefore I would presume that if you have 5 macros in one file they would wind up in one module. If they are in separate files I would guess they would go into separate modules. Independent modules are loaded into memory when the program starts and they remain there. Therefor if you have a macro that is specific to a form, you would be best off moving it into that forms module as form modules are only loaded when the form is opened. I would assume (I never tried it so I'm not %100 sure) that macros are converted into generic functions that are called from the event properties of the controls. In many cases you would be best off moving the code directly into the event procedures if the code was written for one specific control. I'm getting a bit sidetracked now. Let's move on to the next question.
Do converted macros need additional vba programming to get them to function as they did when they were macros?
Probably not.
Are modules event driven like macros or does the code load up when the form/report is open and stay in memory until the form/report closes?
That is really two independent questions. VB and VBA are both event driven. When you click on a command button and it's click event setting is '[Event Procedure]' then VBA will run the function in that forms module named cmdCommandButtonName_Click(). If that function calls another function like one of your converted macros then the code will take a detour through that function before returning to where it left off.
As far as when functions are loaded, an independent module is always loaded while a form module is loaded with the form. What that means to you is that a public function in an independent module can be called from any other module at any time while a public function in a form module can only be called when the form is open.
If a macro is working correctly, is there any benefit to converting it to vba?
Yes. A VB function is more efficient than a macro because it it is compiled instead of interpreted. In other words, VB function are compiled into machine language before the program is run. Macros are parsed and interpreted into bits of machine language when they are called. That means that the VB function will operate faster and more efficiently than the macro that needs to be interpreted every time it's called.
Another advantage to having VB functions is the ability to fine tune what they do. Once you convert the macro to VB you can add, subtract or change code at any time as you see fit.
[This message has been edited by Talismanic (edited 11-08-2000).]
