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.

Biggus

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

http://blogs.msdn.com/access/archive/2009/07/28/meet-the-access-2010-macro-designer.aspx

User Maintained Spreadsheet Applications Suck

I have one special application in Excel that I am especially proud of . This app has functioned for 4 years pulling data from a Data Warehouse using OLEDB and merging multiple data sources and using my work as the core of a major monthly report.

Unfortunately every two or three months I have recieved an emergency call that there is a fundamental problem in the app producing erroneous results. In EVERY case the problem gets tracked back to a user error on the part of the principal user of the final application.  See – the one strange thing about this application is that while it is VERY complex and integrates multiple data sources and uses a complex system of queries and custom formulae, the client INSISTS that he be able to make changes whenever he feels necessary – and he has a habit of making changes that break things – not errors on his part perse, but he doesn’t understand the implications of changes on the rest of the app.

I then get a call in a big panic as if it’s a problem with my app.  Then he apologizes when it becomes apparent it was his fault (at least he DOES apologize).

Why do I allow this?  It is because when we started this application I was contracted to fix or replace a process with two failed previous attempts by others. It was in a big panic, and I was told the goal was to produce a “locked-down” application.  In the end though the client is the client, and if they choose after everything NOT to have it locked down – what could I say (??)

My comments to this guy have always the same “Your business process really isn’t changing at all, so these changes are really mostly impulsive on the part of senior management than real changes in the business requirement. Even if they are justified these requests could if necessary be accomodated outside of the application using some kind of Ad-Hoc capability that doesn’t threaten the reliability of thi core report.  And if you don’t let us lock this down, in the end IT will insist on replacing it with something you’re simply not going to like and which you won’t be abe to change under any circumstances !!”.  This doesn’t work ….. no change.

Stop the Presses !!….

As I was writing this article I JUST found out that this guy is GONE (as is the way of the world today).    This was not staged – this is what really happened today ….  Too bad – he’s a great guy.

Now there are a lot of new questions about this application.  I know this is a critical report that gets rolled in with world-wide reporting.  Who will inherit it?  How do we find out where the bones are buried in this app now? Is there any money available for me to swoop in and make it work?  If not – then what??  Will it be supported going forward from India? Is the s**t going to  hit the fan? …..  Will I get blamed for the fact that the application is so “fragile” at this point with changes that I do not know about?

So in the end my argument against user-maintenance wins out.  My idea is to take well defined, mature business processes and automate the reporting for them in such a way that even if I win the lottery (as opposed to getting hit by a truck), the application will be able to tick along as long as necessary.  The client will get their money’s worth from my efforts and thus hopefully will be inclined to reuse me for other things.  My goal is to make apps that do NOT rely on my support.

 So once again my worst nightmares come to pass.  My biggest fear is always that my client leaves or is fired and then –  who comes in to replace them and what happens from there to this app that is so important to their business processes?  And my failure to win the User-Maintainability battle is leaving me exposed.

Here we go again ….

Biggus

p.s. Simon Murphy has a GREAT reference to this article at:

http://smurfonspreadsheets.wordpress.com/2009/07/30/is-change-control-the-critical-factor/

Thanx Simon.  Looks like the Circular Reference indicator just lit up on my Browser

Offering Opinions

One of the things I learned a long time ago was “Don’t tell the client how to run their business !!”.   Some clients have taken that to mean not even suggesting business process changes in and around the applications we are working on.  “Don’t tell me my business !!”

But in today’s new economic environment I am less reluctant to hold back my advice because in addition to the frustration this has caused me (having to work around wonky, stupid processes), companies NEED help to become more efficient and should be open to any suggestion that could lead to a productivity improvement as a result of a technology solution you can provide them.   If they don’t like my opinion then I will go get another client.

I know that can be tough to do, especially when clients can be hard to find, but a bad client can be a great drag on your business and your life. Don’t let fear or desperation (surely it isn’t that bad yet) get you stuck in a lose, lose situation. 

It is also VERY important that we promote our value beyond just formulae and indexes to the point where we are “perceived” as adding business value for our clients’ processes as these processes impact the work we are doing for them.

Biggus

p.s. Just to lighten things up here are some pictures that explain wny I haven’t been as productve as I could be this summer – Grandkids, dogs, dirt and water – a bad combination….

Office 2010 Here We Come !!

Say what you want about the (Ef)Fluent UI (I love it when they don’t think through the possible bastardizations for a word - I especially liked “Back Orifice” – very clever), I think we have to accept that Microsoft has just laid it down and said “Ribbon – Get used to it !!”

