DMS Facets

This blog has moved

2010-03-06T17:25:00.001+13:00

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

Support for Sole Traders

2010-01-25T15:14:00.001+13:00

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:00pm
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)

2010-01-24T15:52:00.001+13:00

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:

An itemized error report for the processor to post from;
An acceptance report from the clearing house for each claim run;
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)

2010-01-10T18:21:00.001+13:00

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

2010-01-08T23:12:00.001+13:00

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

2009-11-15T21:38:00.001+13:00

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

2009-11-11T22:17:00.001+13:00

… 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

Three way street

2009-09-06T23:10:00.001+12:00

Collaboration the hard way

I have recently had one of the more unpleasant experiences of my career in software, and have been reflecting on where did the process go off track.

Analogy

It was pertinent for me to think of a building analogy.

Let’s say you’re a plumber. You’ve been looking after the plumbing needs for a particular client for 10 years.

Out of the blue, a building company gets in touch… “We have built new offices for your customer, and they’re waiting to move in. All we need is the water and sewage – when can you get it done?”

Well, supposing you value your customer, and you want to do your best by them. Also, you know they are normally cost sensitive, so we have to aim for quick and simple if possible. Especially as you are charging out your time anyway at non-profit discount.

On the other hand, this is obviously going to be a tricky job. So you put aside your bamboozlement about why you weren’t involved in the process at an earlier stage, you adopt a task focus, and start drawing up specifications.

In the expectation that a collaborative relationship with the building company will yield greatest efficiency, you arrange for them to show you the new premises. Trouble is, they keep forgetting to bring the key. If there were ever any drawings, they have long since been misplaced, and the guy who can tell you what’s inside the walls is out of town. Only some of the holes you want to put through will they agree to.

You fly out of town at your own expense to kick the project into a higher gear, otherwise it will never get done. The builder says he will do the plumbing himself, but rapidly backtracks when you call his bluff.

When you finally get inside, you find that everything not directly visible from the front door (which is as far as your customers have been allowed to go) is incomplete and in disarray.

And then, they have put a toilet pan in the middle of the board room, that they just know the customer will need, but they can’t let the customer in until all the plumbing is connected. But the only way to get to this board room toilet is by running pipes around the outside of the building and up through the garage floor.

Well, after a while you finally get it piped out, in spite of the difficulties. The builders are still not opening the doors, but somehow you’re getting it in the neck from your customers because they’re still out in the cold. And as for keeping it cheap, well, that idea went down the drain a while ago. Get the picture?

Reasons

All right, here’s the point. Unless I am mistaken, this type of process would not really happen in the building industry. It would not happen in transport, or hospitality, or fishing. But it does happen in the software industry. Not frequently of course, but my experience is not an isolated example. Why?

I don’t claim to have the answers here. But here are a few factors that may be relevant.

First, there is the notorious difficulty of being precise about software specifications in advance. This is partly a result of the fact that software projects tend to be more individual, or in other words less similar to other projects that the developer has done previously, and so harder to put it within a familiar perspective.

Armen Stein discusses some aspects of this problem in this article.

Second, there is a tendency for the clients to not be clear about what they want; or not know what is possible until they actually see it; or not understand what is involved technically in achieving what they seek. The average person would have a much clearer idea of what has to be done to put a door in a wall, than they do about what has to be done to synchronise data between two databases with different structures.

Perhaps even trickier, given the customer’s extensive experience with some aspects of computing as an application user or power user, they can therefore believe they actually do understand what is involved in database application development, whereas in fact they do not.

Third, there are the many competing technologies, some of them with their passionate adherents, and others where the approach to be taken is decided based on the familiarity and skills of the developer.

I am an Access enthusiast. If a job can’t be done using Access, then it’s not a job for me. Whereas I certainly refer on, in cases of Access not being the suitable tool for the job, on the other hand my experience and expertise in Access allows me to recognise scenarios where Access can work well, which others with an incorrect conception of Access will not realise.

When it comes to putting in a toilet, there is probably not a lot of variation from one to another, from the core technology point of view. But for a database to track an organisation’s membership and finances, there are a bunch of wildly diverse ways of setting this up.

I think this factor makes it more difficult for collaboration between independent consultants working on different aspects of the project. And again, the fact that the clients are not necessarily aware of this factor, can exacerbate the problem.

Fourth, there are a lot of unscrupulous charlatans about.

