How to conduct an SMS survey using a cell phone connected SMS gateway and MS Access

Introduction

Briefly, this tutorial will teach you how to conduct an SMS survey using a cell phone connected SMS gateway and store the survey results in an MS Access database, from which you can generate/print out reports.

You are assumed to be familiar with the Windows Operating System, MS Access, and the SQL language. As a pre-requisite, you would need to prepare a GSM modem or cell phone with data cable for this solution, and a Windows NT based PC like Windows 2000, Windows 2003, or Windows XP.

The system architecture used for the SMS messaging part of the solution consists of a GSM modem, or a cell phone attached to your PC either by a serial port or USB port, a SQL driven SMS gateway software, and a SQL database such as Microsoft SQL Server (MSSQL). For this solution, we used the VisualGSM Enterprise Server, an off-the-shelf SMS gateway software that comes with a 45 day free use - refer to Figure 1 for the setup components.

Setting up the SMS gateway

The first step to setting up your SMS gateway would be to prepare your MSSQL database - you would need to have administrator access. If you do not have a MSSQL database, you may download SQL Server 2005 Express Edition, a free version of MSSQL here (also see Setup Guide for SQL Server 2005 Express).

After you have setup your MSSQL database, you would need to install an SMS gateway software. For this solution, we use VisualGSM Enterprise SMS software, which you can download a trial version of. Unzip the download package, and after reading the extracted readme file, run the installation file setup.exe. The last part of the installation will run the database setup wizard which allows you to automatically setup your MSSQL database for the SMS gateway software.

After that, with your GSM modem or cell phone (list of compatible cell phones/GSM modems) connected to your PC, proceed to setup the COM ports (usually COM 1 or COM 2 for GSM modems) using the SMS gateway software. If you are using a USB cable, you must install a driver that creates a virtual COM port - you can find the virtual COM port using Windows Device Manager/Modems setup. Most GSM modems use baud rates of 9,600, 19,200, or 115,200 (for USB modems). Note: Besides GSM modems, VisualGSM Enterprise also supports the SMPP protocol of communication.

Start the SMS gateway service. If you encounter any problems, you may find the debug log at the following path: c:\program files\visualtron software corporation\visualgsm\vgsmlog\. If the log shows that the software cannot detect the modem, you may use Hypterminal to test the connection to your modem - refer to this Hyperterminal test guide.

Preparing the MS Access file

• Download Access file - 7.08 KB

For our SMS survey, we will need to setup the SMS gateway to write SMS survey replies to an MS Access database.

Run MS Access, create a new database, e.g., access_demo.mdb, and add a table "survey" with fields "Survey_results" and "Respondent". The field "Survey_results" will store the respondent's reply, while the field "Respondent" will store the mobile number of the respondent. Refer to Figure 2 for a screen capture. Save your database configuration.

After that, create an MS Access "system DSN" for this database, e.g., VGSMDEMO - refer to Figure 3. This system DSN is required for the SMS gateway software to access your MS Access database.

Creating the SQL action to insert SMS into an MS Access file

The SMS gateway software allows you to setup "keyword actions" that can execute a SQL statement based on the SMS survey reply received. The keywords should match the SMS response from your survey users (case insensitive).

We use the following SQL statement (refer to figure 4):

insert into survey (SURVEY_RESULTS,RESPONDENT) values ('YES','{N}')

This setup would create a new record in the table "Survey", set the field "Survey_results" equal to "Yes", and the field "Respondent" equal to {N}. {N} represents the respondent's mobile number. It is a fixed property provided by VisualGSM Enterprise. For the example shown in Figure 4, if the user replies A, VisualGSM will insert a new record into "Survey" in which the field "Survey_results" will carry the value 'YES'.

Useful links

1. SQL Server 2005 Express Edition: http://www.microsoft.com/sql/editions/express/default.mspx
2. Setup guide for SQL Server 2005 Express: http://www.visualtron.com/download/Configuring_MSSQLExpress2005.pdf
3. VisualGSM Enterprise SMS gateway: SMS Gateway
4. How to test your GSM modem/cell phone using Hyperterminal: Hyperterminal SMS