Real World Access (44)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

 Ben Clothier’s Material Planning Reporting
        for Nike Corporation

This application was developed in collaboration with fellow Access MVPs George Hepworth and Oliver Stohr.

Nike's Material Planning department deals with factories and vendors all over the world, buying finished goods from factories. They also cooperate with factories and their vendors in planning ahead how much goods, and consequently raw materials, will be needed to fill orders.

Up to now, they had been depending on ad hoc reporting which they had to request from their IT to assist with their materials buying and ensuring that there was sufficient inventory. This process would take days or even weeks, and the department really needed to keep on top of the inventory fluctuations.

Thus, the department contracted with Advisicon to develop a reporting tool, using a combination of SQL Server, Citrix Metaframe, and Microsoft Access.

The SQL Server is used to collect and store large amounts of data from the corporation's main database. The users all over the world use Access via Citrix to define the criteria and options they want to see in one of a few different report templates.

Access then automates Excel, pulling a million rows of data from SQL Server, formatting the data into sheets and adding some calculated data into the Excel worksheets. The whole process takes less than 30 seconds to fill in the criteria and less than 2 seconds to return the generated Excel report to the user.

Technorati Tags: Microsoft+Access,Microsoft+MVP,Software+Development

Uncomfortable Feeling

As usual for a Tuesday, I was down at the Naenae Pool yesterday.

After my swim, I found the showers were cold, which was not really what I wanted.  There was another guy there with the same problem.

So, I told the pool manager, who sorted it out in a jiffy.  And then he patiently explained to us that if it should ever happen like that again, we just had to go to this certain washbasin, turn on the hot tap full bore for a few seconds, which should free up the flow to the showers.

Ok, so now that’s at least two people besides the manager who know about this little trick.

But it made me think of how often this kind of thing happens in software. It all works well, as long as you are aware of some of the ideosynchrasies of use.

I may have even been guilty of it myself at times.  Of course, if you are making a custom database application for a specific organisation, you probably don’t get so formal as to have a “Known Issues” list.  But I can certainly think of examples (not too many, but some) where I have asked the users to just remember to do things in a certain way, or in a certain order.

I guess, like the pool manager, it seems easier at the time to do the quick and easy work-around, rather than bite the bullet and fix the problem.

But for me, next time I respond to a client’s problem with an explanation of how they can do it different, I will be thinking of the cold showers.

Technorati Tags: software+development

Identity Theft

I got an email yesterday from a guy who runs an internet security business, saying "I would not recommend Facebook to any customer, friend or family for reasons of identity protection."

Does he have a valid point? I confess I don’t keep up with the play on many current affairs issues. Deliberately, in fact, because there seems to be so much scare-mongering as the basis of “news”.

However, I am a regular (though fairly new) Facebook user, and it would appear that if Facebook participation is a threat to identity, then many people, including me, apparently have no desire to protect our identities.

But for those to whom it is a concern, it is of course possible to have a Facebook account with absolutely no personal information (or none that is visible to other users), and membership of a Group, for example, being the only connection. In such a case, you would be pretty much invisible unless you decided to participate in any discussion in the forum for that group, and even then, it is only other members who would see it.

But anyway, I reached for Bing and did a bit of reading about identity theft.

I found this definition: “When someone uses your name, address, bank or credit card account number, or other identifying information without your knowledge to commit fraud or other crimes.”

On another site: “Criminals can find out your personal details and use them to open bank accounts and get credit cards, loans, benefits, and documents such as passports and driving licences in your name.”

Well, in a nutshell, my mind is at rest. All of the information I share on Facebook and other internet locations is stuff that is readily available elsewhere. I don’t know how things go in other countries, but I can’t imagine it is much different from here. You can’t get a passport or driving licence or whatnot, simply on the basis of supplying a name and address and date of birth. Sheesh, these days the banks won’t even give you a cash advance on your credit card without separate photo ID, which of course is totally bizarre because they just have to check the signature.

So there you go. Storm in a teacup.

I actually think it is a very sad thing that we see so often that the authors and commenters in blogs and forums are using false names, and I have sometimes tried to encourage them to use their correct names. Maybe I’m old-fashioned, but there’s something rotten about communicating with someone who lies about who they are.

Technorati Tags: Identity+theft,Facebook

This blog has moved

This blog is now located at http://facets.datamanagementsolutions.biz/. You will be automatically redirected in 30 seconds, or you can click here. For feed subscribers, please update your feed subscriptions to http://blog.datamanagementsolutions.biz/atom.xml.