I think that with the apparent success of Windows 7  (I’m enjoying it) I think we can assume that over the next 18 months a lot of companies will go to it – and I guess that most of those will bundle a move to Office 2010 at the same time in one pass to make it easier for them.  Having just set up a VPC with Windows 7 and Office 2010 I couldn’t help but think how reluctant corporations with thousands of users may be to do this too often.

So I think we can assume that we will see things moving fast on this front as IT departments try their darnedest to justify their existence until the economic tide turns (if ever).  Certainly there is the old “Wait for the first Service Pack” policy but I think we may see that get over-looked in an effort to “be seen to be doing something”.

When this finally happens I can see a lot of “need” for assistance making things like Excel and Access apps work in the new environments – work that hasn’t come up yet because so few have REALLY moved to O2007.  So NOW we need to seriously determine the issues they are going to have so we can look like heroes every time, and then we all have to get out and sell ourselves !!

While I have always thought that keeping my knowledge to myself amounted to protecting my Intellectual Property (why I have never gone after membership in the MVP program),  I am afraid that will have to change.  In the words of Ben Franklin (the great womanizer and philosopher – and politician) “We all have to hang together or we’ll all hang separately.”  We have to work at increasing the visibility of Excel and Access development as a ”legitimate” endeavour, worth professional fees.  I think we will have to do it together.

Thoughts on how?

Dick

Information Consultants

I was talking to a client a couple of days ago and he said he would be willing to pay me from his own budget as an “Information Consultant” rather than as a provider of IT services.  I can see that working for Excel but would unfortunately be a more difficult sell for Access (although there’s no real reason why).  I work with people who use Excel (and Access) to collect and analyze “Information”.  Some of that info can come from centralized databases and corporate but as we all know here, a lot of it comes directly from spreadsheets or is calculated in spreadsheets.  This is “Business Information”. 

If the user did their analysis using a piece of paper, or even a calculator, would that be considered an IT function?  Is the preparation of a Word-Processing document an IT function or “merely” part of the business process?  If you hire a communication or marketing specialist to help you make a slick and professional Powerpoint presentation is that an IT function?  If not then what is the difference?

Microsoft has a class of reps worldwide that specialize in Information Workers focusing on the tools that IW’s use.  Unfortunately I’m pretty sure their path inside organizations is still through IT and they wouldn’t appreciate this distinction. But it’s an open question though whether support of IW’s is an IT or a business function. 

I will be talking more about my IW skills and less about my IT skills when discussing solutions with clients for a while and see what happens.

Biggus

Monday, Monday …..

Over the past weekend I just thought of something that I have to do in the future.  I have to make sure clients do not arrange status meetings on projects for Mondays anymore !! This is because it ruins my weekend before it and adds too much stress to my marriage.

I admit openly that I am a big believer in “the squeaky wheel gets the grease” methodology of consulting.  If a client’s hair is on fire they get my attention and urgent response.  I am also a major procrastinator and tend to do my best work when there is a deadline staring me down (which I don’t think is totally untypical I’m afraid).

I have recently run into several clients who insist on meetings on Mondays and they don’t realize that in doing that they have just ruined another weekend for me.  I have just spent this whole past weekend working on stuff for a meeting this afternoon.  Then I’m going to take some time with my Grandkids that I should have given them on Sunday.

So rather than becoming more disciplined and better organized I am just going to change the scheduling of meetings for mid to late week in the future.  So clients be warned - my Mondays and Tuesdays are now all officially “booked” ahead (unless of course your hair is really on fire).

Biggus

Walking and Chewing Gum

I am very thankful for the thoughtful discussion of the previous thread. 

Firstly it shows the influence of Mr. Murphy on our community as he has single-handedly woken up my Blog – thank you my friend.

Secondly I find that even though that thread could go on and on I suppose I have to keep “priming the pump” and launching new threads to keep you all coming back.

Third I would like to evolve the discussion as we move forward.

So here is a new thread that continues on what has been discussed so far:

Let’s assume that we are mostly on-side with the fact that the desktop development space is under threat from various angles (IT, Economic problems, too much grey hair (or not enough hair)), and Microsoft’s failure to appreciate the significance of this space as it continues to try to out-Google Google and out Oracle Oracle.

So while you can please continue to contribute to the Earth Moving thread, I will talk here about what can be done to turn the tide back for spreadsheet and departmental database development.  Unless we choose to abandon the space altogether, we all have an obigation to see what we can do to solve this problem.

For years now I have been travelling out to MicroMecca at my own expense, sometimes more than once a year, in an effort to influence the trajectory of our chosen technology.  I have sat on various Councils and SDRs and Insiders groups and in addition to being informative and reinforcing they have also been a lot of fun. I have made some very good friends in the groups and also within Microsoft.  

