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.

13 comments:

Darren said...

Hi Mike, Can we get a couple of screen shots of what the Visio repors look like as a result of this procedure.

GL Computing said...

Sure, I'll have a look at posting a couple.

ubsacc2004 said...

Hi MIke, i try to add a new field to tbl_contact but why when i tried to add the new fields it had errors. It cant the column name. But the field security is full access. I can find it at contacts - tools -> define fields. Thanks.

GL Computing said...

how are you trying to add the field?

GL Computing said...

An example of the Visio image has been added to the bottom of the article.

Users of earlier versions should also note that the ActReader utility has now been added to the Knowledge Base linked near the top.

Regards,
Mike Lazarus
ACT! Evangelist

Darren said...

Thanks for putting up the image. Any chance of getting a high resolution image though. It's not possible to see the table names on the current one.

Darren said...

Mike, if you are a 3rd party add-on developer I'm sure it's important to know the table structure and the relationships. What other uses could you see for knowing this information?

GL Computing said...

It's actually not that useful for 3rd party developers as they have to use the SDK. This is most useful for people who want to run detailed reports using Excel or Crystal that can't be generated with other means.

pregen said...

Mike, this doesn't work in Visio 2003 - however, there is a supposed workaround. Visio for Enterprise Architects has a SQL 2005 driver that can be used with Visio 2003. It's the SQL 2005 part that causes a mismatch of drivers in Visio 2003. Thanks for posting this. Totally cool.

GL Computing said...

Pat, just as easy to get the Visio 2007 trial if you don't have it :-)

pregen said...

Mike, this doesn't work in Visio 2003 - however, there is a supposed workaround. Visio for Enterprise Architects has a SQL 2005 driver that can be used with Visio 2003. It's the SQL 2005 part that causes a mismatch of drivers in Visio 2003. Thanks for posting this. Totally cool.

Darren said...

Mike, if you are a 3rd party add-on developer I'm sure it's important to know the table structure and the relationships. What other uses could you see for knowing this information?

ubsacc2004 said...

Hi MIke, i try to add a new field to tbl_contact but why when i tried to add the new fields it had errors. It cant the column name. But the field security is full access. I can find it at contacts - tools -> define fields. Thanks.