Support for Sole Traders

SSEs (Small Small Enterprises)

I have been a member of the Hutt Valley Chamber of Commerce for quite a few years. I have learned a lot that has helped me, via some of the courses and events that they run.

I don’t know whether all Chambers in New Zealand are as good. But recently I talked with them about an idea, which they immediately offered their support for.  Here is the notice that they will shortly send out to their mailing list:

Interested in a “Focus Group”?

Maybe you work alone, or as a partnership.

Maybe your business is just you, or just you plus an assistant.

Maybe you work from a home office, maybe from a rented space somewhere, or maybe no office at all.

Such business owners often have a lot in common, regardless of the industry you work in.  For example:

• It can get a little lonesome sometimes;
• The line of accountability can be a bit flimsy;
• Because you’re in charge of doing everything, sometimes you can’t exactly play to your strengths;
• Compared to a larger business, it can be harder to find the time and resources to access professional input;
• Etc…

So… we propose to establish a group to help meet some of these needs.

The plans are a bit loose around the edges at the moment – how it turns out will depend on who’s involved.  But ideas so far include:

• Monthly meetings;
• Informal, flexible, self-regulating;
• Revolving facilitator;
• Emphasis on mutual support, sharing, learning.

A lunch-time meeting to discuss the formation of such a group...
Hutt Valley Chamber of Commerce, Daly Street, Lower Hutt
Monday 1 March 2010, 12:00 – 1:30pm
Lunch provided.

If interested, but unable to attend the meeting, please let us know.

For myself, I know that I often find that problems and priorities become a lot clearer, and decisions a lot easier, if I have an opportunity to just speak out the thoughts instead of trying to grapple with them silently buzzing around in my head.

So I think this “focus group” idea just might lead to an opportunity to reduce some of the isolation that I and other “home alone” small business people experience.

Technorati Tags: chamber+of+commerce,small+business,Micro-ISV

Real World Access (43)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

 Doug Yudovich’s Claims Reconciliation

Sisyphus’s Quest

The process was grueling - if not physically, then mentally, filling one’s day with Sisyphean tasks:

• Print the acceptance reports from the clearing house;
• Print the claim run reports from our billing system;
• Manually identify the invoice numbers between the two stacks;
• Enter matched invoices to a spreadsheet file;
• Scan clearing house reports to identify error messages;
• Highlight said messages;
• Send reports with the identified errors to the processing department;
• The processor scans the reports, looking for the highlighted items;
• Post the errors to the billing system.

When you were done for the day, there was tomorrow — with the claims reconciliation to, well... reconcile.

Calling the process less than optimal was an understatement.  The accuracy of the reconciliation was marginal at best — not all errors were captured, missed by either the initial reviewer or the processor. While not all invoices were reconciled as accepted and processed by the clearing houses, the claim run batches ended up being accepted in the billing system, causing an increase in the Accounts Receivable summaries.

Our billing system vendor was working on a reconciliation solution, but that was at least 18 months out.  We needed an immediate solution.

Enter stage left:  Access!

One thing in which Access excels (no pun intended) is reconciling data from various sources.  Not only can you import data into Access, you can also link directly to external data sources.

In our case, data import was the path we chose, as we were dealing with report files.

In addition to using Access’s data management engine, it is possible to automate other programs from within Access, and not only family members of the Office suite.

We reviewed the various files in the mix, and identified the approach to the data collection. Depending to the format of the source reports, a number of approaches were required — importing the file directly to Access, coding a custom import process, or writing a separate program to manipulate the date into a suitable format.

Thanks to the versatility of Access, we were able to automate that program from within Access. The automation simplified the process on a couple of fronts:

• The user didn’t need to learn how to use a one-off product;
• The automation guaranteed that no steps in the scrubbing process would be missed.

With the data safely tucked in Access tables, it only took a few queries and a couple of reports to get the output we needed:

1. An itemized error report for the processor to post from;
2. An acceptance report from the clearing house for each claim run;
3. A list of invoices that were not accepted by the clearing house within 3 business days.

The third report was a new one.  Identifying missing claims for the processor to take care of before the Accounts Receivable gets too old.

2 months later

Sisyphus had reached the top of the mountain.

The final product was a user interface with three buttons (four when you count the ‘Exit’ button). Importing the data and generating the reports takes about 5 minutes. The reconciliation accuracy is 100% and no claim is left behind more than 3 days out.

To top it off, we freed one staff member to deal with operational needs, versus shuffling (a lot of) paper around.

