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’.
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.
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.
The external data source should be a link to an external 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.
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.
Scroll down to select the ‘SQL Server’ item.
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.
Make sure to set the default database to the SMS Server database.
The default settings should be OK in the following dialog.
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.
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.
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.
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.
Next MS Access will warn about saving the password for every table that you’ve selected.
Finally MS Access will ask which field is the unique identifier in every selected table. This is always ‘ID’.
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.
And this seems to work perfectly
But if you are not as confident in using SQL directly you will find that just editing the table directly will work too.
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.