Use Microsoft Access to send and receive SMS messages

Microsoft Access

Microsoft Access is not optimized for multi-user configurations and therefore it is not the best database for high-volume messaging. For low to medium message volume however it is a good enough database, especially if you already have your contact data stored in an Access database. Using the Access database connector makes it easy to add SMS functionality to your Microsoft Access or Visual Basic applications.

Use the Microsoft JET OLE DB provider

If you have Microsoft Access installed on the computer where Diafaan SMS Server is running, the OLE DB provider is already installed with it. If not, you might have to download the provider from the Microsoft website.

Create the database

You can use Microsoft Access to create the database file. The tables and fields can be entered manually in the Access user interface or with the SQL commands below. Of course it is also possible to use an existing database, but if you want to be sure you can use all options available in Diafaan SMS Server you may use the SQL commands below.

CREATE TABLE MessageOut (
Id AUTOINCREMENT,
MessageTo TEXT,
MessageFrom TEXT,
MessageText MEMO,
MessageType TEXT,
MessageGuid TEXT,
MessageInfo TEXT,
Gateway TEXT,
UserId TEXT,
UserInfo MEMO,
Priority NUMBER,
Scheduled DATE,
ValidityPeriod NUMBER,
TLVList TEXT,
IsSent YESNO,
IsRead YESNO);

CREATE UNIQUE INDEX MessageOutNDX ON MessageOut (Id);

CREATE INDEX IDX_IsRead ON MessageOut (IsRead);

CREATE TABLE MessageIn (
Id AUTOINCREMENT,
SendTime DATE,
ReceiveTime DATE,
MessageFrom TEXT,
MessageTo TEXT,
SMSC TEXT,
MessageText MEMO,
MessageType TEXT,
MessageParts NUMBER,
MessagePDU MEMO,
Gateway TEXT,
UserId TEXT);

CREATE UNIQUE INDEX MessageInNDX ON MessageIn (Id);

CREATE TABLE MessageLog (
Id AUTOINCREMENT,
SendTime DATETIME,
ReceiveTime DATETIME,
StatusCode NUMBER,
StatusText TEXT,
MessageTo TEXT,
MessageFrom TEXT,
MessageText MEMO,
MessageType TEXT,
MessageId TEXT,
MessageGuid TEXT,
MessageInfo TEXT,
ErrorCode TEXT,
ErrorText TEXT,
Gateway TEXT,
MessageParts NUMBER,
MessagePDU MEMO,
Connector TEXT,
UserId TEXT,
UserInfo MEMO);

CREATE UNIQUE INDEX MessageLogNDX ON MessageLog (Id);

CREATE INDEX IDX_MessageId ON MessageLog (MessageId, SendTime);

Add the SQL Connector

Now you can add the new database to Diafaan SMS Server. First create a new SQL Connector with the connector wizard and select OLE DB database as the database type. On the next page you can provide the connection string, the connection string below is an example of a connection string for the OLE DB provider and can be adapted to suit your database configuration:

Connection string for Access 2010 and older:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:mydatabase.accdb;User Id=myUserId;Password=myPassword;

Alternative connection string for Access 2002 and Access 2003:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydatabase.mdb;User Id=myUserId;Password=myPassword;

Send SMS table

Select the database fields

On the next wizard pages you can select the database tables and fields you want Diafaan SMS Server to use. You can only select the most used fields here. Additional database fields can be selected later on the advanced tab page in the connector properties dialog.

Important note

If you want to keep the messages in the MessageOut table you can select the "Change field: .. to .." option. In Microsoft Access the YES value of a YESNO field is -1. Other values have unpredictable results and may cause repeated sending of the messages. For the database layout in this article this would mean Change field: IsSent to -1.

It is a good idea to change the database flavor option on the same advanced tab page to Microsoft. This will instruct Diafaan SMS Server to use a specific SQL syntax for Microsoft, enhancing performance somewhat.