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.
13 comments:
Hi Mike, Can we get a couple of screen shots of what the Visio repors look like as a result of this procedure.
Sure, I'll have a look at posting a couple.
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.
how are you trying to add the field?
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
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.
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?
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.
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.
Pat, just as easy to get the Visio 2007 trial if you don't have it :-)
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.
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?
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.
Post a Comment