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!!!

ACT! Reporting using Excel with OLEDB

Sounds confusing for the non-technical user, but once set up, this functionality can be extremely helpful. Furthermore, if you need data formatted/reported on using specific calculations or parameters, automating with macros or Visual Basic can prove to be an incredible time-saver.

The first couple steps of getting OLEDB setup is a bit different depending on which version of Excel you are using, so here’s a quick start for 2003 and 2007 versions.

Once set up, you can reuse the connection, so this does not need to be done every time!

• 2003: Data->Import External Data->Import Data
• 2003: Click “New Source” button
• 2007: Data->From Other Sources->From Data Connection Wizard
• following are common steps for both versions
• Choose Other/Advanced, then Next
• choose Provider as “ACT! OLE DB Provider for Reporting“, click Next
• choose a database file (e.g. *.PAD file), fill in User Name and Password if necessary
• Test Connection if you want, then click OK

You will now be presented with all the available tables. Choose one specific for reporting, or uncheck the “Connect to a specific table” option, which will allow you to go back and pick other tables using the same connection. Personally, if you are setting this up for automation, you will most likely have one table in mind. Click Next to choose a File Name under which to save this connection, add a description, and click Finish.

Now, here’s the fun part. You can leave the settings of the “Import Data” pop-up as-is, and it will import all the data from the table into a “table” grid in Excel, or choose to create a Pivot Table or Chart directly from the new connection, or Edit Query, limiting the subset of data imported into Excel. The table is pretty straight-forward, and the Pivot Table/Chart creation is outside the scope of this blog (but is inevitably is the end result - so if you aren’t familiar with them, get familiar!) I want to finish by going over the Edit Query option, as that will help limit the extra/excessive data you do not need in a report.

In 2003, on the Import Data pop-up, click the Edit Query button. In 2007, on the Import Data pop-up, click Properties, and then the Definition tab. Change the Command Type to SQL, and change the Command Text to:

SELECT "Contact Contact", "Contact Company", "Contact ID/Status"
FROM "ACT11Demo"."dbo"."VRP_CONTACT"

This limits the data to just Contact, Company, and ID/Status info. Of course, from there we can create a Pivot Table if needed, perform any analysis functions, or whatever else is desired.

Hope this help you better utilize the power of Excel accessing and reporting on ACT! data…

p.s. In Excel 2003, if you go to Data->Import External Data->Import Data again, your saved connection will be there. In Excel 2007, it’s Data->Existing Connections that will bring up the saved connections.