22 June 2009

Using ACT! Reader & Microsoft Access to read ACT! Data

Using Excel OLE/DB is a very quick and easy way to get data out of ACT! for reporting or analysis. However, this method is constrained somewhat by the pre-defined views that are available for use. If you need more data, or more detailed data, you might want to look into using the ACT! Reader utility, which opens up ACT! data to be used via ODBC.

Note: ACT! Reader is only available to ACT! Premium users, or those who purchase the utility.

So, the quick and dirty instructions to getting this to work. (I am assuming you have some familiarity with Access and ODBC.)

  • Open ActReader.exe. This utility is used to set a password on the SQL Server instance that the ACTReader "user" will use for an ODBC connection.
  • Open Microsoft Access. We will be using this in our example, but you can use other tools, I prefer Access.
  • Go to External Data>More>ODBC Database
  • Choose Import, or Link tables, then OK
  • Click the New button to Create a New Data Source (File or Machine - does not matter)
  • Choose SQL Server for the driver
  • Create a name, and description for the data source. The Server can be any machine, but if you are running ACT! locally, choose your machine. Also, append ACT7 to the end of the machine name. e.g. MYLAPTOP\ACT7
  • Click the "With SQL Server authentication..." radio button.
  • Login ID is "ActReader", and password is whatever you set using the utility.
  • Change the default database to the ACT! database you would like to work with

After Finishing, you will be presented with all the tables in the ACT! database. You can now use them like any Access table - build a report, application, analysis, etc. For example, I created an application for project and time tracking. Certain ACT! users are tagged with a Yes/No field in ACT! that sets them to an "active" status. Then, in Access, I created a macro to import *only* those contacts with the field set to "Yes", and append them to the master table in Access, which is the cornerstone for the tracking system. This way, I always have the most current contact data in Access, directly from ACT!.

Hope this helps!!!