Fighting with ACT! integration with Sharepoint (part deux)

I decided to try option B:
Use the ACTReader component to make a connection to the ACT! database.

ActReader.exe is a utility that logs into the database and creates a new user in the database. We can use that user to build ODBC connections. The utility is recommended for those who want to build custom Crystal reports. If you are using ACT! then I recommend you open Help while in ACT! and search for 'reader'. The info you need for using ActReader.exe is there.

To test the utility, I backed up my production database, and copied it and renamed it on my local machine. I then upgraded my local ACT! client to ACT! 2009 Premium. (ActReader is free with ACT!2009, not with ACT!2008. So, I had to upgrade or pay the $500.) After install, I told it to run all updates, and to update the database as well. That went fine.

I then tried to find the ACTReader.exe file. It did not exist. I had to shutdown and restart my machine, then re-launch ACT! 2009, and then it indicated there were (even) (more) (newer) updates. I had to again go through backing up the database, downloading the update, running the update, and then updating the database again. Now, ACT! on my machine is at version: 11.1.183.0. (Apparently the actreader.exe does not come with 11.0 out of the box. You must be at version 11.1.x.) Once I was at the latest build, ActReader.exe was in my Program Files\ACT folder.

I launched the ActReader and set a password for the ActReader user. I then built an ODBC connection on my local machine pointing to the database I renamed as Test. I had to specify the ACT instance in the server field of my ODBC connection. Because the test database is on my local machine, I used localhost\ACT7. I then used my ActReader credentials, and voila... I can FINALLY get a successful SQL test connection.

From there, I opened SQL 2005 Management Studio and logged in with same user credentials, again remembering to specify the ACT instance with "\ACT7". Successful.

To make sure all was truly well, I also created a Database Connection in Sharepoint Designer. I used both the wizard, and also Custom connections. Both were successful. I created a rough data view webpart, and... successful.

Finally, I can poke around the ACT! database til my heart's content. Now if I could only script better in SQL...