What is the difference between Macro and VBA code in MS Access, which one is preferred at which step

[SachAccess]

Hi,

I am trying to understand difference between Macro and VBA code in MS Access.
From my basic observation, I could see that Macro does not have code written as such.
There are actions associated with the Macro where as a VBA code has a proper code lines written in the module.
Please correct me if am wrong.

I am trying to understand the difference between Macro and VBA code in MS Access and which one is preferred at which step.
Is there any comparison between two. Is one more efficient than other or the entire comparison is wrong itself.
Can anyone please help me in this.

 

[Minty]

VBA code is generally considered superior to Macro's as it offers more flexibility and options.
Debugging a Macro is very awkward compared to VBA.

I think I am right in saying there are things you can achieve with VBA that simply aren't possible in a Macro, but I can't give you a specific example as I don't use them.

The exception is the AutoExec macro, which runs on database open, which I have used occasionally (normally to call some VBA code ).

 

[SachAccess]

VBA code is generally considered superior to Macro's as it offers more flexibility and options.
Debugging a Macro is very awkward compared to VBA.

I think I am right in saying there are things you can achieve with VBA that simply aren't possible in a Macro, but I can't give you a specific example as I don't use them.

The exception is the AutoExec macro, which runs on database open, which I have used occasionally (normally to call some VBA code ).

Thanks a lot for the help. Have a nice day ahead.

 

[GPGeorge]

The only macro you really need is the AutoExec macro. It can be used to do some important things, such as check whether the accdb is in a Trusted Location and offer the user a chance to trust the document in a more graceful way than the built-in "Macros Blocked" message.

Others use it for other startup routines.

Macros really are more powerful than they are often given credit for being, but not so much that you'd probably ever invest a lot of time with them.

 

[SachAccess]

The only macro you really need is the AutoExec macro. It can be used to do some important things, such as check whether the accdb is in a Trusted Location and offer the user a chance to trust the document in a more graceful way than the built-in "Macros Blocked" message.

Others use it for other startup routines.

Macros really are more powerful than they are often given credit for being, but not so much that you'd probably ever invest a lot of time with them.

Thanks a lot for the help George. Have a nice day ahead.

 

[KitaYama]

check whether the accdb is in a Trusted Location

How? Any examples?
Thank you.

 

[GPGeorge]

How? Any examples?
Thank you.

Sure. This macro was created by Kim Young for the Northwind Update project, projected to be launched in the first half of 2023 (hedging to allow for the predictable delays.)

 

[KitaYama]

@GPGeorge Million thanks

 

[The_Doc_Man]

You have gotten a few responses. I'll toss in a couple of thoughts.

First, there is a way to convert a Macro to VBA code. The reverse is not true because there are things you can do in VBA that you cannot do in a macro. In my genealogical database, recursion loops would just have not worked at all via macros. Parsing of a GEDCOM (Genealogy Extended Data common format) file would not have worked. Text parsing in general would have been nightmarish.

Macros tend to be "clunkier" so when dealing with seriously complex code, particularly external application objects, VBA will make working with Excel spreadsheets, Outlook messaging, or Word documents much easier. I don't know that I have ever seen macro code for application objects.

I have not tried modern versions of macros, but back in the earlier days of Access macros, the only thing where they made a lot of sense is if you had a sequence of several non-parameter action queries that you wanted to run and those queries had been thoroughly debugged.

I consider the biggest advantage for VBA is the ability to customize error response fairly easily when you wanted to do different things for different errors in the same routine.

Summary: VBA is more flexible and more capable, which is a two-edged sword. Because it is also capable of extreme, almost bewildering complexity. Macros HAVE to stay simple.

 

[SachAccess]

You have gotten a few responses. I'll toss in a couple of thoughts.

First, there is a way to convert a Macro to VBA code. The reverse is not true because there are things you can do in VBA that you cannot do in a macro. In my genealogical database, recursion loops would just have not worked at all via macros. Parsing of a GEDCOM (Genealogy Extended Data common format) file would not have worked. Text parsing in general would have been nightmarish.

Macros tend to be "clunkier" so when dealing with seriously complex code, particularly external application objects, VBA will make working with Excel spreadsheets, Outlook messaging, or Word documents much easier. I don't know that I have ever seen macro code for application objects.

I have not tried modern versions of macros, but back in the earlier days of Access macros, the only thing where they made a lot of sense is if you had a sequence of several non-parameter action queries that you wanted to run and those queries had been thoroughly debugged.

I consider the biggest advantage for VBA is the ability to customize error response fairly easily when you wanted to do different things for different errors in the same routine.

Summary: VBA is more flexible and more capable, which is a two-edged sword. Because it is also capable of extreme, almost bewildering complexity. Macros HAVE to stay simple.

