Updated: Getting the table structure and links from ACT! by Sage with Visio 2007

For some advanced reporting functions, you may want to figure out how all the tables in an ACT! database link together. Here is a quite powerful method to do that.

Note: This requires the ACTReader utility, which is now included with ACT! by Sage Premium 2009 11.1. If you have an earlier Premium version of ACT! (2005-2009), the Actreader is now downloadable from the ACT! Knowledgebase Article 22989. I do not think it will work with non-Premium versions.

If you have ACT! 2009, you should start by reading ACTDataDictionaryDocument.rtf. This is usually in the folder: “C:\Program Files\ACT\Act for Windows”. This will explain the content of the tables and fields.

You must be on the machine hosting the ACT! database (the server)

  1. First you need to use the ActReader.exe utility to set the password:
    1. Open Windows Explorer and browse to the ACT! program directory (usually: “C:\Program Files\ACT\Act for Windows”. If using ACT! Premium for Web, then replace "Act for Windows" with "Act for Web" in the path.
    2. Locate the file ActReader.exe and double-click on it.
    3. You will get an ACT! Reader dialog box with a current password and the choice to change the password. If you do not know the current password, then create a new one and click Reset.
      ActReader.exeYou will use this password to set up the ODBC connection with ACT! Reader.
  2. Create an ODBC connection for your database as per ACT! Knowledgebase Article 22989
  3. Open Visio 2007 (this may work in previous versions, but I haven’t tried it)
  4. Select: File | New | Software and Database | Database Model Diagram
  5. Select: Database | Reverse Engineer
  6. Select Microsoft SQL Server and the ODBC Database Source you created in step 2
    Visio - Reverse Engineer Wizard 1
  7. Click Next
  8. Connect to the Data Source with User: “ACTREADER” and the password you set in Step 1.
  9. Select the Object Types you want to include
    Visio - Reverse Engineer Wizard 2
  10. Select the Tables and Views you want to include – use the ACTDataDictionaryDocument.rtf to determine which you need.
    Visio - Reverse Engineer Wizard 3
  11. Select to add the shapes
  12. Click Finish.

Depending on your selections and the specs of your machine, this may take a bit of time, but you should then have a nice document with a visual view of the ACT! database structure. This can be exported to various formats available in Visio.

Here is an example image (click on image for a larger version):
ACT! 11.1 Demo tables in Visio2007

If you find this useful, please post a comment to this blog and share how you’re using it.