Conclusion

This has been a learning experience for me. In this industry, one needs to be acutely aware of the special factors as discussed above. They need to be allowed for and compensated for.

Trying to shortcut the communication about specifications, done with the best of intentions in the interests of expediency, can backfire.

Finally, remember who the client is. Communicate primarily with the client, and communication with any third party or other provider should go via the client.

Technorati Tags: Microsoft+Access,ISV,software+development

Real World Access (41)

2009-07-08T15:16:00.001+12:00

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

Roger Carlson's Training Registration

Several years ago, Spectrum Health, the largest hospital system in out-state Michigan, changed its Hospital Information Management system.

Since they employ over 12,000 people, scheduling the training for those employees in the dozen or so modules the HIM offered was a challenge. They needed an application to schedule people, courses, and classrooms; and they needed it quickly.

Each course had multiple sessions, each of which could be scheduled in one of many classrooms. Each course could have only one teacher, but multiple teaching assistants assigned to it. Naturally, each course could also have multiple students assigned to it, up to a maximum, which varied based on the classroom to which it was assigned.

The application had to be able to:

create courses and sessions;
assign sessions to classrooms;
assign teachers, assistants, and students to each session.

It had to keep track of:

the seats assigned;
the maximum seating;
who had attended the course;
who had completed the course;
create session rosters;
create certificates of completion.

With Access, I created the database application in under a week's time, and it worked so well, they ended using it for several more projects. All they had to do was copy it, wipe the data, and start over. It was generic enough to be used for most any training initiative.

You can see a working copy here.

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

SharePoint Book Recommendation

2009-07-07T21:39:00.001+12:00

Ishai Sagi’s new book

SharePoint MVP Ishai Sagi has recently published a brand new book about SharePoint 2007.

In a word – masterful. Congratulations, Ishai, on a great contribution!

The author is careful to point out that the book is not targeted to the technical end of the spectrum – the professional SharePoint developers and administrators.

Perfect! There are lots of books, and other resources, that make the assumption that you already know stuff, and jump right in to the intermediate or advanced level.

No, this is for the user. Well, in a sense, the author misses slightly here. He forgot to mention those who don’t necessarily work directly with SharePoint, but work with technologies that can integrate closely with SharePoint. Like (ahem) Access developers, for example.

So really, it’s for people who need a good, solid, overview understanding of SharePoint. Terminology, concepts, functionality. In my experience, there was a big gap for this type of book, and Ishai has filled it superbly.

Here’s the first sentence in Chapter 1:

SharePoint is a platform that allows users to build websites.

That’s what I like… start at the beginning, and move on up from there.

Not that it’s all on the basic level. By the time we get to page 341 we’ve covered some pretty meaty topics.

I have learned heaps from this book, and will continue to do so as I explore it more thoroughly. Just what I needed for my present stage of SharePoint knowledge and skill.

I have no criticisms at all. It is nicely written, in a very readable style. Very well organised. Excellently illustrated. Highly recommended.

Technorati Tags: SharePoint,Microsoft+MVP,Ishai+Sagi,book+review

Doing the Access Stretch

2009-07-06T22:40:00.001+12:00

Pleasing Some of the People Some of the Time

I have heard it said that the best thing about Microsoft Access is that it's part of Microsoft Office, and the worst thing about Access is that it's part of Office.

For one thing, if it wasn't part of Office, it's doubtful it would have survived like it has. On the other hand, it is clearly in a completely different league compared with the other Office products. Most people find it a pretty steep learning curve, and it takes a lot of hard work to achieve proficiency.

In the sense that it is used to “develop” database applications, it is clearly a developer program.

Here's another thing that is a best/worst thing about Access. It provides the ability for beginners to make effective simple applications, as well as providing a very powerful set of tools for professional developers to build complex applications. It simplifies the ability to connect to a wide range of data sources. It provides for a wide range of different application types - desktop / multi-user / hybrid desktop-web / client-server.

There is not a product on the planet that does now, nor ever has, come even close to catering to the needs of such a diverse range of IT workers and such a wide range of application scenarios. And guess what, I reckon this trend is going to become even more so, as Access continues to move forward and keep pace with the wider IT industry.

Is this an advantage? Yes. Is it a disadvantage? Yes.