Thanks a lot for the help! Apology for late reply. Have a nice day ahead.

 

[JasonTFleishman]

Hi,

I am trying to understand difference between Macro and VBA code in MS Access.
From my basic observation, I could see that Macro does not have code written as such.
There are actions associated with the Macro where as a VBA code has a proper code lines written in the module.
Please correct me if am wrong.

I am trying to understand the difference between Macro and VBA code in MS Access and which one is preferred at which step.
Is there any comparison between two. Is one more efficient than other or the entire comparison is wrong itself.
Can anyone please help me in this.

In my experience, after 27 years of using macros, Microsoft fails to support them and errors in the database become unresolvable by either Compact and Repair or Vba Debug. The macros-related errors reveal themselves in a Vba-type catastrophic error 'return without gosub'. The errors also take the form of 'unable to run macro because it's invalid'.
I switched to Vba and have resolved all issues I had been having with macros for decades.
. The Vba coding process feels more rational than macros which seem to have an intermediary between your commands and the database.

 

[Uncle Gizmo]

There's at least one member here who is forced to use macros because their company has has shut down VBA code.

 

[isladogs]

I only ever use 2 macros -autoexec and autokeys
1. Autoexec - as already stated, this runs at startup and can be useful in that it allows certain 'safe actions' to run.
However, its worth knowing that a startup form will load BEFORE the autoexec macro runs. See

Debunking Myths about the Autoexec Macro

This article debunks several widely believed fallacies about the autoexec macro used at startup

www.isladogs.co.uk

2. Autokeys - useful to assign keyboard shortcuts for certain actions e.g. Ctrl+M to open the main form

 

[ebs17]

The safety aspect has not yet been addressed. VBA can read, manipulate and delete many things, including external files and the entire file system. It has everything that a nasty virus needs and therefore poses a potential risk. Therefore, you need trust in the developer and later users that no harmful things will be executed.

If you don't have that trust, including certifications and trusted locations, you'll want to stop VBA from running altogether. A way out there would be to use macros, because they only affect your own application. But this is only a poor way out, because an Access application that is only allowed to use macros is quite simple and uncomfortable. Great demands could not be implemented in this way, something like this will not prevail in practice.

But if you are allowed to use VBA, there are very few reasons to think about macros at all. What macros can do is provided via the DoCmd object and its methods. In principle, these are the options that can be called up via the menu/ribbon. There you have the big disadvantage that invoked actions take place where Access thinks it is active. Therefore, you often have to focus on the desired object first.

In VBA, on the other hand, you have elements of object-oriented programming, so you can address an object by its name even if it is not immediately active.

 

[HillTJ]

@GPGeorge , i came across your autoex macro that checks whether the database is in a trusted location. I like the idea and would like to adapt it to my application where i already use an autoexec to open a log in form. So i glean that if the db is not trusted frmstartup opens, if trusted it runs a startup function. So, is frmstartup an info form something like "please save the database to a trusted location first..".

I guess i'm trying to understand how to implement. Appreciate a heads-u.

 

[GPGeorge]

Actually, it's not mine originally. It was created by Kim Young. I should haven given her credit and will amend the post.

In short, yes, if the accdb is not in a Trusted Location, then a form describing next steps opens. This is preferable to random errors raised when macros or procedures fail inexplicably.

If it is in a Trusted Location, a normal startup routine is called. That startup routine can be whatever initialization code you need for your specific application.

 

[Uncle Gizmo]

I believe at least one of the Microsoft demo databases has an example of detecting if the dB is in a trusted location...

 

[Pat Hartman]

@GPGeorge Since I don't use macros except for two (one to turn off warnings and turn the hourglass on and the second to reverse the warnings/hourglass settings), because I abhor the way logic is handled, I have a question.

Does the macro not support an Else option for the IF? You would never write this logic in VBA the way it is written in the macro. It would be an If with an else, NOT two independent If statements.

 

[HillTJ]

@GPGeorge , thanks. I implemented it basically as you described. If not a trusted location a popup form opens that explains the 'trusted location' issue. On that form is a hyperlink to microsofts 'how to add a trusted location' instructions. Upon closing this form, the database then closes also. I think, pretty neat for just a few lines of code. I agree with Pat, using 'else' instead of two if thens, would make the code even more conscise. Thanks.

 

[GPGeorge]

@GPGeorge Since I don't use macros except for two (one to turn off warnings and turn the hourglass on and the second to reverse the warnings/hourglass settings), because I abhor the way logic is handled, I have a question.

Does the macro not support an Else option for the IF? You would never write this logic in VBA the way it is written in the macro. It would be and If with an else, NOT two independent If statements.

Good question; let me check, but I think Else is not available.
===
Nope I don't see it as an option for Macros.