SMS API for Microsoft Access developers
In this step-by-step tutorial
we explain how to create an Access application, which is capable
of sending and receiving SMS messages. In order to use
this tutorial, you need an installed copy of OZEKI MESSAGE SERVER
and MICROSOFT ACCESS.
Step 1 - create an Access database
First you must have a database. You can give it any name. In this example we named it ozeki_ms6_released . In this database you need to create two tables. One of these tables is going to be used for sending SMS messages while the other is going to be used for receiving. It is important that the table used for sending messages is named ozekimessageout and the table used for receiving messages is named ozekimessagein. The columns in the tables should be created as presented on the following screenshot. (Figure 1.) Make sure you set the field size of the msg column to 255. You can add additional columns to both tables, but you cannot change the name of the columns used for sms sending and receiving. You can download this database in Access 2003 format from here: ozeki_ms6_released.mdb. (174kb)
Figure 1 - Specifying of the Ozekimessagin and the Ozekimessageout table
Step 2 - Setting up windows ODBC driver
In order to use Access you need to set up an ODBC connection. Click on Start / Settings /Control Panel / Administrative Tools and click on the "Data Sources" menu item.(Figure 2) .
Figure 2 - Administrative Tools
The following window opens: ODBC Data Source Administrator (Figure 3.) In this window select the System DSN (1) tab. Then click on Add (2).
Figure 3 - System DSN
Figure 4 - Creation of the new data source
This brings up the ODBC Microsoft Access Setup (Figure 5.) form. Set the Data Source Name to MSAccessSystem (1) write some Desciription (2) Click on Select (3). Open the preveously downloaded file, or the file you have created yourself: ozeki_ms6_released.mdb.
Figure 5 - Selecting database
This can be done
by opening the Plugins menu. Select the Installed Plugins menuitem (Figure 6.)
Figure 6 - Installation plugins
When the form is open, select the database plugin to install. (Figure 7 .)
Figure 7 - Installation of the Database plugin
Next click on Configure! (Figure 8.)
Figure 8 - Configuration of the Database plugin
If you want to use the database for sending and receiving messages place checkmarks into the Use database for receiving SMS messages and the Use database for sending SMS messages checkbox (2,3). Important: place a checkmark into the Auto connect on startup checkbox (4) as well, to make sure, when you re start the program the database connection will start up automatically. You can define a database connection by clicking on Build database connection (1) (Figure 9.)
Figure 9 - Configuration in the Connection information tab
Select the Use Connection String radio button , and click on build (Figure 10.).
Figure 10 - Connection string
In the Data Link Properties window select the Provider tab and select the OLE DB Provider for ODBC Drivers list item from the OLE DB Provider list box(1). Click on next (2).(Figure 11.)
Figure 11 - Data link properties
The Connection tab will appear, Click the drop down menu called "Use data source name" and select MSAccessSystem list item (1,2). Remember in the second step we create the Data Source Name: MSAccessSystem (Figure 5). Make sure the User name is Admin (3). Click to Blank password (4) You should test the connection by clicking on Test Connection (5) (Figure 12.)
Figure 12 - Choosing of the data source
If the connection is ok you will see: (Figure 13.)
Figure 13- Connection is successfully
When you setup is correct the next window will appear: At the bottom of the window you can see the log if you click on the statusbar. When the Database connection is established the Database plugin 1 : select id,receiver,msg,operator text will appear in the log.
Figure 14 - Refreshing of the currently installed plugins
Sending sms via MS Access
Figure 15 - Content of the Ozekimessageout table
OZEKI SMS allow you to control the SQL queries used to access the data in your Access database . In these versions, you must modify these queries to make things work. Here is what you should do:
Figure 16 - Configuration in the SQL templates
Notice that the apostrophe around the id field is removed.