Ozeki 10 SMS Gateway
SQL Anywhere SQL Anywhere | OLE DB OLE DB

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.

Start the video

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 simply see how to use Microsoft Access with a few easy screenshots.

Basics of Microsoft Access connection

The Database User can connect to your Access database tables through ODBC or OLE DB. This depends which database connection driver you use. 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 basicly 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 Database User and click the blue 'install' button next to it as you can see on Figure 1.

Figure 1 - Start installing your SMS Gateway's Database User

Please provide a unique name for your Database User (Figure 2). This is required so it will mistaken for other users or applications. Click 'OK' if you are ready. You can look at the already created users and applications on the left side panel to easily select a unique username for the Database User.

Figure 2 - Give a unique username for the Database User

The user is successfully installed, so now it is time to configure it.

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 through your ODBC Driver

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 connection string as seen on Figure 3. Both OLE DB and ODBC drivers are supported, but on this page you can only see ODBC examples. For OLE DB examples, you can check the next page.

Figure 3 - Add the database connection string for your ODBC driver

The connection string shown on Figure 3 is for standard security. Every Access Database has an *.mdb file, which contains a username and password for the database. Every Microsoft Access connection string must contain a *.mdb file. Below you can see different type of connection strings for different kind of database users. The following strings are examples. But it is very important to keep the structure of each string:

Standard security:

Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.mdb;


Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;SystemDB=C:\mydatabase.mdw;


Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd=;

Enable the following admin functions: CREATE USER, CREATE GROUP, ADD USER, GRANT, REVOKE and DEFAULTS. These functions are useful when you would like to CREATE the table structure. Then you can use the following connection string:

Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;ExtendedAnsiSQL=1;

Local identifier:
Local identifier can use non-US date formats or special characters. This string uses the en-gb local identifier which is 2057:

Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Locale Identifier=2057;Uid=Admin;Pwd=;

OLE DB driver connection examples are seen on the following page. Visit it if you use an OLE DB driver.

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 to make some minor changes like moving the apostrophe (' ') surrounding the $id keyword.

Figure 4 - Sending template UPDATE-s your message in ozekimessageout to 'sending'

Figure 5 - Sent tamplate UPDATE-s your message in ozekimessageout to 'sent'

Figure 6 - Not Sent template UPDATE-s your message in ozekimessageout to 'notsent'

Figure 7 - Delivered template UPDATE-s your message in ozekimessageout to 'delivered'

Figure 8 - Undelivered template UPDATE-s your message in ozekimessageout to 'undelivered'

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 4 to 8. Please jump to the Microsoft Access 2013 tutorial for details.

Copyright © 2000- - Ozeki Ltd | info@ozeki.hu
Home > Developers guide > SMS from SQL > Microsoft Access
Legal | Privacy | Terms of use | 5799 | | Login