This is a topic that is close to my heart. Unlike many professional freelance programmers, I do not have a deep IT background. I came to databases from an entirely different background. If it wasn't for the fact that Access provides something for the newbie, I would never have got into it. It was still a steep learning curve, but it was manageable.

I have been full-time Access developer for about 12 years, part-time for a few more years before that. Know what I'm saying? I love my work, and I owe my career to being able to do something with Access without knowing what I was doing.

This type of story is not uncommon in the Access world in general.

Now, that's the trouble... The fact that anyone can have a go, and think they're doing cool stuff, but in reality making a mess, because Access is in Office like Word and Excel and some people therefore assume it is as easy to use - that's the root of the stuff you sometimes hear about Access being regarded as "just a toy".

In many situations, it is a constant battle for Access developers to maintain their dignity and get taken seriously. And for Access to be seen as an appropriate/best tool for jobs, which it really is but the reputation is tarnished. That's the reason I built this webpage, to try and demonstrate that Access does some serious stuff.

But of course, for a computer programme to continue at the cutting edge, it has to change.

With Access 2007, Microsoft made a huge investment. I thank the Access team at Microsoft, for their willingness to take the hard and unpopular decisions in order to keep Access current with the IT industry, create a product that is unique in its scope of functionality, and provide Access with a future.

I see Access 2007 as the first tentative steps in a major movement
towards Access 2010 and beyond, and sometimes tentative steps only make sense in retrospect.

It looks to me that Access is just reaching adolesence. As such, I anticipate that in 10 years, my work will still be centred around Access development.

What a stroke of luck that some of us hitched our wagon to the Access horse, and not one of those other technologies that are slowly fading into oblivion.

We are expecting the Technical Preview (Beta) of Office 2010 sometime soon, and my bet is on an increase in the number of people who will recognise that Microsoft is getting it right.

Technorati Tags: Microsoft+Access,software+development

Sweet as cherry pie

2009-07-05T22:15:00.001+12:00

Bing causing a sensation

People are noticing.

Bing has sort of sneaked out of the closet while nobody was watching. And we find that it has grown up since we last checked.

Somehow, you get a sense that Microsoft are getting it right this time.

We have an internet search engine that has come a long way. I mean a real long way. I am not the only one who is finding it good enough for most normal search purposes.

Plus a couple of very nice features that really set it apart from its competitors – a list of “Related Searches”, and a very nicely done site preview on the search results page.

The Images search also shows some great improvements over the Windows Live Search caterpillar from which this butterfly grew. Again, they have managed to come up with some real points of difference – category options, and very effective ‘similar image’ sub-search.

As a home page, Bing is very attractive. Simple and elegant and uncluttered. But putting that daily picture on there, always a superb and powerful image, is a stroke of pure genius. I have talked to a number of people for whom this has been a significant factor in them choosing Bing.

Maps Search is also looking extremely nice, and seriously rivals anything else out there.

Actually, I can’t think of anything negative to say. Well, apart from the fact that it looks like it will eventually be swamped with advertising – but I guess that is sadly inevitable in this day and age.

Anyway, I am really liking Bing, and wow! I think this is going to make a real impact. I wonder when we will start seeing people using “Bing” as a verb, in the same way that the word “google” is used now.

And then, after all that, I found out today that those of us outside North America, are again the poor cousins. The North American version has even more very cool features that we don’t have (but presumably we will eventually). For example, being able to scroll back to previous days’ default Bing picture. And having a bunch more options available right on the front page.

I really think that Microsoft dishing up a product of this type that is quite as good as it is, has taken a lot of people by surprise. But I have heard that significant numbers of people are already declaring themselves as Bing fans.

Technorati Tags: Bing,internet+search,Microsoft

Non-commercial Re-installations

2009-02-03T07:01:00.001+13:00

A challenge for freelance developers

Maybe it’s just me. But I suspect this is a situation faced at times by others who work at the micro-ISV level.

It has happened to me 3 times in the last week. Customers have got a new computer, and have installed “my” application, without contacting me.

Well, what’s wrong with that, you may ask. This is how I see it…

If you are a freelance developer, building an application for a very specific one-off scenario, you don’t go to the extra effort/expense of providing for the “generality” factor. Like you do if the application will be deployed more widely.

No, you set it up and install it to the computer system it was designed for. You make sure, on that system, that everything related to the application is carefully put in the right place, and nicely lined up.

