How can we help?

Send and receive using MS Access


This article describes how to connect to the SMS Server database through MS Access. To learn how to import from MS Access please have a look at Send bulk messages using the Bulk Wizard‘.

To achieve send and receive through MS Access we’ll create an MS Access database and setup some ‘linked tables’.

Access linked to the Auron SMS Server

Access linked to the Auron SMS Server

This article uses MS Access 2019, however, this functionality has been available in MS Access for a very long time so if you have an earlier version of MS Access it makes sense to try this as well.

1. Create or open a database

For the sake of this article we’ll create a new database using the default settingsĀ  of MS Access.

Create a new MS Access database

Create a new MS Access database

2. Create a table from an external data source

In the newly created database click on the tab ‘External Data’. In this tab click on the left most button ‘New Data Source’. The data source will be another database and it should either be SQL Server or Azure database. If you are not sure which one to choose then it is most likely SQL Server.

Link to external database

Link to external database

The external data source should be a link to an external table.

Select linked table

Select linked table

Next it will ask to select an ODBC data source. However, unfortunately there is no data source available that will connect to the Auron SMS Server so we will need to create one.

Select a datasource

Select a datasource

3. Create an ODBC Datasource

Creating a new data source is fairly straightforward but it does contain a lot of steps. It starts with clicking ‘new’ on the ‘Select Data Source’ dialog.

Create a new ODBC data source

Create a new ODBC data source

Scroll down to select the ‘SQL Server’ item.

Select SQL Server

Select SQL Server

Click next to confirm your selection and get to the dialog where you can start entering some connection details.

These details will need to match the connection settings where put in the SMS Server configuration wizard.

Enter ODBC connection details

Enter ODBC connection details

Enter more OBDC connection details

Enter more OBDC connection details

Make sure to set the default database to the SMS Server database.

Enter even more OBDC connection details

Enter even more OBDC connection details

The default settings should be OK in the following dialog.

Finish entering connection details

Finish entering connection details

At the end of this you will have the option to test the ODBC connection. If the connection is successful it should look like this.

ODBC datasource test successfull

ODBC datasource test successfull

If the test was successful you will now have a new data source that you can use to connect to from MS Access.

4. Continue setting up the external data source connection

Select the data source that we’ve just created.

Select the Auron SMS Server data source

Select the Auron SMS Server data source

MS Access will now ask how you want to authenticate. In this dialog it’s not possible to remember the credentials but this will come up later.

SMS Server database credentials

SMS Server database credentials

In the next dialog you can select which tables to link to. The most useful tables are the ‘insert’ tables. That is: ‘InsertSms‘, ‘InsertEmail‘ and ‘InsertFile‘.

These tables combine all SMS, E-mail and File properties together in their own tables so you can send a new message with just a single ‘insert’ statement and view all message with very simple ‘select’ statements.

This is also where you can finally opt to save the password.

Link to the following SMS Server tables

Link to the following SMS Server tables

Next MS Access will warn about saving the password for every table that you’ve selected.

Warning about saving the password

Warning about saving the password

Finally MS Access will ask which field is the unique identifier in every selected table. This is always ‘ID’.

Select the unique identifier

Select the unique identifier

5. Test the integration

Now that the linked tables to the Auron SMS Server are setup in the MS Access database it’s time to test if they work.

If you are an experienced database user the simplest test may be running a single query on the MS Access database to see if this results in a message sent in the SMS Server.

The query in this examples is:

INSERT INTO dbo_InsertSms (StatusID, ToAddress, Body) 
VALUES ('SCHEDULED', '+3112345678', 'Hello, World')

This will schedule a new SMS message to send to +3112345678.

Test MS Access integration

Test MS Access integration

And this seems to work perfectly

Single query test successful

Single query test successful

But if you are not as confident in using SQL directly you will find that just editing the table directly will work too.

Directly editing the table

Directly editing the table

Direct edit success

Direct edit success

The same goes for every other method of inserting records using MS Access.

For all intents and purposes you access table is now directly connected to the SQL Server database of the Auron SMS Server.