Aeon can be linked with Microsoft Access so that you can create custom data and statistical reports. This page covers the steps to take to link Aeon with Microsoft Access.
While Microsoft Access reporting is still available to create custom reports in Aeon, you may want to use SQL Server Reporting Services (SSRS) instead. SQL Server Reporting Services (SSRS) is a server-based report generating software system that is used to produce and deliver a variety of interactive and printed reports. SSRS allows you to create your reports and run them locally, or access and run them from the web.
Setting up the SQL Server User Account
This step is for non-hosted sites only. Sites with servers hosted by Atlas Systems need to contactAtlas Support to have an account created for them.
We use Windows Server 2008, SQL Server 2008 and SQL Server Management Studio as the basis for this example.
We refer to the Aeon database by the default name, AeonData, in both text and examples. If your database has been renamed to something other than AeonData, use that name in your configurations in place of the "AeonData" default.
Adding the AeonLink Login
- On the Aeon Server, or on any machine currently connected to the server, open SQL Server Management Studio. Expand the local SQL Server's Security folder.
- Right-click on Logins and click New Login. The Login - New window opens to the General page.
- Type the Loginname in the space provided. Atlas Systems suggests using the name AeonLink, but you may call this login whatever you like.
- Click SQL Server authentication and assign a password to the login name. Be sure to use the same password consistently throughout this setup.
- Check or uncheck the three password enforcement options according to your institution's specific password policy.
- Under Defaultdatabase choose AeonData. If you assigned a different name to your Aeon database, use that name. Click OK.
- The Login - New window closes and you are back in the main SQL Server Management Studio screen.
- Under the local SQL Server's Security | Logins folder, double-click on the new AeonLink icon. A Login Properties window opens. Double-click on the User Mapping page.
- Under Users mappedto this login: check the Map box for the AeonData Database. The user AeonLink now appears under the User column. Click OK to close the window.
Setting Permissions for the AeonLink Login
You will want to give user AeonLink read access to all tables in the AeonData database. You do this by making the login a member of the Role Member db_datareader.
- Under the AeonData database, double click and open the user AeonLink. Go to Databases | AeonData | Security | Users | AeonLink. The AeonLink window opens to the General page.
- Under Database role membership | Role Members check the box next to db_datareader.
- Click OK to close the window.
Testing the AeonLink Permissions
You can test the AeonLink permissions by logging into the SQL Server Management Studio using the AeonLink login and password and running a simple query.
- Log into SQL Server Management Studio using your AeonLink logon and password.
- Click New Query to open the SQL query space.
- Run a simple query by typing the query into the query space and click Execute. Here is an example query to run:
select * from customization
- Results of the query should appear at the bottom of the screen with the message Query executed successfully.
- You should not get any permissions errors. If you do, walk through the above process and check for missteps.
Setting up the ODBC Data Source Link
In Microsoft Windows, the Data Sources (ODBC) application is located in the Start menu under the Administrative Tools folder. It is also found under the Control Panel | Administrative Tools.
- Open the Data Sources (ODBC) application and click on the User DSN tab.
- Click the Add button. You will get a list of available drivers to use. Select SQL Server and click Finish.
- The Create a New Data Source to SQL Server window appears; complete the necessary fields using the table below as a guide. Click Next to cycle through the available settings.
When you reach the end of the settings screens, click Finish.
Field Value Name aeonlink Server Your Aeon server name Authentication Method SQL Server Login ID aeonlink Password aeonlink password Change the Default Database AeonData (if dropdown does not work, just type the database name in)
** Notes: If the connection is not successful and your database resides on an Azure SQL Server, you will want to download a newer version of the Microsoft ODBC Driver for SQL Server and try again.
- An ODBC Microsoft SQL Server Setup window appears. It displays the ODBC data source configuration that will be created.
- Click Test Data Source to make sure the setup works. It should say Tests Completed Successfully.
Click OK and close the OBCD Application.
Setting up Microsoft Access
These instructions were written using Microsoft's MDAC (6) and Access 2007 as a guide. If you have a different version, the process may vary from the steps outlined here.
- Open your Microsoft Access program.
- Choose the option to create a new, blank database.
- Enter a File Name for your database and click Create.
- Click the External Data header and select More | OBDCDatabase from the Import group.
- Select the Source and Destination of the Data. Choose Link to the data source by creating a linked table. Click OK.
- Select the Data Source by clicking the Machine Data Source tab. Click AeonLink and click OK.
- For LoginID, use aeonlink and the password you chose above, and click OK.
- A Link Tables dialog is displayed. To link all of the tables, click Select All and click OK.
- If any tables ask you to Select Unique Record Identifier, click OK. When this is finished, the link is complete.