Up to now though, I have had the policy that my thoughts were mine and Microsoft’s alone mostly because I know that publicly criticizing Microsoft is the kiss of death.  But I can see that people like you and me are going to have less and less influence inside Microsoft as we go forward.  If I am to have influence it probably has to come from the outside as well – and I accept the inherent risk there is to that. 

I also have decided that it is not good enough for Microsoft to be the only company that knows who I am and how good I might think I am – I have to get visible – we ALL have to get visible. 

I have also decided that even though I don’t work for the company, I am not out of line to have opinions on the operation of Microsoft as it relates to MY business.  This is especially true since there are NOT a lot of alternatives that at this time would potentially be  business opportunities for my company in satisfying the Departmental clients (thanks in large part to the efforts of Microsoft over the past twenty years).  There’s simply no money in Open Office development and support (yet?).  So I think I am allowed to have an opinion .  Otherwise my option would be to go to someone else’s technology – which I do NOT want to do.

The incredible success of Excel and Access’s underground ubiquitousness – (there’s a word for ya !!) was simply because business users saw the value of them.  But for the last 20 years there has been a concerted effort on the part of  “professional” technologists to kill them – simply put.

Ironically, and probably not on purpose, it  has been apparent for a while that the focus within Microsoft is to talk to those that make the buying decisions at the highest level of large corporations rather than those who do the actual work or those Line of Business staff who would actually get value out of the technology.  These are the same people who wish Excel and Access would F-Off and die.  So promoting the Desktop development tools would be a direct threat to Microsoft’s ability to impress the buyers of their high-end technology.  Guess who loses?

But I remember the saying about President Gerald Ford (a great guy for a Republican – oops this isn’t a political blog – me bad) that he couldn’t walk and chew gum at the same time.  In other words he wasn’t much of  multi-tasker (which I’m sure wasn’t true).  The same thing has always been true of Microsoft.  They get on one major theme and it ripples through the whole organization for a period of time (NT, Internet, SQL Server, etc.).  Guess which technology is their current one ???? 

Now that the company is NOT run by a technologist and now that the chief technologist is OBSESSED with Communications and Collaboration over all other uses of technology (the echoes of the demise of the Lotus Development Corporation are frighteningly loud in my mind), those have become the core message (obsession) of the company .

I love Sharepoint and Communicator and all the stuff they are coming out with in that space and I am working hard to encourage integration of Excel and Access into the SharePoint world.  I also suppose that only promoting the SharePoint technologies make sense if your main concern is the short-term profitability of Microsoft.   My argument though is that maintaining interest in the Desktop and promoting Departmental development with Excel and Access is in the short AND long-term interests of Microsoft.  Of course Microsoft prides itself on being a “Leader” by developing and promoting new and exciting products for their customers, but I don’t believe that means abandoning the good products that ironically are a large part of the argument for Windows running on Smart-Clients. 

An argument can be made that without Office who needs Windows and without Windows who needs Microsoft (??).  I guess unless you believe that SharePoint will be the new Windows – hmmmm…

At the same time who is going to satisfy the needs of all the millions of smaller ways technology can help businesses be more productive ??

So while we who are the true believers must speak out and explain the value of our technologies whenever and wherever we can, I believe that things are really not going to change until Microsoft makes an effort to keep people in their Excel spreadsheets and their Access databases.  Good old “Lock-in” ?? 

Microsoft has to learn how to “walk and chew gum at the same time”….

Biggus

The Earth Is Shifting Under Our Feet

I have been an independent spreadsheet developer for 25 years and an independent Access developer since 1992.  In that time I saw my rates triple and the billable hours sustained to the point where  I was able to consider myself a “professional” of some kind and where I considered my gig equivalent to a “job” in IT management.  I fear that unless something is done, and fast, by people who have the ability to influence the marketplace (and it isn’t people like me by the way), the jig is up – so to speak.

I am seeing a market where corporations will simply not spend money on Departmental applications as they gather all their IT Dollars (or Euros or Pesos, etc, etc) into a circle behind the Enterprise computing model.  Worse still I see where all IT spend is now controlled by IT themselves therefore the era of “Departmental” applications arranged, planned and paid for by Line of Business managers is over.   IT has taken the levers of all spend on technology – even spending on a small database or a budget spreadsheet application.

This means several things:

1. In the near future corporations will in effect be run by user-developed (mostly bad) spreadsheets as IT fails to deliver the lower-level technology solutions that people like myself have delivered for the past generation.

