Send and receive using MS Access
This article describes how to connect to the Auron Omni database through MS Access. To learn how to import from MS Access, please have a look at Send bulk messages using the Bulk Wizard.
If you need to integrate with a different database (MySQL, PostgreSQL, Oracle, or any other), or if you need a different way to integrate with MS Access, please have a look at the database integration channel.
To send and receive messages through MS Access, we create an MS Access database and set up linked tables.
Access linked to the Auron Omni
This article uses MS Access 2019. However, this functionality has been available in MS Access for a long time, so earlier versions should also work.
1. Create or open a database
For this article we create a new database using the default MS Access settings.
Create a new MS Access database
2. Create a table from an external data source
In the new database, open the External Data tab. Click New Data Source. The data source is another database, typically SQL Server or Azure SQL Database. If you are unsure, SQL Server is usually the correct choice.
Link to external database
Choose to create a linked table.
Select linked table
Next, select an ODBC data source. There is no predefined data source for Auron Omni, so we create one.
Select a datasource
3. Create an ODBC data source
Click New in the Select Data Source dialog.
Create a new ODBC data source
Select SQL Server.
Select SQL Server
Continue and enter the connection details. These must match the settings used in the Auron Omni configuration wizard.
Enter ODBC connection details
Enter more ODBC connection details
Make sure the default database is set to the Auron Omni database.
Enter more ODBC connection details
The default options in the remaining steps are correct.
Finish entering connection details
At the end you can test the ODBC connection. If successful, it looks like this:
ODBC datasource test successfull
After a successful test, the data source is ready to use.
4. Continue setting up the external data connection
Select the data source you just created.
Select the Auron Omni data source
You are asked to authenticate. Credentials cannot be saved at this stage, but this can be configured later.
Auron Omni database credentials
Next, select the tables to link. The most important tables are the insert tables:
These tables combine all message properties into a single structure so you can insert messages with a single insert statement and query them with simple select statements.
You can also choose to save the password here.
Link to the following Auron Omni tables
Access warns about storing the password for the selected tables.
Warning about saving the password
Finally, Access asks which field is the unique identifier for each table. This is always ID.
Select the unique identifier
5. Test the integration
Now that the linked tables are set up in MS Access, it is time to test the integration.
A simple test is to run a query that inserts a message into Auron Omni:
INSERT INTO dbo_InsertSms (StatusID, ToAddress, Body)
VALUES ('SCHEDULED', '+3112345678', 'Hello, World')
This schedules a new SMS message to +3112345678.
Test MS Access integration
This works as expected.
Single query test successful
If you prefer not to use SQL directly, you can also insert records by editing the table directly in Access.
Directly editing the table
Direct edit success
This applies to all supported insert methods in MS Access.
At this point, your Access database is directly connected to the SQL Server database used by Auron Omni.