Fighting with ACT! integration with Sharepoint

There are three ways to extract data form an ACT! database into Sharepoint.
a) Create an OLE DB file (.odc.)
b) Use the ACTReader utility.
c) Use the SA admin utility and break the SQL password, for direct SQL access.

My business is using ACT! 2008 Premium.

My first attempt was to use an OLE DB file. This option is free. The ACTReader comes free in the ACT!2009 Premium version, but we are on 2008. The SA admin utility is $500.

So... I first tried the OLE DB file option.
Here are the instructions from Sage themselves:
http://kb.sagesoftwareonline.com/cgi-bin/sagesoftwareonline.cfg/php/enduser/std_adp.php?p_faqid=14023

This did not work for me. I had my ACT!Server running Excel 2003. Not until I applied every single Microsoft update I could find, could I get that to work. Until then I was getting "Cannot CoCreateInstance" dll errors.

However, Sharepoint designer only likes Office 2007. From my workstation running Office 2007, I again could not build the OLE DB file. To do so, I had to install the full ACT! 2008 client on my machine. I wanted to avoid this, as I do not need ACT! on my machine, but... just copying the ACTOLEDB.dll over to my machine and manually registering it was not enough to make it function. After installing the full ACT! client on my machine running Office 2007, I could then build the ODC file. That worked great. I can view my ACT! data table in Excel.

Now that I knew I could get the data into Excel 2007, I wanted to see if Sharepoint Designer would be able to do the same. I copied the Connection String created in Excel (Data > Connections > Properties > Definitions). It looks something like this:
Provider=ACTOLEDB.1;Data Source=\\MyActServer\actdb\ProductionACTDb.pad;User ID=User Name;Persist Security Info=True

No matter how I tried, this attempt failed. I toyed with NTFS share permissions and the like, and still had no success. I could not get the table to open in Sharepoint designer via OLE DB at all.

So... Consider the OLE DB attempt a failure.

See next post.