Epilogue

Today, 5½ years later, we are finally testing one aspect of the reconciliation process in our billing system. We anticipate that the complete process will be incorporated into our billing system by 2012.

Harnessing the power of Access to manage information, and as a rapid development tool, saved us a lot of money and resources.

This application also paved the way for developing additional Access applications for the company. Some became critical to the mission of the business… but that’s a different story.

Technorati Tags: Microsoft+Access,Microsoft+MVP,software+development,claims+reconciliation

Real World Access (42)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

 Gina Whipp's Franchise Management

Imagine you're a Company with franchises all over the United States.

You have Brokers located in several states managing their regions. You have billions of Prospective Buyers all over the world that might be interested in your Franchises. You need to connect the two while still maintaining control over your Franchises and your Brokers.

A Microsoft Access application, using data stored in an SQL Server database, provided the perfect interface for this.

• When a Buyer enters their information via a website, that information is immediately available to the Broker.
• Franchises are automatically connected to Buyers by Zip Code and County, or by Location Interest entered by the Buyer.
• Buyer activity on the website is viewable by the Broker.
• Quickly identify hot Buyers and available Franchises.
• Reminders you set in the Access application are automatically sent to Microsoft Outlook.
• Send eMails without ever leaving the Microsoft Access application.
• Admin logon has full control over Brokers and their Regions and their access to menus.

Since mid-2007 this database handles ten Brokers in as many States, each handling an average of eight states, including Provinces in Canada, with 700 to 1,000 new Prospective Buyers logging on to the website each month.

Technorati Tags: Microsoft+Access,Microsoft+MVP,software+development,franchises

Enigmatic Technology

Some rambling quirky personal revelations

My friend Kirk once told me that I am “an enigma”.  I remember this, because even though I have a pretty good vocabulary, I had to look this one up to be sure.

The circumstances at the time were that I chose not to try and find a place on the floor in a crowded Los Angeles airport departure lounge to open up my laptop and check my email.

Kirk knows that I don’t have a TV or a microwave, and such like. But this email failure apparently was beyond the pale. How can I call myself an IT professional, and pass up any opportunity to use technology to the full?

Not a Luddite

My daughter asked me today about early cameras. I told her about the Kodak Brownie box camera I had when I was (much) younger, and we then watched a guy on YouTube pulling one apart.

But I have a nice digital camera now, and have no urge to revert to older technology.

On the other hand, I bought my CD player in 1987.  It still plays music, and I have no desire for anything more “modern”.

But I prefer playing records. We bought a new turntable last year. My friend Oli thought I was joking when I told him. But our turntable is by far my most recent acquisition as regards entertainment technology.

It was heartening to read an article in our local weekly rag this week, suggesting a resurgence of interest in vinyl records as a music medium. For myself, I don’t know how the interest ever waned. Playing a record is an incomparable process.

Autofocus

I also thought about this theme recently when, being the beginning of the year, I bought my 2010 diary.

For a few years I tried various software – calendars and task managers and all that.  But in the end I had to conclude that for me there is no future in this – it’s just too sterile and remote.  Computers are really awesome for some stuff. Other stuff needs hands on. Simple as that. In the case of diaries, it means pencil and paper - it looks like an increasing number of people are agreeing with this assessment too.

I have also started using a time management system called Autofocus. It is brilliant in its simplicity, and manages to combine the intuitive with the rational.

Such a system can be managed electronically.  However, I join the author of the system, Mark Forster, in preferring the pencil. He says it like this: “I find paper and pen much quicker and easier, and it has a meditative natural quality about it which the computer entirely lacks.”

Sixth Sense

I have recently watched this presentation with fascination. It is a hint at emerging technology, which is always fun to see, and ponder on the possible future real-life application of what might emerge from it.

One thing that struck me in the point that Pranav Mistry makes about this development at the end of his talk. Bridging the “digital divide”, staying human by retaining a more intuitive and natural connection with the physical world, is a desirable pathway forward.

Technorati Tags: Technology,Autofocus

The Place of VBA

John R. Durant helps put things in perspective

Over on Glenn Lloyd’s blog, there is a reference to an interesting article on John Durant’s blog, entitled “Why VBA Still Makes Sense”.

At first glance, I thought this article was making a case for VBA to have a continuing strong place in the toolkit of Office developers.  But a closer reading gives the impression of “damning with faint praise”.

We see a few examples of what makes VBA attractive to work with.  But in the end, the feeling is that it is down the scale.

