Access White Paper

I went to a SharePoint User Group meeting tonight.  A great presentation by visiting British trainer Craig Carpenter.

But I digress... Somehow at these events I always seem to end up mentioning Access to somebody. And, as is so often the case, the "somebody" is apparently surprised to know that Access is being used in the real world.

Well, on this occasion, I was able to refer the person to an article that was fresh in my mind.  It's the Microsoft Office Access Vision White Paper, published by Microsoft.

It's available for download as a Word document.  A couple of years old, but still a good read.

I take the liberty of quoting from the final section of the document:

Microsoft believes strongly in the strategic importance of continued investment in Access and the Access database engine. Access addresses a class of users who would never use Visual Studio and developers who can help empower and support those users. The Access database engine, with its versatile ability to connect to multiple diverse data sources and its powerful client-side query processor, fills a need that is distinct from SQL Server and SQL Server Express. Also, as a tool for creating rich Windows-based forms and reports bound to Windows SharePoint Services data, Access can add significant value to the Web-based capabilities of Windows SharePoint Services. The Web services–based technology that Access uses to connect to Windows SharePoint Services is a promising harbinger of how Access will be able to add value to other emerging service-oriented data sources. For these reasons, Microsoft sees a bright future for Access and will continue to invest strongly in its continued growth.

Nicely put, don't you think?

Technorati tags: Microsoft+Access

Real World Access (39)

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

 Bob Larson's Medical Point of Sale

Providence Health System, in Portland, Oregon, was opening a retail outlet for selling appearance items for cancer patients in one of their hospitals.

They needed a Point of Sale (POS) system in order to handle the retail transactions.  The transactions would include marking which items were covered by insurance and/or cash sales.

They found a system that the Home Medical Equipment division was implementing, but it was not going to be available for 6 months to a year.  So, they needed some program to handle the sales until such time as they could get hooked up to the Enterprise system.

Using Microsoft Access I designed, programmed, and implemented a POS system which is able to handle the cash sales, including credit cards (and tracking the bank confirmation codes), and insurance sales.

The system can submit sales invoices to the business office.  They have all the information required for billing the insurance companies.

At the time I left the company the retail outlet was successfully using the Access program I created and, if it had done the actual insurance billing, they were wishing that they could just keep using the program I created instead of the Enterprise system.

Technorati tags: POS, Microsoft+Access, Microsoft+MVP, software+development

Real World Access (38)

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

 Branislav Mihaljev's SMS Messaging

One of the customers asked me if I can build a database program in Microsoft Access which he will use to send single or mass SMS text messages.

Basically it is a simple task, as SMS messages will be sent through a SMS Gateway, and VB code is already provided by the SMS Gateway provider. With small modification this code can be easily adapted to work in Access.

Wherever the base of a program is a small or mid sized database, Access can be used to build a program around it, so we both agreed to continue with Access.

There were lots of things to consider for this application. The buyer maintains several lists in Excel database-like format, so I needed to write a custom import module to import all these data. As most of the worksheets do not have the same field names, there was a need to give the user the option to "connect" fields from a database table to Excel columns. In some worksheets, the columns are not in the same order, which complicated the programming a bit, but in the end a custom import wizard (reusable for other programs) worked well!

Usually, even for a single user, I split a database into backend and frontend parts. At least there is one advantage: when the backend is completed I can work on the user interface (frontend) without interfering with the user's actual data.

Furthermore this program should maintain different types of message recipients, where each type can belong to one of four groups. In other words, the user has five attributes to define each recipient group. So we have decided to use different backends (one for police, one for fire brigade, one for army, etc). Also there was a need for the option to create a new backend database.

The main screen overall looks simple. It allows the user to select the user type, and using four filters choose a group of recipients. The user can select one or unlimited number of recipients and send the same SMS message to each one.

The SMS screen does not allow more than 160 characters to be entered (it has a counter of the number of typed characters), and it can schedule sending for any date/time in the future.

Sending generic messages is not always user friendly, so we thought we could use "smart tags" in the SMS message. Here is a sample message with "smart tags" in:

Dear <Name> we have a meeting at <Company> tomorrow at 9AM.

