The scenario: You’ve inherited an Access database that was originally built way back say with version 97 and some of the forms have started to look a little tired (which is not an issue) and you discovered that when opening the database there are over 250 separate macro procedures which most were only useful for a single form.
What do you do? You certainly need to consider either tidying up the macros, grouping them in to some sort of logical sequence; reducing the number of Access objects, possibly embed them into your forms (which the newer versions tend to prefer) or convert Microsoft Access macros to Visual Basic using the converter tool.
Here’s a quick video tutorial I found which shows you how to convert a macro…
How to Convert Microsoft Access Macros To Visual Basic For Application VBA
You may wish to note that any external macro objects (that’s a macro which is found in the Navigation Pane) will be converted into a Function and not a Sub procedure as external macros can either be called as a function or a sub routine which the system has no way of knowing how it’s being used.
Any embedded macros (that’s a macro which belongs to either a form or a report) will convert them into the standard Sub routine signature.
It is recommended that you leave both the default options when converting macros to include the error handling trap and any comments that may be present within the macros.
In most cases, macros will convert without fuss though some have been known to cause compiling errors. That is why you will need to re-compile and check the code to be sure.
With Access VBA, you can skin a cat several ways and the conversion code may not always be deemed the most efficient or flexible enough to make code not just re-usable but help to optimise the database further.
It’s therefore worth looking at possible scaling up changing some of the key commands into a stronger structure in particular when working and referencing to create a database and recordset objects using either DAO or ADO as a replacement to perhaps calling the DoCmd or RunCommand (class) objects.
How to convert Microsoft Access macros into Visual Basic is simple enough and has always been a tool since the introduction of Access Basic and Microsoft keeps changing their philosophy as to which route should be deemed the first choice – at present, embedded macros seems to be flavour for version 2013!
If you want to learn how to use Microsoft Access macros as your main calling procedural language, then take a look at my macros eBook offer.
Tags: access macros into vba, access vba, converting ms access macros, how to convert microsoft access macros to vba, macro to visual basic code, using access vba