Aeon Access Reports

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.

Setting up the SQL Server User Account

This step is for non-hosted sites only. Sites with servers hosted by Atlas Systems need to contact Atlas Support to have an account created for them.

Adding the AeonLink Login

1. 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.
2. Right-click on Logins and click New Login. The Login - New window opens to the General page.
3. Type the Loginname in the space provided. Atlas Systems suggests using the name AeonLink, but you may call this login whatever you like.
4. Click SQL Server authentication and assign a password to the login name. Be sure to use the same password consistently throughout this setup.
5. Check or uncheck the three password enforcement options according to your institution's specific password policy.
6. Under Defaultdatabase choose AeonData. If you assigned a different name to your Aeon database, use that name. Click OK.
7. The Login - New window closes and you are back in the main SQL Server Management Studio screen.
8. 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.
9. 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.

1. 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.
2. Under Database role membership | Role Members check the box next to db_datareader.
3. 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.

1. Log into SQL Server Management Studio using your AeonLink logon and password.
2. Click New Query to open the SQL query space.
3. 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

4. Results of the query should appear at the bottom of the screen with the message Query executed successfully.
5. 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

1. Open the Data Sources (ODBC) application and click on the User DSN tab.
2. Click the Add button. You will get a list of available drivers to use. Select SQL Server and click Finish.
3. 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.

4. 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.  

5. An ODBC Microsoft SQL Server Setup window appears. It displays the ODBC data source configuration that will be created.
6. Click Test Data Source to make sure the setup works. It should say Tests Completed Successfully.

7. Click OK and close the OBCD Application.

Setting up Microsoft Access

1. Open your Microsoft Access program.
2. Choose the option to create a new, blank database.
3. Enter a File Name for your database and click Create.
4. Click the External Data header and select More | OBDCDatabase from the Import group.
5. Select the Source and Destination of the Data. Choose Link to the data source by creating a linked table. Click OK.
6. Select the Data Source by clicking the Machine Data Source tab. Click AeonLink and click OK.
7. For LoginID, use aeonlink and the password you chose above, and click OK.
8. A Link Tables dialog is displayed. To link all of the tables, click Select All and click OK.
9. If any tables ask you to Select Unique Record Identifier, click OK. When this is finished, the link is complete.