One of the examples this week was an Access 2000 MDE application, originally deployed with the Access 2000 Runtime, referencing a couple of third-party DLLs, using a set of specifically located TXT files, and requiring a DNS for some core functionality connecting to the MySQL database on their website.

The customer replaced their PCs, with Access 2007 installed. The IT company who supplied and set up their new computers, simply copied the Access database files from the old PCs to the new, and somehow expected them to work. Which they didn’t, of course.

One of the problems with this, is that it suddenly starts to feel like it’s my fault. There seems to be something of an undertone in our communication of “What’s wrong with this dumb database of yours, it was working fine and now suddenly it’s throwing all these errors, we didn’t think you would fail us at a time like this.” Well, I’ve got broad shoulders, and I can handle this one.

More difficult, is the fact that by this stage it has become urgent. They rely on this application. They have to get their invoices out by tomorrow. And of course they are in a distant city. And they don’t know what “unzip” means. And so on… Pressure!

As I said before, this is not the only time. So I can’t help dwelling on the question… Why didn’t somebody think of getting in touch with me before the event? “We’re getting new computers. What do we need to do to make sure our database will work?” It would have been easier and cheaper and nicer.

Technorati Tags: Microsoft+Access,Micro-ISV,software+development

Access Security

2009-02-02T07:33:00.001+13:00

Management by Technology

When planning and designing a Microsoft Access application, the question of the security of the data has to be carefully considered.

Similarly, one is called upon, from time to time, to discuss this aspect in technical forums and newsgroups.

Of course, from the technical angle, there are many aspects that could be discussed – and indeed they have been, many times in many places. Hiding the database window, disabling the bypass key, deploying as MDE/ACCDE, backend in SQL Server, etc. But that is not my main focus here.

Nor do I want to diminish the importance of attending to the question of data security in general. However, I think we have to recognise that how critical a consideration this is, will vary hugely from one industry to another and from one application to another.

If security is highly critical, basically an Access application with a JET/ACE backend database is not the right tool for the job. So? There are millions of database application scenarios where security is not highly critical.

A few weeks ago, I was responding in a newsgroup to a question about preventing access to the backend database. This person had realised that if anyone on his staff was sufficiently savvy, it would be possible for them to use various means to locate and connect to his application’s data.

This person had taken all the reasonable steps, as a developer, to make the backend database file difficult to find and open directly, and to make sure that the ordinary user would only interact with the data via the frontend application.

He asked what further steps he can take to prevent this hypothetical tech-savvy staff member from finding a way in.

I was discussing this over lunch with Lucy Thomson, fellow MVP, and she confirmed my viewpoint. “People keep trying to use technology as a substitute for management!”

Exactly! This is not a technical question. It’s a management question. It’s already patently obvious that you’re not supposed to mess with the database file. It’s like a locked door. It wouldn’t be too hard for someone with a screwdriver or bolt cutters to get in - but they would surely know they were doing something wrong.

Mr/Ms Savvy linking to the data file, or however they are going to do it, is hacking the company system. This is a serious offence. Make sure the staff know it will not be tolerated. Public flogging followed by instant dismissal for first offenders.

Expend your development efforts on building a great application. And apply management solutions to management problems.

Technorati Tags: Microsoft+Access,Micro-ISV,software+development,Access+security

Nana is 100

2009-02-01T07:35:00.001+13:00

On a personal note…

My family and I have returned home from a very special visit to Adelaide, South Australia – my home town.

The purpose for the trip was to help my grandmother Phyllis Schapel to celebrate her 100th birthday, on 10th January.

Of course it was a very family-oriented affair. Wonderful to catch up with immediate family, cousins and extended family members, many of whom I hadn’t seen for many years.

And of course to spend some time with my Nana. She’s a great lady, always know for her sharp wit. Here are a few of her birthday quips I will remember:

Looking across the room at my wife Susan, she turns to me and says…

“She’s too young to have you for a husband!”

(Of course I should have commented back about a certain person being old enough to have me for a grandson, but I wasn’t quick enough – too busy laughing!)

The congratulatory letter from the Queen has been read, the candles are lit, the assembled throng are on the verge of singing Happy Birthday, and Nana says…

“Who paid for this cake?”

Witnessed this interchange…

Nurse: “To what do you owe your long life?”
Nana: “Good, clean living.”
<general laughter>
Nana: “Hey, I didn’t say I was perfect!”

And another…

Somebody: “All set for another 100 years?”
Nana: “Well, I’ll definitely be around for part of it.”

