What are the Pros and Cons of VBA Code vs Macros in MS Access?

Question

I have been working on a database and wanted to do it using purely VBA codes. However, I was told by a friend that its more secure for me to use macros because the database will be used on a local network. I am not very verse in the use of macro but if its better way for the database then let me know the pros and cons. and if I decide to later migrate the database to sql will there be problems. the first database I did had a generated errors with with older versions of access which created my inclination to VBA. I know this question is made to sound complex but I just basically want some guidance as to pros and cons of VBA vs Macros in access.

Answer 1

• Macros are considered to be easier to learn.
• In most cases it's probably faster to create a macro than it is to write VBA code.
• Macros can be used in Access Web Apps that are published on a Sharepoint Server. VBA can not.
• VBA is much more powerful and flexible than Macros. You can make API calls, read and write to text files, access other databases or data stores using ADO, run DAO loops through records, and write many other complex features.
• As to security, I don't think one is more secure than the other. Using ADO and Pass Through Queries is one of the more secure ways to build an Access database application because you wouldn't need to use linked tables and you could use Stored Procedures (if using SQL Server). You wouldn't be able to do this using Macros.

In most of the more professional Access applications (not web apps) I've worked on, we only used one macro and it was named AutoExec. When you create a Macro and name it AutoExec, it's the very first thing that will run (even before the form you have set as your main form). This macro is usually used do things like hide UI elements, establish connection strings, link tables, and popup a login form, most of which probably has to be done by calling VBA functions from the macro.

Answer 2

Macros have some built-in functionality that eliminates the need for code, plus you can build functionality quicker with macros. For example, if I want to open a database and run 10 Create Table queries, I can create a macro to do that with just a few clicks. I can do the same with VBA, but it takes longer and requires a much better working knowledge of VBA. I am not aware of a single operation that a macro can do and VBA code can't.