Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
16 Oct 2007CPOL4 min read 82.8K   859   65   7
Setting up an SMS gateway to conduct an SMS survey and storing the survey results in an MS Access database, from which you can generate/print out reports.

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.

Screenshot - system1.gif

Figure 1

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

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.

Screenshot - access.gif

Figure 2

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.

Screenshot - dsn.gif

Figure 3

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

SQL
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'.

Screenshot - keyword.gif

Screenshot - keyword2.gif

Figure 4

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
Singapore Singapore
I'm a freelancer with more than 8 years of experience, specializing in wireless application development.

Comments and Discussions

 
Questionpak soft sms gateway Pin
jimisd_axily8-Dec-12 17:29
jimisd_axily8-Dec-12 17:29 
QuestionSMS from MS Access Pin
Berti Vogs29-Jun-12 0:34
Berti Vogs29-Jun-12 0:34 
NewsMobile Marketing agency Pin
Sara Williams3-Mar-11 1:13
Sara Williams3-Mar-11 1:13 
TxtImpact has announced the launch of Mobile site builder an Add-on application integrated with mobile marketing campaigns.TxtImpact MobileSite Builder lets companies create branded mobile websites and Integrate Mobile web in your Text mobile marketing campaigns for richer user experience.<a href="http://www.txtimpact.com">http://www.txtimpact.com</a>[<a href="http://www.txtimpact.com" target="_blank" title="New Window">^</a>]
QuestionCan you supply the source code for this project? Pin
Member 400813110-Jan-09 12:18
Member 400813110-Jan-09 12:18 
QuestionHow can creat a Virtual Com port Pin
Member 26646231-Mar-08 8:06
Member 26646231-Mar-08 8:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.