2. There will be no funds available for anything like a “professional” class of specialists in the area of Spreadsheets and smaller databases (such as Access).

3. It is time for people who are currently employed by or who have a long-term gig with one client doing their internal specialized Excel and/or Access apps to realize that if their gig disappears (which it eventually will – NOTHING last forever – especially in this business climate) they will have to go do something else – or work as a volunteer – and stop kidding yourself.  They must consider their options and soon.

The tragedy of all this besides the obvious personal tragedy for us who ply our trade in this space, is that there will ALWAYS be a need for this kind of work.  Furthermore if there is no way that people (especially younger people) can make a decent professional living supporting this space and see a future for their efforts, there will be no new players and no continuity.

The loss to Companies everywhere (both directly and in lost opportunities to be more productive) will be huge.  The loss to Microsoft will be significant (but they’ll never notice – or at least they’ll never admit it). 

Have a nice day

Dick

Thoughts On Access and SQL Server

I am working on some stuff today using SharePoint Lists with an Access Frontend.  Frankly the reason I am using SP Lists is because this particular client’s IT department won’t allow Access going against SQL Server (a common problem). 

SP Lists do work (and I expect them to work even better in SP 2010) but it would be totally logical to use SQL Server Tables as my data store.

The fact is that I have been using SQL Server databases with MS Access linked to tables therein for years – and they just simply work?  I even use an Access ADP file to manage my SQL database, it’s tables, view and SPs to the greatest extent possible (in SQL 2005 or lower anyway) and I find it all VERY productive.

On top of it all, using SQL Server has allowed my apps to be distributed world-wide in Corporations with a serious World-Wide network.  Regardless, in a regular LAN the performance of a SQL Server database over an Access MDB or ACCDB backend is staggering (to say nothing of the stability and scaleability).

I keep asking Microsoft why they don’t promote this idea more and I get no real answer.  My theory is that the SQL Server team doesn’t want Access promoted as an alternative for apps out of some kind of “developer” snobbery and the Visual Studio folks agree.  Since both of these applications are favoured by MS Management and are basically considered “Cash-Cows” for the Corporation, they obviously don’t want Access promoted into their “Space”. 

Who cares what the users really need?  Who cares about the needs of Corporate Departmental clients or Medium and even Small businesses?  Who cares that more SQL Server CALS would be sold and even more Office Pro licenses.  Who cares if this satisfies the needs of a large and mostly under-appreciated market nuche that Microsoft already sorta owns anyway (but maybe that’s part of the problem right there?).

Dick

Things To Know When Using Sharepoint Lists In Access

I just found out an interesting thing to remember when uploading data from Access (or Excel) into Sharepoint Lists. 

If the tables were originally exported from Sharepoint lists into Excel or Access, Sharepoint exports ALL FIELDS in the source including the fields created by SharePoint at creation-time of the original list.  These fields show up on the end of the Field lists in Sharepoint (or when attached in Access) and are internal to the functioning of SharePoint in ways that are mostly obvious from their names.

They include (not exclusively): 

Content Type

File Type

Workflow Instance ID

Modified

Created

Created By

Modified By

URL Path

Path

Item Type

Encoded Absolute URL

and are not visible when in a List View in SharePoint unless you administer them to show up.

The fact is that you will probably never use these fields (but then again you may) but, if you re-upload a table into another Sharepoint list REMOVE ANY FIELDS YOU DID NOT CREATE FIRST. 

If you do not remove the fields then Sharepoint will get somehow confused when you subsequently access the data (inconsistently).  I have noticed that in Access linked Lists I sometimes (no particular rule as far as I can see) get an error that the table is not found.  Subsequent attempts to get at the table succeed though.  But in the meantime your error trapping has kicked in and in a perfect world the application exits with a trapped error.  The Lists seem to work but there is a definite problem.

Remove those extra fields so that Sharepoint can recreate them and you will be fine.

Another important reality is – do not have a field named “ID” in your table.  Sharepoint creates an autonumber field called ID when you create a table.  Because this is an Autonumber it becomes a very useful field for your table joins in your application.  But remember, as in all autonumber fields, if you create new List Tables and rely on the ID field for Foreign Key assignments you will have to match them up using an Update query later.  Therefore before I do such an upload I always create an OldPK field and put the old ID number in there, thus allowing me to match things up again later t new new Autonumbered ID key.  This is frustrating but necessary in my experience and only takes a few minutes and a few Action queries.

I have TRIED to copy Lists between sites in Sharepoint 2007, which would eliminate the need for this kind of kludgy process but I hope in 2010 that function will work (??)  We’ll see I guess.

Dick