Use Microsoft Access to send and receive SMS messages
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 (
CREATE UNIQUE INDEX MessageOutNDX ON MessageOut (Id);
CREATE INDEX IDX_IsRead ON MessageOut (IsRead);
CREATE TABLE MessageIn (
CREATE UNIQUE INDEX MessageInNDX ON MessageIn (Id);
CREATE TABLE MessageLog (
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:
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;
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.
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.