07 June 2008

Real World Access (38)

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

 Branislav Mihaljev's SMS Messaging

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Post a Comment

Links to this post:

Create a Link

<< Home