Access Macros Are The Future


Before anything else I have to admit that I came to the Excel and Access world from the User Community – I did not come to it from a background in professional programming. 

So my first experience with automation was using true Application Macro like Lotus 1-2-3 Macros, Excel XLM macros and Access Macros.  I have to admit that the introduction of VBA in Excel and Access Basic was quite a shock to my system, but I enjoyed the challenge and the capabilities they provided me that were not available to me before. 

At the time I did comment openly that I felt that VBA was kinda over-kill for my purposes as a person who automated spreadsheets.   I still feel that way frankly.   Over the last years I have watched the whole “Managed Code” argument and have accepted the fact that in the end VBA or Access Basic were never leaving the Smart-Client PC.  I agonized for years through the entre process of the definition and development of VSTO in .NET while never getting to the point where I felt it had anything to offer me as a “Guy Who Automates Spreadsheets”.  The fact is that all I ever need is basically a true Macro language and it frustrated me that the presence of VBA was actually becoming a problem for me in the future path of the technology I choose to work in.

Then one day the question was asked at a Council meeting in Redmond “What would you think if we provided a more capable Macro capability in Access?”  The room was stunned.  Then everyone laughed nervously and went on to something else.  But the Microsoft guys were serious – and now we have been shown that they meant business – Access 2010 has a seriously capable Macro capability and a pretty slick Development Environment to work in.  They have created what is close to being a fully functional “Application Macro Capability” for Access that could effectively replace VBA totally for most of the needs of a developer like me.  Should I go over to it?

I have decided that the answer is YES.

Let’s face it – Access is NOT part of the grand Microsoft Developer Universe and is never going to be.  The fact is that we have all been using a kludgey version of VB with much of the heavy lifting added as an afterthought through either our old friend DoMenuItem or Docmd.Runcommand.  These are “Macros” plain and simple.  So many of the skills required to develop in Access are not transferrable to anywhere else anyway. 

So why not just go all the way back to a TRUE macro language anyway and use it do the work we really want, which is driving Event-Driven automation of our Access Databases?  There is no reason to be ashamed about this and in fact it will free us to focus on what really gives Access the edge for rapid development of departmental databases – namely the Queries, Reports, Forms, Sub-Forms that we find so quick and easy to implement.  In the end our job is to deliver the most in the shortest time to keep costs down for clients and to allow us to charge a decent fee for our efforts – we want to just be more productive, not necessary be “Real” Developers like the VS guys.

So now that I have If-Then-Else structures and Error trapping in Access Macros I am going to see what they can do for me.  I think I’m going to like them.  I also think they may open a lot of opportunities going forward that I can’t even see yet.


p.s. Here’s an interesting link related to this topic from the Access Dev Blog:

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

3 Responses to Access Macros Are The Future

  1. sam says:

    Why does Access not have a Macro Recorder like Excel.

  2. Dick Moffat says:

    I’m not sure if it’s possible because how would you be able to record what the developer wants to do when they, for example, after you change the value in a Control what Event do yu wanto fire then (AfterUpdate, OnExit, OnChange, OnLostFocus, etc., etc…..)? I’ve seen this brought up over and over again and in the end I think it just simply cant be done conceptually.

    Remember – when you use Excel recorder you are using the application “live” with all the capabilities of Excel available thru the UI. A lot of Access’s functionality is not in the UI.

    Too bad really 😦


  3. Marcus from London says:

    Hi Sam,

    Excel’s macro recorder simply records the state of attributes in the GUI. Try changing only the font size via Format->Cells with the macro recorder running.

    The resulting VBA code records the current state of every attribute (StrikeThrough, Underline etc) not just the one you changed. Essentially Excel doesn’t know which option you changed so it records the state or value of every option in the dialogue box.

    Further, there are many things that Excel can’t record – try recording a simple message box!

    While recording some actions in Access would be handy – for example recording tables and field creation in your choice of DAO, ADO or SQL script would be nice – most coding in Access involves responding to user events.

    Cheers – Marcus

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s