When the program generates the SMS messages it will replace <Name> with the real name and <Company> with the name of the company where the recipient works.

As well as being user friendly, "smart tags" appear to be useful in many other ways. "Smart tags" are managed in an option screen, where the user can create a limited number of new "smart tags".

The program checks for the number of SMS Gateway credits available, and if the number drops below a specified number, it generates an e-mail to the SMS Gateway provider asking for additional credits, providing all necessary payment details. Actually the program interacts with the SMS Gateway, pulling all the information needed: credits spent, credits remaining, number of messages sent, etc.

Finally, since we have lists of people in the database, we modified the program to be used as contact management as well, and I have added an option to send e-mails instead of SMS messages. In the end we have a program which can send message information to recipients in different ways.

Imagine if there is a large fire and they need to inform in shortest possible time as many off-duty fire fighters as possible? Using a mobile phone it can take some time, whereas using this Access program they can send one or hundreds of messages in just a couple of seconds.

Technorati tags: Microsoft+Access, Microsoft+MVP, software+development, SMS, text+messaging

Shonky Operator

Beware of Roger Hamilton and XL Results Foundation

I recently received an invitation to a presentation in Wellington by Roger Hamilton from XL Results Foundation.

The topics of the meeting included personal profiling, wealth creation, and business success.  I figure I always have something to learn.

He does this all around the world.

I attended the evening event, and also the follow-up breakfast meeting the following morning.  What I witnessed was quite disturbing - to the extent that I am moved to comment on it, as well as to alert the organisation that promoted it to me.

Roger Hamilton is an interesting and entertaining presenter.  Give credit where credit is due.  He is a clever guy, he shared some good ideas, and I learnt something for sure.

However, his core purpose is not to inform.  The whole thing is a sales process.

Well, in business there's sales and there's sales.  I am not qualified to comment on legalities, but I am surprised that what he is doing does not cross over the boundary into being classified in New Zealand as an illegal pyramid scheme.

Either way, he certainly crosses the boundary of ethics and honesty, very skillfully using psychological techniques to deceive and manipulate.

As a result, I saw a large number of people part with a large amount of money, for a product which is not worth a small fraction of what they paid, in response to vague and misleading information and straight out lies.

Sure, they signed their names voluntarily, and people have a right to make their own impulsive decisions, and to pay money without doing due diligence if they so choose.  So that's all accepted.

But it's still a nasty scam.

In researching about it afterwards, I found a number of web sites and blogs where others have similar concerns.  On one of those, the author asks "How you feel when you hear about people being swindled in the same way - do you let it happen, ignore or speak out?"  I think I will speak out at any opportunity.

Having said that, I need to try and be succinct.  So, what was for sale? Life Membership of Mr Hamilton's little club.  What is the price? $NZ14,500 per person if paid on the spot, or $NZ17,500 if paid in instalments over 12 months.  Is there an opportunity to review any hard information before making a commitment?  No.

Here's a brief overview of the sales process:

• The man with the gift of the gab gets everyone real hyped up.
• Those who "resonate" with the hype have to fill in the application form.
• After that you get told the price.
• After that, you compete to be one of the 5 who will be accepted (except that after the first 5, they keep accepting people anyway - there were 10 who took the plunge when I was there).
• After that, you have to give your credit card details, and sign the form which states no refunds.
• After that, you get some specific details about what you get for the money.

Well, that last point is a tad unfair, I admit.  During the course of the talk, you do get some snippets of information, in very emotive and general terms, about Life Membership.  But certainly not enough to base a rational decision on.

Pretty much what it comes down to is:

• Subscription to a monthly magazine - lots of advertising and the occasional interesting article.
• Potential benefits of social networking with other members. Sort of like a private LinkedIn.
• The opportunity to contribute to charities, in some undefined way.
• An income opportunity by getting a percentage cut of the membership fee of people you refer (this was not mentioned at the meeting, I found out about it later).
• Attendance at a whiz-bang function.
• Access to life/business coaching.

$14,500??  C'mon!

On the face of it, the coaching aspect sounds like the most meaty of the offerings.  But then, if I'm going to get coaching, I need to find a coach I can "click" with.

