DMS Facets

Access White Paper

2008-07-21T23:17:00.001+12:00

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)

2008-07-21T21:57:00.001+12:00

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)

2008-06-07T14:14:00.001+12:00

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

2008-06-02T22:47:00.001+12:00

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)

2008-05-07T11:53:00.001+12:00

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)

2008-03-27T15:06:00.001+13:00

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

2008-03-23T22:43:00.001+13:00

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

2008-03-23T22:24:00.001+13:00

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)

2008-02-25T20:52:00.001+13:00

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

2008-02-24T22:58:00.001+13:00

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

Mouse Parade

2008-02-23T22:06:00.001+13:00

This week has seen the opening of another chapter in my long-term process of caring for the tendons in my hand and arm.

I am referring to my acquisition of a new Microsoft Natural Wireless Laser Mouse 6000 (#4 in the picture).

When I first experienced problems because of mouse damage (RSI, OOS, whatever you call it), the only effective solution I found was the 3M Ergonomic Mouse (#1 in the picture).

Well, originally I got an Anir Vertical Mouse, which was the predecessor of the 3M edition, same basic design.

Over a period of a few years, I think I had a total of 4 of these upright mice. This was a lifesaver. This design has it's limitations, for example even the latest versions do not have wheels. And they seem to wear out pretty quickly. But they force your hand and arm to a position they naturally fall to by default, preventing further damage, which was critically important for me at the time.

Here is a nice picture of how to use it.

Eventually I found that my hand had strengthened, and I decided to give another product a try. Enter the Evoluent Vertical Mouse (#2 in the picture).

Well, I used a couple of these over a couple of years, and I must say they are excellent. It took a while to get used to initially, but after that, it certainly fit the bill. Includes a mouse wheel, and ancillary buttons. The only real problem we had was that occasionally they would just stop, until you re-plug it in to the USB port. Some technical glitch, but it happened on both the units I had.

Then, last year, I got a desktop set. I mainly wanted it for the Microsoft Natural Ergonomic Keyboard (which, by the way, are magnificent beasts). But since it came with a Microsoft Wireless IntelliMouse Explorer (#3 in the picture), I decided to give this a go, given that it had some nice additional features, such as wireless, and a zoom button, and horizontal scrolling - stuff I had never had before.

This mouse (and it's current successor the Wireless Optical Mouse 5000), require you to twist your arm inwards to an unnatural position. But there is enough of a lateral angle on it to provide a significant advantage over a standard horizontal design. And the rest of the body of the unit is so nicely streamlined that it allows a very comfortable grip. So much so, that I have been using it for normal daily use, with only occasional discomfort.

But then, when I heard about the new Natural Mouse, I just had to have one. And so far it's working out very well.

In fact, I think they have come up with a winner this time. This mouse seems to combine the best features of all the ergonomic products mentioned above.

It is a fully featured desktop mouse, with 5 button programmable functionality, scroll and tilt wheel and click the wheel. The wheel action is very satisfyingly positive. The zoom button (unlike the one on the Optical mouse) does not keep getting accidentally activated by coming in contact with the edge of the keyboard.

The lateral angle of the "top" of the mouse is only slightly "off" the natural position. The height of the mouse, at least for my hand size, allows the pressure on the desk to be comfortable. The contours of the base lend themselves to horizontal wrist movement as well as full arm control. The width of the mouse, and the angle of the grips, mean you can control it without needing to squeeze inwards with the thumb, which makes it more comfortable in this regard than the Evoluent.

It did take some getting used to, of course, just because it's different. Something slightly disconcerting at first was that the angle of movement of the mouse did not "line up" for me with what I felt were the horizontal and vertical coordinates. There is no provision to adjust this tracking angle, which was one of the nice features of the Anir/3M mouse.

So, it's recommended. And not just for those who need an ergonomic solution because of existing injury problems - I believe all regular computer users should use safe as possible tools.

Technorati tags: ergonomic+mouse, safe+computing, product+review, Microsoft+mouse

Bill Gates talks about Office

2008-02-23T08:23:00.001+13:00

Thanks to Access MVP Thomas Möller for pointing out this very interesting recent interview with Bill Gates.

We know that Bill has always been very fond of Access, and it is good to see this is still the case.

In this interview, Bill gives some interesting hints about what might be coming in Access 14.

Given that, plus the continued high level of input within Microsoft to the development of future versions of Access, we can be confident of a rosy and exciting future.

We also hear some great comments about where we are going with Microsoft Office in general, and the future of computer technology.

Technorati tags: Bill+Gates, Microsoft+Access, Microsoft+Office, software+development

Up in the air

2008-01-30T00:49:00.001+13:00

Can anyone figure out how they allocate passenger seating on aircraft?

As far as I can tell, it must be a fairly complex data management task, so my hat's off to the person who wrote the algorithm for Air New Zealand.

Tell you why I think so. All sarcasm aside, honestly.

Today I travelled on a plane, I think it was called a Q300, which had 50 seats, as per the diagram.

And you will also see from the diagram which seats were occupied (blue)... it was not a full flight.

I was in seat 6D (shown in red). In the interests of a bit of extra elbow room for me and the person next to me, I thought it would be good to move forward to the vacant seat 5D.

Stewardess Kathy wouldn't allow it. Man, I wish I'd been able to record her reason, but it was obviously a well rehearsed speech.

Basically it amounted to the fact that doing so would interfere with the "trim" of the aircraft during take-off and landing, throwing it dangerously off balance.

Wow! If the plane is that vulnerable to the seating configuration, it's real scary to think the seat allocation software might get it wrong. Eh?

On the other hand, maybe what Kathy told me isn't true.

Maybe she should have said "it is strict company policy for passengers to remain in their allocated seats". Or "my other duties do not allow me the time to evaluate passengers' requests to change seats". Or some such.

Maybe Air New Zealand requires their staff to say ridiculous things to passengers. Who knows. But if so, that's cruel, really, don't you think?

But, as a database developer, I have to consider: If you had to place 18 people within 12 rows of 4 seats plus 2, from the point of view of preventing the plane falling over... how would you do it?

Technorati tags: database+development, seating+allocation, air+safety

Real World Access (34)

2008-01-15T21:02:00.001+13:00

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

Glenn Lloyd's Distribution

Bulk Product Information Management and Reporting

My client, the regional manager for a major multi-national corporation, is responsible for the storage and distribution of a small variety of bulk products throughout a vast geographical area.

The company operates a main storage and handling facility that periodically receives stock via cargo ship and several sub-terminals located at strategic points in the region.

From the main terminal the company distributes bulk products to the sub-terminals and to industrial customers in the terminal’s vicinity. Sub-terminals, in turn, distribute product to industrial customers within their respective territories. The company distributes and re-distributes its products within the region by bulk highway transport.

The company’s global data handling and reporting system focuses on the 'big picture'. As such, it is not suited to meeting more detailed and immediate management information needs at lower levels of the corporation.

My client required comprehensive and timely reporting of ongoing business activity on a terminal by terminal basis. He had initially asked that I develop an Excel based solution for him.

However, it became clear to me, after reviewing the activities and needs he was expressing, that a worksheet approach would be cumbersome to develop and maintain. On the other hand, the data management aspects of my client’s needs indicated that a database oriented solution would be far more suitable in the long run.

After further review and discussion, we decided that Access would be the appropriate vehicle to implement the solution.

The Access application provides a form-based user interface for the collection and update of manually entered data. The interface also provides for the import of Excel data in some cases.

The system includes on demand reporting of product activity, inventory balances, customer activity, summary sales data, overtime useage, and maintenance and safety activity by product, terminal, region and customer.

Technorati tags: Microsoft+Access, Microsoft+MVP, software+development, product+distribution

Real World Access (33)

2008-01-03T21:40:00.001+13:00

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

Dave Hargis's WIA Database

This Access application was written to support the United States Workforce Investment Act. This law provide for training and assistance for people having difficulty obtaining employment.

The law requires specific reporting on these people. The reporting goes from the entity providing services to the state. The state combines reports from all providers and reports to the federal government.

In addition to the reporting requirements, this application provides for gathering extensive demographic information on applicants and tracking the outcome of services provided.

The State of Oregon requires that the reporting be done in FoxPro dbf files. The reporting consisted of transforming the data into the required format, zipping the dbf files, and emailing them to the state.

It is a multi user application with about 50 users, but no more than about 10 would be using the database concurrently.

It consists of 124 tables, 90 forms (some of which are used as subforms), 130 reports, and 310 queries.

Technorati tags: Microsoft+MVP, Microsoft+Access, WIA, compliance+reporting

Real World Access (32)

2007-11-15T03:18:00.001+13:00

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

Paul Baldarelli's Taxis

I have running, in 2 different cities, a comprehensive taxi reservation/dispatch system. Each handles in the range of 500-1,000 calls per day.

It is a Microsoft Access application, with the data in SQL Server.

On the reservation side, calls can be attached to the customer’s telephone number, enabling all their information to pre-populate the reservation form the next time they call. Calls can be taken for now or any point in the future. The user can create recurring calls, such as when someone wants to be picked up for work each weekday. This relieves the user from having to re-enter the same information repeatedly, as the system will automatically create the upcoming reservations (each pickup needs to be a unique reservation). There is also the facility to enter "refused" addresses, so if someone calls from that address the system will refuse the reservation.

The system supports landmarks, enabling users to simply choose a landmark, after which all relevant address information is filled in to the reservation form.

In a sister application, managing limousine reservations, the user can enter a fax number or email address and the system will automatically send the customer a confirmation of the reservation.

On the dispatch side, upcoming reservations are displayed in descending order of the required pick-up time. The user can select how far ahead they want to see calls for. Formatting will highlight "special" calls, such as those for wheelchair capable vehicles or those with particularly time-sensitive needs (airport trips for example). Dispatchers assign calls to specific vehicles, enabling them to track later who got each call. Dispatched calls automatically drop off the listing, but can be looked up easily if the driver calls back looking for info.

There is an audit function running, so management can see who took/changed/dispatched a call, and if they changed anything what it was before and after. They can get summaries of audit activity for a specified date range.

There are of course numerous reports, charts, etc, giving users and management the information necessary to do their jobs. For example:
- call listings by any combination of vehicle/charge account/date range/street/landmark/etc.
- charts for number of calls per day and number of calls by hour for a selected day.

Technorati tags: Microsoft+Access, Microsoft+MVP, Taxi+reservation

A Great Story

2007-09-10T20:59:00.001+12:00

My Father the Writer

This is a bit of a departure from the type of material I usually post. But this is very cool.

Apparently my Dad's sister (my Auntie Mem) has been "encouraging" him for a while to write about this particular subject.

And now he's done it!

Well, I don't suppose Dad will mind me mentioning that he is 80. And as far as I know he has never done much writing. Certainly his experience with computer word processing has been pretty sparse.

So in view of that, I reckon he has done a pretty good job. Do you agree?

It's a story about his Dad (my grandfather), and an account of a holiday when he was a boy.

Click Bull Island Holiday to read a good interesting story...

Real World Access (31)

2007-07-31T16:51:00.001+12:00

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

Brent Spaulding's Processes

Maintenance/Production/Quality Management System

I work for an automobile company in the body weld department of an assembly plant. Our department's end product is the shell (body) of a vehicle all welded up.

To perform this task, we employ the use of several manual and automated welding processes. The automated processes are made up of processing lines. Each line has stations (or cells), each station typically a grouping of robots. The robots primarily perform welding tasks. However we also have robots to apply sealers, and move parts and vehicles from line to line or station to station. All the robots, lines, cells, fixtures, etc are the "Processes" we use to build the product.

I have developed a Microsoft Access application, known as MxP, with a SQL Server data store, to monitor processes' status (Running / Not running), track quality issues the processes create in the product, and keep tabs on process maintenance (both preventative and reactionary). The system is also used to provide production information (start and stop times, production quantities, efficiencies, part traceability, etc.).

Integration with an HMI (Human Machine Interface) allows me to track live information about the processes (build quantity, running status, and part traceability). By using auto logged information, the core aspects of the data are quite accurate due to the removal of much of the user inputs.

Users input data that supports the automatically logged information. In short, MxP will record when and where something happened, our team members record why it happened, and how they fixed it.

On top of the MxP generated data, the system also links with many other applications our facility uses for reporting and traceability purposes.

The system has the capability of filtering and sorting large amounts of information for reporting purposes and research. The research is usually done to answer questions that generally take the form: "Which process had the most stop time due to weld separation over the last 4 months?"; "Which process had the most stop time last week?"; "When was the last time this process was stopped for >20 minutes?"… The list of questions is endless.

Because of the wide array of questions asked of MxP, the reporting system is completely flexible, in that the users have the ability to filter the data on any field for any report.

The formats of the reports range from tabular listings, detailed reports, as well as Pareto and trend line charts. Summary reports have elements of all the different primary formatting options.

The system commonly has ~50 concurrent users, and has had a peak of ~200. The users can access the system from our WAN which extends all across the U.S., plus has limited connectivity to Japan.

The ultimate goal is to have one application that can provide the core information our Production and Maintenance personnel need to track and manage their day.

With the flexibility of linking with many different data sources, coupled with the ease and speed of application development and distribution, Microsoft Access has fulfilled our needs beyond the expectations of many.

Technorati tags: Microsoft+Access, Microsoft+MVP, software+development, production+tracking

Real World Access (30)

2007-07-25T15:55:00.001+12:00

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

Leigh Purvis's Duty

Each year in the UK there are two million 8 page forms (with a possible 232 questions and potentially an additional 99 x 70 questions per form) submitted to the government for Stamp Duty Land Tax (the tax charged when buying property, which is filed by solicitors on behalf of their buyer clients).

This nets the government some £7 billion per annum. But the old legal paper based system can require much to and fro, with late fines for firms unable to correctly submit forms in time (not to mention the embarrassing letter from the Revenue sent to the client informing of the firm's failure!)

In 2005 the government developed their own online submission process – and opened the process to third party software submitters too.

The result is a multi-user, very multi-firm Access 2003 desktop application with Jet backend, which I developed and now maintain for the owner company SDLT.co.uk.

It initially validates the user entered / imported data through the substantial set of business rules. Upon successful local validation, the application submits a post of XML data to the online process of the Government.

Progress is polled repeatedly until a response is received indicating the success of the submission (usually well within a minute). The result is that a user can close out a legal process in minutes which can take weeks to complete on the paper equivalent. If any issues with the submitted data are returned then they are reported to the user to amend their data appropriately and try again right away without penalty.

A sibling Access application downloads the history of use from an online location for reporting and invoicing by the owners.

SDLT.co.uk was the first external vendor application to post submissions to the new service in 2005, and is the biggest third party software vendor handling e-submissions within this process – totalling several thousand successful submissions per month and growing constantly.

Now widely distributed in many law firms across the UK, having developed in Access meant it was possible to create all required functionality and yet be first - while any required technical changes, new standards or user requests can be implemented very quickly and are distributed through an auto-update process. There's a version under development, where the Access application will use data in a SQL Server database.

It has become a regional software award winner.

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

Custom Toolbars 2007

2007-07-22T15:12:00.001+12:00

At the June meeting of the Wellington Office User Group, Peter asked a question about using Custom Toolbars in Office 2007 applications, and how this relates to the Fluent Interface (Ribbon).

I have put together a few pieces of information, as this applies in particular to Access 2007.

Toolbars as toolbars

Whereas VBA will continue to be central to the programming of Access
applications, in general, this does not apply to the Command Bars
(including Menu Bars). You can not make modifications to menu bars and toolbars through the familiar UI, this facility simply doesn't exist.
You have to create/modify them in an earlier version of Access, and then import to your Access 2007 application. Then...
1. Click the Microsoft Office Button in the upper left corner
2. Click the Access Options button in the bottom right corner
3. Click the Current Database category on the Access Options dialog box
4. In the Ribbon and Toolbar Options section select your menu bar name from the Menu Bar combo box
5. Click Ok to save and close the Access Options dialog box.
6. Close your database and then reopen. Viola! No Ribbon, and your
custom Command Bars are shown as such.

Some more information here.

Customising the Ribbon

When it comes to controlling the Ribbon itself in Access 2007 applications, we are basically talking XML, not VBA.

Access MVP Oli Stohr has an excellent article on creating custom ribbons.

On the MSDN site, there is a community article and associated video about adding tabs and controls to the Access 2007 ribbon, which I also found very helpful.

And then, there is Office MVP Patrick Schmidt's RibbonCustomizer add-in, which is a wonderful tool for making many customisation tasks easy.

Technorati tags: Microsoft+Access, software+development, Access+2007

Protecting the Style in MS Word

2007-07-22T13:16:00.001+12:00

At the June meeting of the Wellington Office User Group, Aniela asked a question about how to ensure that a Protected Document in Word 2007 can't have the Style changed.

It appears at first glance that anyone can select a different Style option from the drop-down list, even if the document has been protected and passworded.

Our presenter, Jonathan Stuckey from Microsoft has come back to us with an answer.

Thanks.

Technorati tags: Microsoft+Word, WOUG

Real World Access (29)

2007-06-22T12:00:00.001+12:00

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

Scott Diamond's Art Collection

My company had a lot of artwork within their offices. Some of it very valuable, including an original Andy Warhol. However, we really didn’t have a good inventory of what we had on the walls and in storage. In addition, management wanted a way for new executives to browse the inventory to choose art they might want in their offices.

So I was tasked with creating such an inventory.

While an art appraiser was brought in to value each piece and take pictures, I worked on an Access application. Within 2 weeks I had a working prototype that provided the functionality requested.

The database recorded info about each piece of art: title, size, artist, medium, location, value, purchase date and appraisal date, and also a set of keywords used to categorize each piece so executives could search for what they might want (example, still lifes, landscapes, portraits etc.) Only authorized facilities staff had access to the information about the value of the works.

The application also stored and displayed images of each art work.

The application was completed and put into production late in August 2001. This became very significant since my company occupied the 50th – 54th floors of Two World Trade Center (the south tower). Of course all the artwork (approximately $3 million worth) was lost. But my artwork catalog (which was backed up on tape stored off site), was recovered and used to get full reimbursement from the insurers.

Technorati tags: Microsoft+Access, Microsoft+MVP, Art

Real World Access (28)

2007-06-17T07:56:00.001+12:00

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

John Mishefske's Homeland Security

This application, which I developed in 2004, searches a list of designated parties with whom US persons are prohibited from conducting business transactions.

It periodically automatically retrieves the U.S. Treasury list of Specially Designated Nationals (SDN).

It then provides a search facility and an encrypted, signed, non-refutable report, proving the name was checked on a particular date. This is in compliance with federal legal requirements.

The key to the application was its simplicity - salespeople needed to do a quick check and get results immediately. No waiting for web site or checking printed reports for a particular name, and you could check names without an Internet connection (though you needed one to get updates).

Technorati tags: Microsoft+MVP, Microsoft+Access

Pro Access 2007 : Book Review

2007-05-27T22:01:00.001+12:00

I have been very much enjoying reading Martin WP Reid's new book, Pro Access 2007.

In fact, for me, it is just what I need at this time.

As an Access developer who has acquired a reasonable level of skill and knowledge of earlier versions of Access, I have nevertheless been feeling a little intimidated by the scale of the learning curve ahead, to come to terms with the new wave of changes in my industry. I bet I'm not the only one!

Martin Reid is easy to read. He has a slightly informal, shoot from the hip sort of style. Very personable.

He wades into all the key areas where Access developers have to take notice. The Ribbon UI, what's new in Access 2007, SQL Server 2005 Express, Windows SharePoint Services, to name a few.

It's a relatively short book, less than 400 pages. It isn't an in-depth coverage of everything you would ever want to know. It's not that sort of book, nor does it pretend to be.

On the other hand, it is not a trivial work. It is written for the professional developer, and the content relates accordingly.

What comes through, is the author's passion for Access, and his understanding of Access's continued position at the cutting edge of database application development.

Martin Reid's skill here, is in identifying some of the core concepts, demystifying them by explaining them in straight-forward language, and illustrating them by superb examples. All this at a comfortably manageable pace.

So as you see, I like the book, I learned heaps from it so far, it got me over a hump, and I will no doubt be referring back to it again.

Technorati tags: Microsoft+Access, book+review

Real World Access (27)

2007-05-23T09:37:00.001+12:00

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

Thomas Möller's Mutual Funds

I have developed an application for a bank. With this application, commissions for mutual funds are accounted. The payments of the investment companies are verified and the payments are distributed to various clients.

From the beginning we have used Access as the front-end system. The data was stored in Access (JET) tables.

With time, the business grew. We accounted for more mutual funds and we increased the number of clients. In addition contracts with further societies were secured. These factors led the volume of data to grow fast. This was the reason to migrate the data tables to IBM DB/2. Microsoft Access is still in use as the front-end system.

Technorati tags: Microsoft+Access, software+development, MVP, mutual+funds