Community
Showing results for 
Search instead for 
Do you mean 
Reply

SQL Chart control for ACT! 2010 using OLEDB2

New Member
Posts: 3
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

I need to query the contact, history and opportunity tables to produce reports and charts. I was hoping to use Access and Excel but the way I have done this in the past is through ODBC. I would create a DSN but for that I need un/pw. I am not familiar using OLEDB with Access and Excel.

I was able to bring contact records into excel but contact_history gives me no records. I do this using OLEDB.

Furthermore, even if it did it is not helpful as I need a way to join the tables.

Crystal reports should work but there I need odbc since the OLE DB(ADO) provider ACT! OLE DV Provider for Reporting 2.0 simply does not connect.

 

So, that is where I am - stuck... I've tried some third party tools and obviously they contec to ACT somehow but I can't find what I need thru third parties.

Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

When you use Excel and setup the connection to external data using OLEDB (or ODBC) you select a table, but you can change this option after setting up the connection to identify a SQL statement to join multiple tables.  This is the most direct way to get the data you want from ACT! into Excel.  I recommend using OLEDB2 as it's much easier to write the SQL you need, though you can do the same in the OLEDB provider as well.  

 

If using OLEDB2 the SQL will look something like

 

Select * from contact c1 inner join history on contact_history ch on c1.contactid=ch.contactid inner join history h1 on h1.historyid=ch.historyid  where c1.contact like 'something here to filter the contacts'

New Member
Posts: 3
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

I see. So, where can I get OLDEDB2? What I see in Pop upexcel for example is this:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

thanks

Nickel Super Contributor
Posts: 843
Country: United States

Re: SQL Chart control for ACT! 2010 using OLEDB2

Second item in the list!

Rita Kogstad
RKA Associates
Stamford, CT
Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Once you setup the connection go to connection properties in Excel then change the command type and command text as per attached image.

Bronze Contributor
Posts: 1,329
Country: USA

SQL Chart Control - Clickable

I heard a rumor that the Dashboards in 2011 would be "clickable" - I see that the normal ones are but not ones built with the SQL chart control.  Is there a new control for 2011?  Will there bill clickable links with the chart control?

Patricia Egen
Certified ACT! Consultant, Trainer, Developer
www.egenconsulting.com
(423) 875-2652

Follow us on:


Visit our Popular ACT Wizards Page Wizards Here!

Copper Contributor
Posts: 84
Country: Singapore

Re: SQL Chart control for ACT! 2010 using OLEDB2

Hi Allen , forgive me to jump in here. As I was search thru . I notice that you are very familiar on this topic. May I ask , if I have 2008 ver of act can I use oledb2 to access the activity info ? Or I must upgrade my current ver to 2010 ? As my purpose is to activity info. Hope to heard from you. Paul

Nickel Elite Contributor
Posts: 937
Country: USA

Re: SQL Chart control for ACT! 2010 using OLEDB2

Hi Paul,

 

Only ACT! 2010 or newer (ACT! 2011) have the OLEDB2 provider which exposes activities. 

 

Highlighted
Copper Contributor
Posts: 84
Country: Singapore

Re: SQL Chart control for ACT! 2010 using OLEDB2

Hi Allen , Many thank for reply my question. http://community.act.com/t5/Sage-ACT-Premium/Access-Activity-info-using-OLEDB2-access-2011-ACT/td-p/... I hv try to create a post on above, I am not able to locate the variable for link activity and company , where I go wrong ?. Can you let me know which table have activity info ? Paul