I have no idea, of course, about any of the coaches within that organisation.  There was one of them at the meeting I attended, and I sure wouldn't have "clicked" with him.

But you would have to assume that the coaches are all people who at some stage have been through the same process I observed.  That means they are not representative.  It means they are people who are able to be swayed by excitement.  It means they are people who have demonstrated their willingness to invest large amounts of cash without proper consideration.  It means they do not have a well-developed ability to detect unethical behaviour.  It means they are not for me.

So that's the value for money aspect.  But in the end, it is primarily the snake-oil tactics that I am upset about.

Something just really got to me about this.  Because I saw it first-hand, I suppose.  It was certainly amazing to see the vulnerability of some folks, and to reinforce my belief in the concept of consumer protection.

Which is simply my point... Watch out!

Technorati tags: scam, wealth+creation, social+networking, consumer+protection

Real World Access (37)

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

 Jack Cowley's Rodeos

The general consensus seems to be that if you are cowboy or cowgirl and want to enter a rodeo, you fill out a form, pay your entry fee, and wait until the appointed day. After everyone has signed up, the participants’ names are drawn from a hat, and if your name was drawn 5th in bull riding, then you are the 5th rider out of the chute.

Nothing could be further from the truth.  It is so complex that it takes hours and hours of manual labor to get people in the order in which they finally get their turn to participate in the rodeo.

Rodeos usually have a ‘main’ event or ‘performance’. Some participants want to be in this event, and some prefer not to be, preferring to be in a later or ‘slack’ event. Or they can select to be in ‘slack’ if they don’t get chosen for the ‘performance’. Or they can choose to be excluded from the rodeo if their number is not drawn for their preference, whether it be ‘performance’ or ‘slack’.

And this is just the beginning! There are team events (2 people), and a person can be in multiple team events and individual events and they can choose to be excluded from all events if they can’t be in their individual events and team events in their preference of ‘performance’ or ‘slack’.

This seems easy enough. But each person and each team is given a randomly generated priority number, and that number determines where they are in the order of the event. A low number and you can go near the end, which is good, as you can see what your competition has done. But if you do not make it into your individual event (‘bad’ priority number), you can choose to be excluded from your team event as well… or not!

Just to add to the confusion, the Barrel Racing event is the reverse, so you want a low number as you go first, not last, so you can set the time to beat.

Basically everyone gets the randomly generated number and then you start trying to put him or her in the events and performance(s) they want to be in. If there are more people signed up for ‘slack’ and not enough for ‘performance’ then a high priority numbered person in ‘slack’ will be moved to ‘performance’.

This has all been done with 3x5 cards and lots of shuffling and moving of cards and reshuffling and more moving, until you end up with a rodeo ready to go. With over a hundred plus people signed up for an ‘average’ rodeo, that is a lot of shuffling and reshuffling!

Now we are doing this with an Access database. Currently a beta version of a simple rodeo can be generated in just a few seconds, saving many hours of manual work deciding who gets to ride and when.

Technorati tags: Microsoft+Access, Microsoft+MVP, Software+Development, Rodeo

Real World Access (36)

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

 Sascha Trowitzsch's Remote Equipment Service

A manufacturer produces machines for industrial application, that fully automate the milling of guitar necks. The sound of the instruments will be optimized this way.

They needed a database to administer all sold machines, their parameters and processes.

I developed an application named "plekBase", based on of Access 2003, which acts as a remote service centre for machine maintenance.

The devices are deployed worldwide.  They produce daily error log reports, and send them by email. "plekBase" receives these emails, records and analyzes them, and notifies technicians about malfunctions and operational deviations.

Based on the data received, the application independently generates a service case if necessary. If a service case is produced, the database reveals all customer's data to the current user technician, who can decide to immediately contact the client via phone with a click of a button (TAPI interface), or send a service enquiry via email.

The database also contains a catalog of all parts of any machine, including pictures, technical drawings, and documentation. This guides the attending technicians with the troubleshooting of specific errors they received.

Technorati tags: Microsoft+Access, Microsoft+MVP, software+development, remote+diagnostics

New Blog - GPG On Access

