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