Home

---

An approach to sending text messages from within an Access application

 

Concept

 

In an Access application, I had a functionality requirement for the sending of individualised text messages from within the application, merging data from the database into the text messages.  There are various ways to go about this task.  Here is the solution I ended up with.

 

SMS Service

 

I decided to use the services of an SMS provider.  On the advice of a colleague, a Mobile Devices MVP, I selected Clickatell http://www.clickatell.com/ and I have been very happy with this decision.

 

Clickatell offer a range of APIs to connect to their service, via SMTP, HTTP, FTP, etc.  After weighing up the pros and cons, within the framework of my specific needs, I decided on the FTP avenue.  In a nutshell, this means that the data is structured to meet Clickatell’s prescribed specifications, transmitted as a text file to their FTP server, and from there it is automatically parsed into the text messages and sent.

 

This service requires the pre-purchase of text message credits.  In my case, I charge these costs back to the users of my applications.

 

FTP

 

The FTP process in this instance is relatively simple.  I use more complex FTP functionality in other contexts, and have come to value the use of a third party ActiveX control, available from http://www.chilkatsoft.com/ChilkatFtp.asp.

 

The chilkatftp.dll file is installed and registered in the C:\Windows\System32\ folder, and a Reference set to it from within the Access application.

 

Example

 

The following describes a specific example of this usage.  I have not tried to make it generic or comprehensive.  It is not my intention to provide here a tutorial on any of the specifics, nor do I expect to have you understand the details of my application! Hopefully you will be able to see how to adapt it to other scenarios.

 

The example involves sending text message notifications about alterations or cancellations of sports fixtures, to people who have registered for such notifications for particular sports/clubs.

 

I have a table named SMSData with a single field Data.  The purpose of this table is to temporarily hold the data for the current transmission of the text message file.

 

I have a table named Alterations, which holds the relevant data about the current batch of match alterations and cancellations.

 

And I have a table named AlterationsSMS which holds the details of the subscribers related to the current alterations.

 

There, that should be enough information to allow you to follow the code below…

 

So this is how I do it

 

Ok, so here’s the process:

1.  Append the required data as records in the SMSData table

2.  Export the data from this table to a text file

3.  Upload the text files via FTP to the SMS service

 

Here is some sample code:

 

-- code -----

 

Option Compare Database

Option Explicit

Dim WithEvents ftpObj As ChilkatFTP

_____________________________

Private Sub SendSMS()

 

    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset

    Dim strSQL As String

    Dim success As Integer

    Dim TextFrom As String

    Dim strData As String

    Dim strFileRoot As String

    Dim strFileName As String

    Dim strFilePath As String

 

    Set dbs = DBEngine(0)(0)

 

    strSQL = "SELECT DISTINCT Competitions.CompetitionID, Alterations.AlterationID, Competitions.Division," & _

" Competitions.[SC], Alterations.GradeNumber, Alterations.Team1, Alterations.Team2," & _