Hey!  I have noticed that Access MVP George Hepworth (aka Grover Park George) has recently joined the ranks of Access bloggers.  He calls it GPG On Access.

I'll be keeping my eye on his blog. I expect he will be targeting some interesting topics, and he has a great knack for explaining things neatly.

Technorati tags: Microsoft+Access, Microsoft+MVP, blogging, Grover+Park+George

Office 2007 Ribbon Saga

There is some fantastic information available, about the evolution of the Ribbon (Fluent User Interface) for Office 2007.

I first heard about it on Clint's post to the Access Team Blog.

He points to an article by Jensen Harris, Group Program Manager of the Microsoft Office User Experience Team.

And in particular a video of a recent presentation done by Jensen, showing some of the steps in the process of deciding on the Ribbon concept, and putting it together.

(Note: 146 Mb, 70 minutes)

For some reason, this has really caught by imagination, and I have just looked through it a second time.  Even though I had heard about a lot of it before, I have found it totally fascinating to get a glimpse behind the scenes of such a bold process.

It is a very nicely done presentation.

Of course, the Ribbon interface itself has received a mixed reception.  This is not surprising - adjusting to the unfamiliar is always uncomfortable.

Judging by some of the responses to the blog posts mentioned above, there are still a lot of people who are putting up a fight.  Then again, judging by these survey results, they are a minority.

I think there is a lot of good information around, to assist people to learn the Ribbon.  This includes interactive reference guides for finding Office 2003 toolbar and menubar commands in Office 2007 ribbons.  In the case of Access, it is here.

There are some good tools available too, to facilitate customising the Ribbons.  I have used these, and can recommend:
RibbonCustomizer
RibbonCreator

And there are also a number of third-party tools available, which mimic an Office 2003 interface within Office 2007, for those who want to live in the past.

But to suggest that Microsoft might revert to the old style, or provide users with a choice, is away with the fairies.

The Ribbon isn't perfect, and nowhere is that more true than in Access.  I look forward to seeing improvements in due course.

But no matter how much moaning anyone does, what we have now is still a great step forward, from which there will not be any turning back.

My belated congratulations to Jensen Harris and his team at Microsoft, for stepping up to the plate, and doing what had to be done.

Technorati tags: Microsoft+Access, Microsoft+Office, Office+Ribbon, software+development

Real World Access (35)

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

 Graham Seach's Investment Banking

Many of the Access projects I develop are for very large organisations. One in particular was for a multi-national investment bank.

This $3.9M project was to develop a highly secure enterprise system that allowed the bank to meet its regulatory reporting requirements under both the BASEL I & II accords (similar to Sarbanes Oxley).

This project employed a Project Manager, a Systems Architect, 4 Access developers, a SQL Server DBA, 2 Business Analysts, 2 DataStage developers (data migration), 2 MicroStrategy developers (reports), 2 Test Engineers, plus team managers.

The long-term plan for the project was to create an enterprise-wide intranet solution in DotNet. Access 2OO3 was selected as the Version 1.0 frontend technology due to its flexibility, power, and its RAD (rapid application development) capability.

The SQL Server 2005 data store was designed as a data warehouse, so the front end had to be designed to cater for huge numbers of records (in excess of 100 million).

The Access application provided users with the ability to manage and analyse deals and exposures, capital deductions, limits, on/off balance sheets and collateral allocations, and to reconcile the GL (general ledger).

The Access application's user interface is fast, responsive and extremely stable, having been designed using the principles of human-computer interaction (HCI). The UI design focus is on usability and employs a direct manipulation model.

The system uses techniques not normally found in typical Access databases, for example:

• Extensibility (user customisation)
• Proactive integrity checking (the system actively prevents users from making mistakes rather than just responding to them)
• Screen update delay minimisation techniques
• Screen fading effects
• Graphical tooltips
• Full drag-n-drop facility throughout
• Dynamic list scrolling during drag operations
• Automatic user shutdown/lockout during data load operations
• Ad-hoc environment switching (using XML configuration files)
• User-defined security model (including screen/action permissions) tightly integrated with SQL Server security.

The project was delivered on-time, despite a highly aggressive schedule.

