none
VBA VS MACROS in access 2007

    Question

  • I want to know differences between Macros and VBA. Which is better. what are the pro and cons of both.

    Can you explain me???????

    Monday, February 25, 2013 5:09 PM

Answers

  • A.

    What I have recently discovered was, that

    (1st)   CurrentDB.QueryDefs("myQuery").Execute dbFailOnError

    is slower than

    (2nd)   CurrentDB.Execute sqlString, dbFailOnError.

    IMHO, this is because (2nd) is executed directly, while for (1st) MS Access has to open MSys* table(s) to get the right SQL-string to be executed.

    I think it's similar with macros vs. VBA code.

    =====================================================

    B.

    It's very difficult to check for errors in macros. In VBA you can use error-handling.


    Vladimir Cvajniga


    • Edited by Vladimir Cvajniga Monday, February 25, 2013 10:12 PM
    • Marked as answer by EMHO Tuesday, February 26, 2013 4:30 PM
    Monday, February 25, 2013 10:10 PM

All replies

  • the short and simple explanation is

    the programmer has more control with vba

    macros are more "trusted" in the trust center and/or over the internet

    I'm sure someone else can give you a more complete answer.


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Monday, February 25, 2013 6:15 PM
  • Some other answers have already been provided on your posting on Answers: http://answers.microsoft.com/en-us/office/forum/office_2007-access/vba-vs-macros-in-access/77c93e82-bd6d-4821-b4de-469f8238cdd9

    Typically I'd ask, what are you trying to do, then we can tell you which tool to use.

    For instance, if you are creating a web database, then VBA is out and you can only use Macros.  Context is everything.


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, February 25, 2013 6:46 PM
  • I try to do best performance for my Access Database because i will use my DB on network so i was asking myself. What is the best way to my ms access database been faster
    Monday, February 25, 2013 9:55 PM
  • so the question is. What is it faster?????? VBA or Access Macros
    Monday, February 25, 2013 10:06 PM
  • A.

    What I have recently discovered was, that

    (1st)   CurrentDB.QueryDefs("myQuery").Execute dbFailOnError

    is slower than

    (2nd)   CurrentDB.Execute sqlString, dbFailOnError.

    IMHO, this is because (2nd) is executed directly, while for (1st) MS Access has to open MSys* table(s) to get the right SQL-string to be executed.

    I think it's similar with macros vs. VBA code.

    =====================================================

    B.

    It's very difficult to check for errors in macros. In VBA you can use error-handling.


    Vladimir Cvajniga


    • Edited by Vladimir Cvajniga Monday, February 25, 2013 10:12 PM
    • Marked as answer by EMHO Tuesday, February 26, 2013 4:30 PM
    Monday, February 25, 2013 10:10 PM
  • so the question is. What is it faster?????? VBA or Access Macros

    Hi EMHO,

    In my opinion far more important than the speed difference between VBA and Macros is how efficient is your data retrieval in  terms of used keys, and in terms of amount of data transferred over the network, especially when the network is slow.

    If you can only use Macros, then there is no choice. Otherwise I prefer VBA, because of the re-usebility of code to a far extent.

    Imb.

    Monday, February 25, 2013 10:28 PM
  • As far as database speed, one consideration is where it is located. You'll want to ensure that each user has their own Front End (FE) and that the Back End (BE) is located as close tot he root of the drive it resides on and make sure you use the UNC path as mapped drives from different computers can cause application delays.

    As far as Macro Versus VBA. I like a little of both worlds. If I use standard Macro actions I like to use embeded Macros so as not to worry about trust locations and certificates. If however I am going to use some VBA anyway I pretty much convert all my Macros to code so I can check it out so to speak. If using Access 2007 you just open in designview the Form or Report and convert Macro to VBA. I always include the error handling and markup in the available checkboxes.

    In my experience some Macros are faster than VBA and some VBA is faster the Macros I haven't taken the time to figure out which one's make the difference. I haven't found a lot of different. If you like reading code then I suggest playing in VBA as much as you can.

    Enjoy and have fun.


    Chris Ward

    Monday, February 25, 2013 10:36 PM
  • if you use a lot of dsum, dcount, dlookup etc.  in your code (especially in sql) you will be slowing down. it's think usually faster to use a record set that returns what you need rather than any of the d's but for one of's they're handy.

    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Monday, February 25, 2013 11:30 PM
  • If we are talking about performance, with todays computer the difference between the 2 are negligeable.  I use VBA 99% of the time.  It is simpler, more powerful, re-useable,...

    That said, there are things you can do to try and optimize you database

    For one thing, ensure you establish a persistent connection to your back-end, see: http://www.devhut.net/2012/09/29/ms-access-persistent-connection-in-a-split-database/

    Place you back-end in the highest directory that you can (do not burry it 20 directories deep, regardless of what the IT department says).

    Also, carefully review  http://msdn.microsoft.com/en-us/library/office/dd942824(v=office.12).aspx

    You also have to note there are a multitude of factors you cannot control: network speed for instance.


    Daniel Pineault, 2010-2012 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, February 26, 2013 12:17 AM