I don’t think this is how I want to look at it, from the Microsoft Access point of view.  And I think one of the things this article does for me, is highlight once again that Access is exceptional.

Therefore it is not surprising that Durant doesn’t mention Access even once.

He refers to programs that have macro recording features – which in Access, of course, would not make sense.  He refers to the integration of .Net programming into Office applications – and of course, VSTO does not include Access specifically.

However, I would imagine (I have no hard evidence) that in the real world, Access is the main locus of VBA code.  That’s because Access is primarily an application development tool, and it’s hard to develop an application without doing the kinds of things that VBA provides.

Therefore, wouldn’t it be true that VBA is central to Access, in a way that does not apply to any other members of the Office family?

Access 2010 is coming with significant enhancements to macros (Access macros, that is), associated with enabling the new web application functionality.  But nobody is pretending that these macros, cool though they are, will be a replacement for VBA.

So for people (like me) who plan to continue using Access to develop serious desktop applications, the antennae will always be sensitively poised to detect any hint of change in the level of Microsoft’s support for VBA.

At the moment, I think it’s still looking good!

Technorati Tags: Microsoft+Access,VBA,application+development

What is SharePoint

… from the Access developer’s point of view?

We’re moving towards the release of SharePoint 2010, and with it a new stage in the life cycle of Microsoft Access.

In a nutshell… we will be able to build a database  application, right in Access itself, using tools entirely native to Access.  And then we will be able to publish that application to a SharePoint site, and use the application in a browser.

This is causing some Access developers to start to look at SharePoint in a new way.  And it is causing some SharePoint people to look at Access in a new way.

Perspective

I might write some more about this in future posts.  But for now, let’s just say 2 things.

1.  From the Access angle, here's an absolutely critical point to remember…  The new web application functionality is additional.  Nothing is being removed or replaced.  If you don't have a use for this new stuff, then you don't have to use it, and you can just ignore it.

Desktop database solutions are not going away in a hurry.  VBA continues to be enhanced too, and VBA is not going away any time soon.  We will still be able to build good database applications using Access, just like we have always done.

2.  From the SharePoint angle, Access Services is a tiny corner of a huge product, and most SharePoint developers and users will probably blithely proceed with their work, totally oblivious to our existence.

Facets of SharePoint

Because SharePoint is such a big pool, it can be a bit tricky to grab hold of its meaning, depending on your perspective.

In a video from the recent SharePoint Conference in Las Vegas, you can hear Microsoft’s Steve Ballmer say that SharePoint is:

• a collaboration system
• a business insight system
• a social networking system
• workflow
• document management
• search
• a platform for business intelligence
• a platform for a whole big set of scenrios
• kinda magical, in a certain way

In fact, SharePoint is trying to do so much, in a holistic, integrated, comprehensive way, that it is easy to get bamboozled.

As well, it is also becoming more clearly a part of the Office family.

So it seems to me that one’s understanding of SharePoint will depend on which particular bits of it we have contact with. Which, unless you are a dedicated SharePoint consultant/developer, is likely to only be a small part of the overall picture.

Keep It Simple

I have referred before to Ishai Sagi’s book, and quoted his opening sentence: “SharePoint is a platform that allows users to build websites.”

In the talk about SharePoint, this is a simple truth that is often left out.  We come back to the baseline that SharePoint is a platform for building websites.

Websites for many different purposes. The tools provided by SharePoint to allow those sites to be used for document management, and team collaboration, and social networking, and all the other points mentioned by Steve Ballmer, are impressive.

Enter Access

Well, as I understand it, from SharePoint's point of view, an Access web application is just another website - one of a number of different types of sites it supports.

In the same talk, Steve Ballmer listed Access Services (along with SharePoint Designer, and InfoPath Form Services), under the heading of “Composites”.  So maybe we will start calling ourselves compositors rather than developers. :)

But here’s the key thing… Access 2010 is coming with some unbelievable new functionality, specifically to allow database applications to be run in SharePoint sites.  And SharePoint 2010 is coming with some fantastic new functionality, specifically to allow Access web applications to be supported and integrated.

The aspect of Access Services is just one corner of the pie – a corner that is presently getting a lot of attention because it's new and surprising, but nevertheless still just a corner of Access's overall place in the world. But if Access developers want to take advantage of the expanded range of applications that we will soon be able to offer, then I really think we will eventually be very happy to have SharePoint as one of our closest buddies.

Technorati Tags: Microsoft+Access,Access+2010,SharePoint,Software+development