The intranet version has been delayed because the Access version works so well that it has quickly become the line-of-business application. Management now see no reason to replace Access.

Technorati tags: Microsoft+Access, Microsoft+MVP, software+development, investment+banking

Kids and the Internet

As a parent, as well an IT professional, I am very interested in the topic of safe computing practices for children.

A few days ago, a guy I know pointed out this article.

It caught my attention, especially since I have been managing a blog for some of the kids at my daughter's school, during their summer holidays.

Well, I found this article, and the website behind it, to be deficient.

The site is aimed towards teenagers.

It does not mention the appropriate role for parental guidance.

It does not provide them with an opportunity to evaluate or discuss the ideas presented.

It does not provide any justification for the claims and opinions presented.

To say stuff like "pose a risk to your privacy or even your personal safety" without explanation or example is not informing, it's scare-mongering.

The best threat they can come up with is the one about future employment prospects.  Give me a break!  It improves their future employment prospects.  It will help them to screen potential employers.  Any potential employer who is going to take into account what you posted to a blog when you were a kid, you don't want to work for, so there, you save yourself a wasted interview.  Doesn't the calibre of our kids ensure they will have a plethora of employment/business opportunities through life, without needing to suck up to bad employers?

On the other hand, if they post good and appropriate stuff online, there's no risk anyway.  And the way to increase that probability is to introduce them at any opportunity, within a very tight and safe environment (such as the one I provided with the blog), and teach them how to do it well.  Isn't that part of being a parent - to guide them as they learn (as they must) to distinguish what is fun from what is dangerous?

Anyway, listen, by the time our kids are old enough to get jobs, there won't be any such thing as the Internet - well, nothing like what we know it today anyway.

So let's dispense to where it belongs, this ridiculous idea that kids posting online will put their futures at risk.

So, what are the risks?  None mentioned on that website.

One possibility is that personal information will be provided, that will assist:
- a deranged non-custodial parent embroiled in a custody battle to kidnap the child
- a paedophile to conduct a targetted pre-meditated attack on the child

Do these things happen?  Yes.  Often enough to make good copy for the Women's Weekly.  But I couldn't agree that we should curtail life in case something bad happens.  In the final analysis, an extremely low risk here, and once again, the risk dramatically reduced by good parental guidance (which does not mean over-protection).

And of course, for those whose personal circumstances are such that they have to take extraordinary steps to stay incognito, well, they have to take extraordinary steps.

Another risk area is commercial exploitation, i.e. bombardment with advertising, spam, and other unsolicited marketing, because of personal information revealed.

Another risk is infecting your computer with malware via access to malicious websites.

Another risk is revealing information such as passwords, bank account numbers, etc, which can be exploited.

So yes, there are risks. Does this mean don't play?  No. It means learn to manage and minimise the risk.  It is not too soon for our kids to see the adults in their lives, their parents and teachers, managing and minimising the risk.

I like what Kimberly Moritz says:

As an adult, I have no problem discriminating, considering the source, looking at the possible bias. I have no problem avoiding the million and one websites out there that focus on nonsense.

I don't think blocking access to the web is going to teach our kids how to do those things. I'm certain that opening it up completely to students who are still developing their good sense and judgment isn't the answer either.

Good parents pay attention to what their kids are doing on-line, just like they pay attention to every other aspect of their lives.

I also like what Mary McCallum says:

Cue the 'terrorist' parent who trusts his/her children but also expects the worst and makes sure s/he is knowledgeable and curious about the things children are into, is tolerant, is non-judgemental when there are 'hiccups', and is always ready and willing to dive in unexpectedly to check on the children and their activities AT A MOMENT'S NOTICE!!

This discussion would not be complete without mention of some software!

Have a look at PC TimeWatch. It is an excellent, easy to use tool for managing computer use. It is probably more applicable for older children, who are starting to have independent access to a computer.

I have noticed that Windows Live OneCare includes a monitoring module which looks interesting.

And I understand that Windows Vista includes parental control and content managing tools, which I confess I have not checked out, but their existence is certainly indicative of a trend.

I expect tools like this will become more available and more powerful in the near future.

Technorati tags: family+safe+computing, parenting, internet+privacy, children+and+internet