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)
- First you need to use the ActReader.exe utility to set the password:
- 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.
- Locate the file ActReader.exe and double-click on it.
- 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.
You will use this password to set up the ODBC connection with ACT! Reader.
- Create an ODBC connection for your database as per ACT! Knowledgebase Article 22989
- Open Visio 2007 (this may work in previous versions, but I haven’t tried it)
- Select: File | New | Software and Database | Database Model Diagram
- Select: Database | Reverse Engineer
- Select Microsoft SQL Server and the ODBC Database Source you created in step 2
- Click Next
- Connect to the Data Source with User: “ACTREADER” and the password you set in Step 1.
- Select the Object Types you want to include
- Select the Tables and Views you want to include – use the ACTDataDictionaryDocument.rtf to determine which you need.
- Select to add the shapes
- 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): 
If you find this useful, please post a comment to this blog and share how you’re using it.