Community
Showing results for 
Search instead for 
Do you mean 
Reply

Having a problem joining to the Activity table

New Member
Posts: 7
Country: United_Kingdom

Having a problem joining to the Activity table

Hi

 

I am trying to get a list of activity's for a contact in Excel - however I cannot join to the Activity table.

 

So I have used the OLD DB connector in Excel and am able to get all opportunity, company and contact details however as soon as I try and join the activity table I get an error.

 

The following query returns lots of records showing that the CONTACT_ACTIVITY is linked to the CONTACT.

 

select CONTACT.ADDRESS_1, CONTACT.ADDRESS_2, CONTACT.ADDRESS_3, CONTACT.CITY, CONTACT.STATE from CONTACT
inner join CONTACT_ACTIVITY on CONTACT.CONTACTID = CONTACT_ACTIVITY.CONTACTID

 

However as soon as I add the activity table in the query below (have tried ACTIVITY AND TBL_ACTIVITY) it returns an error:

 

select CONTACT.ADDRESS_1, CONTACT.ADDRESS_2, CONTACT.ADDRESS_3, CONTACT.CITY, CONTACT.STATE from CONTACT
inner join CONTACT_ACTIVITY on CONTACT.CONTACTID = CONTACT_ACTIVITY.CONTACTID
inner join ACTIVITY on CONTACT_ACTIVITY.ACTIVITYID = ACTIVITY .ACTIVITYID

 

Any ideas on why I cannot connect. 

 

I did also find that when running ACTDIAG and looking at the db reports that the activity table does not list any of the field names.

 

Many thanks

 

Myles

Administrator
Posts: 999
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Any more information on what the error is?

What version of Act! are you connecting to?
New Member
Posts: 7
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Hi Jon

 

Apologies for the delay been off enjoying the Christmas festivities.

 

The error in Excel is:

 

"The query did not run, or the database table could not be opened.

 

Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again."

 

The database is fine as the previous query runs, however it just cannot open the ACTIVITY table.

 

Connecting to ACT v18.

 

Let me know if there is anything else I can provide to solve.

 

Many thanks

 

Myles

 

 

 

 

Administrator
Posts: 999
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Can I ask what method of OLE DB connection you're using?

It might be useful to take a look at this KB:
http://kb.act.com/app/answers/detail/a_id/25837

The attached document has some sample queries, which I've confirmed DO work.

Including:

SELECT CONTACT.*, ACTIVITY.*
FROM CONTACT_ACTIVITY INNER JOIN
CONTACT ON CONTACT_ACTIVITY.CONTACTID = CONTACT.CONTACTID INNER JOIN
ACTIVITY ON CONTACT_ACTIVITY.ACTIVITYID = ACTIVITY.ACTIVITYID

I can confirm that this query worked, returning the full contents of the both contact and the activity tables.

I did have some issues when trying to narrow down the columns being selected though, including data initialisation errors as you've mentioned.

Take a look at that KB and see if it's of any help.

New Member
Posts: 7
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Hi Jon

 

I am using the ACT OLE DB Provider for Reporting 2.0.

 

I have followed the steps and can get all queries to work except any ones linked to Activities. As mentioned previously I can get it to connect to contact activity but as soon as I join to the activity table it errors. Is there anyway the data connector does not have access to the activity table?

 

We have lots of activities in there but just cannot report on them. 

 

One thing - when I am entering the username and password to connect to the database I am entering my ACT username and password (which is an administrator account on ACT) - could it be that the connector does not give access to this level of user for this table?

 

Kind regards

 

Myles

New Member
Posts: 7
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Hi Jon

 

Sorry also forgot to mention that in ACTDiag I ran the Rebuild OLEDB v2.0 Report Objects and also through ACT ran the Check and Repair and tried after each of these.

 

Kind regards

 

Myles

New Member
Posts: 7
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Hi Jon

 

I think I may have found what could be the problem - in https://community.act.com/t5/Act-Premium/Access-Activity-info-using-OLEDB2-access-2011-ACT/td-p/1017... and the post from Alduet from 30 Nov 2010 it mentions a "defect missing that stored process". 

 

We have been using ACT since 2005 and the database has been in use since then so not sure if the database has this problem. I did connect in Excel to the Demo2016 DB and the query worked. I am currently running a backup which I will restore into a new database and see if that solves the issue.

 

Kind regards

 

Myles

New Member
Posts: 7
Country: United_Kingdom

Re: Having a problem joining to the Activity table

No - the backup and restore did not work - any ideas how I can fix the database so it can connect to the activity table?

Administrator
Posts: 999
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Hmm, you could try running a schema update - this should rebuild stored procedures etc.

Follow the steps on this KB:
http://kb.act.com/app/answers/detail/a_id/38560

Please make sure you backup your data first, just in case.
New Member
Posts: 7
Country: United_Kingdom

Re: Having a problem joining to the Activity table

Hi Jon

 

I have followed the steps to rebuild the stored procedures, however it is still not working. Below is the messgae that I get when I try to connect which does seem to appear that the SP is still missing.

 

Any ideas on what to do next?

 

Kind regards

 

Myles

Excel Error Message.png