ENEWS > FALL 2007

ACT! Technology Corner
ACT! Data Access

ACT! provides a number of stock reports, envelopes, and labels which allow users to produce a variety of reports and correspondence. These are based upon report templates which can be further modified or copied to create a customized and specific solution as needed. Template customization is performed by the user right from within the application using the ACT! Report Designer.

In addition to report templates, the ACT! architecture provides four additional data access methods. These methods can be used for reporting or data extraction:

• ACT! OLE/DB Reporting Provider
• ActReader Utility and account
• ActPassword (sa password Utility)
• ACT! SDK (Software Developers Kit)

The objective of providing these additional methods outside of ACT! is to offer the user the most flexibility in creating reports, lists or other documents to suit most any need. This includes data extraction to consolidated databases for rollup and highly-customized reports using such third-party software such as Crystal Reports®.

The four data providers each offer a different approach and data exposure. Below is a summary of each.

OLE/DB Reporting Provider
The ACT! OLE/DB Reporting Provider is a data source provider written by Sage and included with ACT!, ACT! Premium, and ACT! for Web products. The provider is designed to leverage the underlying client-server platform and the power of the Microsoft® SQL Server™ relational database. Highlights include:

• Provides a read-only ACT! logon to the database.
• Returns the same secured data, both record and field-level, as ACT! using database View tables.
• Conforms to the OLE/DB 1.1 specification.
• Enabled by default, but can be disabled via ActDiag (included with ACT!).
• “Flattened” data model presentation with relatively few view tables.
• Dynamic by design – updates automatically via define fields.
• - View column format for explicit field discoverability.
• Currently excludes Activity and Group/Company Membership data.

The provider gives an abstraction and flattening of the relational tables which comprise a logical record in ACT!, including:

• Contacts, Groups, and Companies.
• Notes, History, and Opportunities.
• Characteristic, security, and other supporting tables.

Typical Use: Creating and running third-party reports such as Crystal Reports to return secured data (both record and field level).

ACT! Reader
ActReader is an optional utility which can be purchased from Sage to set a per-machine password. This password is to a SQL Server account, named ActReader, which has read-only access to all Premium databases on the machine for which the password has been set. Highlights include:

• Allows direct read-only SQL Server access to native table and data.
• Allows un-secured data access (i.e. no data is private!).
• Does not require an ACT! logon.
• Supported in Premium databases only.
• Can be used for ODBC or native OLE/DB connections.
• Requires fundamental SQL language and relational database knowledge.

Typical Use: Data extraction and rollup reporting as no data is secured or filtered by security.

ACT! Password
ActPassword is an optional utility which can be purchased from Sage to view the per-machine SQL “sa” password. The “sa” account, meaning System Administrator, is the most absolute and powerful account in the Microsoft SQL Server security model. Upon installation of ACT!, the “sa” password is set to a random alpha-numeric value and is not disclosed to the end users or administrators. This is because much of the data integrity aspect of the application that drives the ACT! platform resides in the database. This is done to maintain the highest degree of data integrity and product quality. Highlights include:

• Full read/write access to the SQL Server instance.
• Highest level of SQL Server instance access.
• Does not allow user to set his/her own sa password.
• Full access to all databases, data and procedural objects.
• Not supported for writing to and/or altering the SQL instance or databases (EULA violation).

Typical Use: Third-party administration software such as online backup of SQL Server databases.

ACT! SDK
The ACT! SDK, or Software Developers Kit, is the preferred and supported method of interacting with the product and database for the purposes of writing data, as well as extending the ACT! product itself. The SDK is built on the Microsoft .Net platform offering a highly-customizable and feature-rich programmatic environment for the developer. The ACT! application itself is written to use this same SDK. Highlights include:

• Developer-oriented approach requiring familiarity with programming concepts.
• Requires an ACT! logon.
• Not “Report Writer-friendly” (i.e. Crystal Reports).
• Exposes built-in functionality for creating custom solutions.
• Provides the ACT! data experience.

Typical Use: To further extend and customize the ACT! application to meet a specific requirement by the user.

The ACT! product, supplemented by the four Data Access Methods described here, provides a robust, flexible and rich set of abilities to meet most all user reporting and data extraction needs.

Bill Blakey
Development, ACT!
Sage Software

Your feedback is very important to us, and we want to know what you want to hear about. Please let us know what content you would like to see in the Technology Corner columns. Send us feedback at architectureCorner@sage.com.