" Alterations.Venue, Alterations.MatchTime, Alterations.AltType" & _

            " FROM Competitions INNER JOIN Alterations ON Competitions.CompetitionID = Alterations.CompetitionID"

    Set rst = dbs.OpenRecordset(strSQL)

    strFilePath = CurrentProject.Path & "\SMS"

    If Len(Dir(strFilePath, vbDirectory)) Then

        ' do nothing

    Else

        MkDir strFilePath

    End If

    If Len(Dir(strFilePath & "\*.*")) Then

        Kill strFilePath & "\*.*"

    End If

    strFileRoot = strFilePath & "\" & Right(DLookup("[WebFolder]", "UserDefaults"), 3)

    Me.SMSProgress.Caption = "Preparing messages..."

    Me.SMSProgress.Visible = True

 

    ' for each match

    With rst

        Do Until .EOF

 

            ' Delete all existing records from T: SMSData

            strSQL = "DELETE * FROM SMSData"

            dbs.Execute strSQL, dbFailOnError

 

            ' Append User & Password to T: SMSData

            strSQL = "INSERT INTO SMSData ( Data )" & _

                    " SELECT SMSFTPUser.Details" & _

                    " FROM SMSFTPUser"

            dbs.Execute strSQL, dbFailOnError

           

            ' Add Sender identity to the file

            strSQL = "INSERT INTO SMSData ( Data )" & _

                    " VALUES ( " & """climsgid:" & LCase(Right(DLookup("[WebFolder]", "UserDefaults"), 3)) & """" & " )"

            dbs.Execute strSQL, dbFailOnError

 

            ' Append Mobile Phone Numbers where subscriber is included

            strSQL = "INSERT INTO SMSData ( Data )" & _

                    " SELECT 'to:' & SubsSMS.CellPhone" & _

                    " FROM SubsSMS INNER JOIN AlterationSMS ON SubsSMS.SubscriberID = AlterationSMS.SubscriberID" & _

                    " WHERE ((AlterationSMS.AlterationID=" & ![AlterationID] & ") AND (AlterationSMS.Include=-1))"

            dbs.Execute strSQL, dbFailOnError

 

            ' append sport from AllSports table

            strSQL = "INSERT INTO SMSData ( Data )" & _

                    " SELECT DISTINCT 'text:' & Choose([PreSuf]+1,[Activity],[Zone]+' ' & [Activity],[Activity] & ' '+[Zone])" & _

                    " FROM AllSports" & _

                    " WHERE AllSports.[SC]='" & ![SC] & "'"

            dbs.Execute strSQL, dbFailOnError

 

            ' append grade or division

            strSQL = "INSERT INTO SMSData ( Data )" & _

                    " VALUES ( " & """text:" & ![Division] & """" & " )"

            dbs.Execute strSQL, dbFailOnError

 

            ' append message based on T: Alterations.Alt Type

            Select Case ![AltType]

                Case 0

                    strData = "text:All matches cancelled."

                Case 1

                    strData = "text:" & ![Team1] & " v " & ![Team2] & " - cancelled."

                Case 2

                    strData = "text:" & ![Team1] & " default to " & ![Team2] & "."

                Case 3

                    strData = "text:" & ![Team2] & " default to " & ![Team1] & "."

                Case 4

                    strData = "text:" & ![Team1] & " v " & ![Team2] & " now at " & ![Venue] & _

                                  " at " & Format(![MatchTime], "h:nnam/pm") & "."

                Case 5

                    If IsNull(![Team2]) Then

                        strData = "text:" & ![Team1] & " now have a bye."

                    Else

                        strData = "text:" & ![Team1] & " v " & ![Team2] & " now at " & ![Venue] & _

                                      " at " & Format(![MatchTime], "h:nnam/pm") & "."

                    End If

            End Select

 

            strSQL = "INSERT INTO SMSData ( Data ) " & _

                    "SELECT " & """" & strData & """" & ";"

            dbs.Execute strSQL, dbFailOnError

           

            ' append SR sig

            strSQL = "INSERT INTO SMSData ( Data )" & _

                    " VALUES ( 'text:---' )"

            dbs.Execute strSQL, dbFailOnError

            strSQL = "INSERT INTO SMSData ( Data )" & _

                    " VALUES ( 'text:via SportsRunner' )"

            dbs.Execute strSQL, dbFailOnError

           

            ' append Organisation sig

            TextFrom = Nz(DLookup("[TextFrom]", "UserDefaults"), "")

            If TextFrom = "" Then

                ' no from field to enter

            Else

                TextFrom = "from:" & TextFrom

                strSQL = "INSERT INTO SMSData ( Data )" & _

                        " VALUES ( '" & TextFrom & "' )"

                dbs.Execute strSQL, dbFailOnError

            End If

 

            ' Export SMSData to text file

            strFileName = strFileRoot & ![SC] & Format(![GradeNumber], "00") & Format(Now, "mmddhhnn") & ".txt"

            DoCmd.TransferText acExportDelim, "AltCancSMS", "SMSData", strFileName, False

 

            .MoveNext

        Loop

        .Close

    End With

 

    Set rst = Nothing

 

    'Connect and send to FTP server

    Me.SMSProgress.Caption = "Connecting to SMS..."

    Me.Repaint

    Set ftpObj = New ChilkatFTP

 

    ftpObj.HostName = "address.of.ftp.server"

    ftpObj.UserName = "MyUserName"

    ftpObj.Password = "MyPassword"

    ftpObj.Passive = 1

 

    success = ftpObj.Connect

    If success = 0 Then

        Me.SMSProgress.Caption = "SMS Connection failed!"

        Me.Repaint

    Else

        Me.SMSProgress.Caption = "Sending messages..."

        Me.Repaint

        success = ftpObj.MPutFiles(strFilePath & "\*.txt")

        If success = 0 Then

            Me.SMSProgress.Caption = "SMS transmission failed!"

        Else

            ' register calls to subscribers

            strSQL = "INSERT INTO SentSMS ( SubscriptionID, SMSTime, Texts )" & _

                    " SELECT AltCancSubs.SubscriptionID, Now() AS Sent, Count(AlterationSMS.AlterationID) AS Texts" & _

                    " FROM AlterationSMS INNER JOIN AltCancSubs ON AlterationSMS.SubscriberID = AltCancSubs.SubscriberID" & _

                    " WHERE ((AlterationSMS.Include) = -1)" & _

                    " GROUP BY AltCancSubs.SubscriptionID, Now();"

            dbs.Execute strSQL, dbFailOnError

           

            Me.SMSProgress.Caption = "Messages sent successfully!"

        End If

        Me.Repaint

    End If

    ftpObj.Disconnect

    Set ftpObj = Nothing

    Me.TimerInterval = 5000

   

    ' clean up

    dbs.Execute "DELETE * FROM AlterationSMS", dbFailOnError

    Me.AltCancDetail.Requery

    Set dbs = Nothing

 

End Sub

 

-- end code -----

 

Here is a sample of the data in the SMSData table.  In this example, it describes a single text message to only one recipient, for only one sports fixture.

 

 

 

Summary

 

FTP transmission of data to a text messaging service is an effective way to send text messages from within an Access application

 

 

 

Steve Schapel