My daughter Hazel has posted here about it. All in all, a pretty memorable experience.

Snip Snip

2009-01-31T08:36:00.001+13:00

I like the Snipping Tool in Vista

Sometimes “quick and easy” is good.

The Snipping Tool which is part of Windows Vista is a screen capture utility.

For me, it’s been a recent discovery. But since that time, I have found myself grabbing it fairly often.

The best thing about it is that it’s “feature-sparse”!

Of course, for some purposes you need screen capture with lots of flexible options about what to capture, when and how, coupled with powerful editing tools, in order to produce presentation-standard outputs.

In such cases the Snipping Tool does not snip the mustard, and for myself Snagit continues to be an essential blade in the quiver.

But I reckon the Snipping Tool designers have got the mix of functionality just about right, for those times where you want a simple no nonsense instant shot.

Here are a few examples of what I have used it for today:

I got an email from a customer asking where does he put a certain type of information into his Access database application. I opened the application at the form my customer is supposed to use, grabbed the Snipping Tool, grabbed a screen shot of the relevant section of the form, highlighted the textbox, and emailed it to him. Total time less than 60 seconds. Totally effective, adequate to the purpose, and certainly quicker than I could have handled it by any other means.
As part of a development task, I had to delete a bunch of tables in a database, and replace them with tables imported from another database. I wanted to make sure the Relationships pertaining to these tables would end up the same, without having to work it out (thinking is so time consuming, don’t you find?) Opened the Relationships window, grabbed the Snipping Tool, grabbed a shot of the relationship diagram, didn’t even save it – just left it sitting there to refer back to, deleted the tables, imported the new ones, and checked the relationships. Nice.
While working on one aspect of a database, I thought of something tangential that I would need to attend to later. Grabbed the Snipping Tool, took a capture of the relevant part of the report (in this case), used my tablet to write a note on it, and whacked in into my “to do” page in OneNote. Couldn’t have done it quicker and better any other way.

And so on. The examples are infinite of course.

So here’s the deal… you get:

A handful of configuration options;
The choice of 4 capture size/shape options;
A pen for freehand drawing/writing, with the choice of 2 nibs, 3 thicknesses, and 16 colours;
A highlighter, in the colour of your choice (as long as you choose yellow);
Save your capture to a choice of 4 formats;
A snappy user interface.

A lot of the time, that’s plenty.

Technorati Tags: screen+capture,snipping+tool,software+development

Real World Access (40)

2009-01-30T16:03:00.001+13:00

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

Jeanette Cunningham's Roads and Bridges

ParkRoads is an MS Access based road and bridge asset management program.

It is designed for use by rangers and other road management staff in Parks Victoria, a national park management agency, in Victoria, Australia.

The program:

records assets on roads, including the road surface;
assesses the condition of the asset;
develops works schedules for maintenance and upgrade works;
calculates repair cost estimates;
prioritises works based on structural condition and cost estimates;
tracks progress of works.

Reports can be generated on screen, in Access templates, or exported to Excel worksheets.

Photographs, technical reports, and assessments can be attached to the database.

The program is designed to be intuitive for non-engineer type road managers, and does not require extensive training courses.

Technorati Tags: Microsoft+MVP,Microsoft+Access,data+management,asset+management,roads

Database discussion

2008-08-04T22:08:00.001+12:00

Access & SQL Server at US TechEd

There is a video recording available of an interesting panel discussion, chaired by Mary Chipman at the US TechEd in June.

It is about considerations for moving a database from JET/ACE to SQL Server.

This is a topic where one can often find considerable confusion. You will see people wanting to choose between Microsoft Access and SQL Server.

To me, it's simple. SQL Server is not an alternative to Access. They're quite different categories of software, and you can't compare them. Access's primary purpose is application development. As far as I am aware, SQL Server can not be regarded as an application development platform. It's database management system software, and as such needs a frontend user interface built using "something else".

In many cases, that "something else" is Access.

Therefore it was excellent to see the panel bringing some good perspective, balance, and clarity to this question.

One of the panelists, Access MVP Armen Stein described Access as a "development environment". A development environment that comes with a free database engine (JET/ACE), which you can use by default, but under certain scenarios you may prefer to use another database engine - SQL Server for example. This is a good way to put it.

Technorati tags: Microsoft+Access, SQL+Server, database+development

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