After the ACTReader password has been set, you may create a data connection from a third party program such as Microsoft Excel and use the ACTReader account and password to successfully connect to a database. This enables you to view and generate custom reports in third party programs such as SAP Crystal Reports, or Microsoft Excel. For example, you may connect to the Sage ACT! database from Microsoft Excel, import data, and create a pivot table or chart to work with that data. In this week's tip, I'll show you how to create a data connection outside of Sage ACT!.
Creating a Data Connection
1. Click Start > Control Panel > System and Maintenance > Administrative Tools, and then click Data Sources (ODBC).
2. In the ODBC Data Source Administrator dialog box, from the User DSN tab, click Add.
3. In the Create New Data Source dialog box, select SQL Server Native Client.
4. Click Finish.
5. On the Create New Data Source page, complete the following:
Type a Name and optionally, a Description for the data source connection.
In the Server box, type or select the computer name of the Sage ACT! SQL server to which you want to connect. When typing the name, type “local/ACT7”, or “.\ACT7” (without the double quotes) to connect to the local machine’s SQL Server instance.
6. Click Next. You will need to specify how you will authenticate to the ACT7 instance. Select With SQL Server authentication using a login ID and password entered by the user. Then provide the ACTReader username and password you previously configured. For more information about configuring the ACTReader password, see last week’s tip!
In the Login ID box, type “ACTReader.”
In the Password box, type the password you set using the ActReader utility.
Leave the “Connect to SQL Server to obtain default settings for the additional configuration options.” selected.
7. Click Next.
8. On the next page, select the Change the default database to check box, and then select your Sage ACT! database. Do not change any other default selections. Click Next.
9. On the final page, do not change any default options. Click Finish.
10. To ensure you have configured the authentication properly, click Test Data Source. Otherwise, click OK until you close all dialog boxes and wizard pages.
Making the Connection from Third Party Software
1. Open a third-party reporting/data analysis tool. For example, open Microsoft Excel.
2. Connect to the ODBC data source. In Microsoft Excel 2010, select the Data tab.
3. Click the Existing Connections button. Select the ACT7 connection created earlier, and then click Open.
4. When prompted for your SQL Server Login, verify the Server is set to the ACT7 instance, the LoginID is set to “ACTReader”, then type your ACTReader password and click OK.
5. Select a table from the list to view and work with data from that table. You will be prompted to select how to view the data in Microsoft Excel. After making your selection, click OK.