SMS from/to Microsoft Access

This article is a detailed guide on sending SMS from/to Microsoft Access. You can simply start to send and receive SMS messages through Ozeki SMS Gateway's Database User by using Microsoft Access database tables. All you have to do is insert or read data rows from the appropriate SQL table by configuring INSERT INTO and SELECT statements for the user. This page shows how to install and prepare the Database User in the browser GUI of Ozeki SMS Gateway. Additionally on the next page you can see how to use Microsoft Access with a few simple screenshots.

This example contains a database that you can download from here: ozeki_database.accdb

What is Microsoft Access?

Microsoft Access is a database creation program that allows for anyone to easily maintain and edit a database. It is suitable for anything from small projects to large businesses and is a very visual program. This makes it great for performing data entry, as you don't need to work with tables and spreadsheets. In this instance we can use a Microsoft Access database, along with Ozeki SMS Gateway to send and receive SMS messages.

What is Ozeki SMS Gateway?

Ozeki SMS Gateway is a powerful SMS Gateway software you can download and install on your Windows or Linux computer, or to your Android mobile phone. It provides an HTTP SMS API, that allows you to connect to it from local or remote programs. The reason why companies use Ozeki SMS Gateway as their first point of access to the mobile network, is because it provides service provider independence. When you use Ozeki, the SMS contact lists and sms data is safe, because Ozeki is installed in their own computer (physical or virtual), and Ozeki provides direct access to the mobile network through wireless connections.

How to send SMS from/to Microsoft Access

In this video tutorial you will learn how to send and receive SMS using Microsoft Access and Ozeki SMS Gateway. First you need to open Ozeki SMS Gateway and create a Microsoft Access messaging connection. Then, you need to download the example project found above, and set it as data source for the connection. Doing this will enable you to send SMS from the SQL tab of your Microsoft Access messaging connection.

Video 1 - How to send SMS from/to Microsoft Access (Video tutorial)

How to receive SMS with Microsoft Access database

This SMS receiving system makes it possible to receive SMS using an MS Access Database. This system consists of an Android phone with the Ozeki SMS Gateway running, that will be used to send messages, a computer with Ozeki SMS Gateway installed, a database server, and your application. You can learn how the procedure works from the following paragraph:

The phone sends the message. The computer with the Ozeki SMS Gateway will store the message in an MS Access Database Server. Your application could get the messages using an SQL Query. (Figure 1)

how to receive sms with microsoft access database
Figure 1 - How to receive SMS with Microsoft Access database

How to send SMS from Microsoft Access database

This SMS sending system makes it possible to send SMS using an MS Access Database Server. This system consists of a phone, with the Ozeki SMS Gateway running on it, that will be used to receive the messages, a computer with Ozeki SMS Gateway installed, a database server, and your application. The procedure works the following way: Your application will send the message to the MS SQL Database Server. It will store your message and the Ozeki SMS Gateway will be able to acquire it using an SQL Query. The message will be sent to the phone after it was acquired. (Figure 2)

how to send sms from microsoft access database
Figure 2 - How to send SMS from Microsoft Access database

Basics of Microsoft Access connection

The Database User can connect to your Access database tables. Keep in mind to create two tables. One for receiving and the other one for sending messages. The table structure should match the one in the MS SQL configuration, which basically consists of an ozekimessagein and ozekimessageout table. Finally, you should write SQL statements to work with these tables. At the bottom of the page, you can find some UPDATE statements to use on the sent message records.

Add Database User

Make sure that your Ozeki SMS Gateway is connected to your GSM modem or IP SMS service provider. For example SMPP, CIMD2 or UCP/EMI are accepted service provider connections. After you are able to manually send and receive SMS messages with SMS Gateway, you can install (Figure 1) and configure a Database User as you can see below.

You should start by selecting 'Users and applications/Add new user or application' from the top menu then search the SQL messaging User and then select the MS Access user. Click the blue 'install' button next to it as you can see on Figure 3.

start installing your sms gateways database user
Figure 3 - Start installing your SMS Gateway's Database User

Configure Database User

Database User is highly advised to connect to the most common databases. Here you can find the list of supported databases. After using a connection string to connect to your database, you can set which SQL statements to use in Ozeki SMS Gateway.

Connect to your Microsoft Access database with the help of an ODBC connection string, which points to your database through your ODBC driver. Please type the path of the database file as seen on Figure 4.

configure the database connection settings
Figure 4 - Configure the database connection settings

Set SQL templates

After setting the connection string, it is time to set the SQL templates. These statements are required to update the outgoing message table, which is most likely called 'ozekimessageout'. The default statements in Ozeki SMS Gateway will not work with Microsoft Access databases. This is why you are highly suggested to check the following figures (on Figure 5) to make some minor changes like moving the apostrophe (' ') surrounding the $id keyword.

sending template updates your message in ozekimessageout
Figure 5 - Sending template UPDATE-s your message in ozekimessageout

Congratulation! You have properly configured Ozeki SMS Gateway to send SMS messages from your Microsoft Access database. You are recommended to test it by INSERT-ing your first SMS message to your outgoing message table. The SMS Gateway's Database User is capable to read these message record and update them as described above from Figure 3. Please, visit to the Microsoft Access 365 or Microsoft Access 2013 tutorial for details.

Summary

The article above will show you how to send and receive SMS messages through Ozeki SMS Gateway's Database User by using Microsoft Access database tables. The Database User can connect to your Access database tables, you just have to create two tables and use the given SQL statements to work with these tables. Manage your messaging system using Microsoft Access is very convenient because all of the messages are stored in a system that was designed for easy lookups.

The information you have obtained is valuable, there are further useful documents on the Ozeki website, do not stop here. It is a good idea to read the article titled SMS from/to Oracle.

Now download the Ozeki SMS Gateway and keep going!

More information