Community
Showing results for 
Search instead for 
Do you mean 
Reply

Access Activity info using OLEDB2 access 2011 ACT

Accepted Solution Solved
Copper Contributor
Posts: 84
Country: Singapore
Accepted Solution

Access Activity info using OLEDB2 access 2011 ACT

Hi All, I try to use the below command to access activity information , unfortunity i am not able to get any information from ACT database. For testing i have created 2 contact , and 2 opportunity. But i have access more then 10 table , and yet i am not able to get any information, where did i go wrong. ( By the way i manage to use OLEDB2 driver access all table , but inside the table info are all blank , it is i access the wrong table ? Hope some one can advise me. I already read/search the forum , but i am not able to get info.  Paul

 

CONNECT TO [Provider=ACT! OLE DB Provider for Reporting 2.0;Data Source=C:\Documents and Settings\Paul Yeo.TDS\My Documents\ACT\ACT Data\Databases\temp.pad;User ID=Paul Yeo;Persist Security Info=True;Initial Catalog="";Extended Properties="";Location="";Mode=ReadWrite] (XPassword is BMRPGYFPWE);
SQL SELECT *
FROM "COMPANY_HISTORY";

SQL SELECT *
FROM "ACTIVITY_TYPE";


SQL SELECT *
FROM "COMPANY_ACTIVITY";

SQL SELECT *
FROM COMPANY;


SQL SELECT *
FROM "CONTACT_CONTACT";


SQL SELECT *
FROM "COMPANY_CONTACT";

SQL SELECT *
FROM ACTIVITY;

SQL SELECT *
FROM "CONTACT_ACTIVITY";     // error msg loop

SQL SELECT *
FROM "ACT_TABLES";

SQL SELECT *
FROM "GROUP";

SQL SELECT *
FROM ACCESSOR;

SQL SELECT *
FROM "COMPANY_OPPORTUNITY";


Accepted Solutions
Solution
Accepted by topic author paulyeo11
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 84
Country: Singapore

Re: Access Activity info using OLEDB2 access 2011 ACT

Hi Allen, thank you for the code you post to me , i manage to figure out and i create the below code , but i am not able to link the activity for example meeting regarding offer our company product , link to particular contact in the act database., hope you can tell me why i still cannot link the activity with company , where i go wrong ?

 

 

// get the company information. most impt i need to get the company name.

 

SQL SELECT
 "ACCESS_LEVEL", "ADDRESS_1", "ADDRESS_2", "ADDRESS_3",
    "BILLING_ADDRESS_1", "BILLING_ADDRESS_2", "BILLING_ADDRESS_3",
    "BILLING_CITY", "BILLING_COUNTRY", "BILLING_STATE",
    "BILLING_ZIP_CODE", CITY, COMPANY, "COMPANY_DESCRIPTION",
    COMPANYID, COUNTRY, "CREATE_DATE", DIVISION, DIVISIONS
FROM COMPANY;

 

// below get the activity information.

 

SQL SELECT "ACCESSOR_ACTIVITYID", ACCESSORID, ACKNOWLEDGED, "ACTIVITY_TYPE",
    ACTIVITYID, ACTIVITYPRIORITYID, ACTIVITYSERIESAPPLIEDID,
    ACTIVITYTYPEID, ALARMED, "ALL_DAY_BANNER", "BANNER_COLOR",
    "BASE_RECORD", CLEARED, DELETED, DETAILS,
    DURATION, "EDIT_DATE", "END_DATE_TIME", EVENT, "EXTERNAL_ID",REGARDING
FROM ACTIVITY;

 

// below link the above 2 table , using activityid and companyid.

SQL SELECT *
FROM "COMPANY_ACTIVITY";

 

View solution in original post


All Replies
Nickel Elite Contributor
Posts: 937
Country: USA

Re: Access Activity info using OLEDB2 access 2011 ACT

If you are accessing this via code then your provider info below is incorrect:

 

You have: Provider=ACT! OLE DB Provider for Reporting 2.0;

 

You need: Provider=ACTOLEDB2.1

 

Select * from Activity

 

will get you all activity records in the database.

 

I suggest using a .udl file to create your connection then opening the .udl file with notepad to get a properly formed OLEDB  init string.

Copper Contributor
Posts: 84
Country: Singapore

Re: Access Activity info using OLEDB2 access 2011 ACT

Hi Allen,

 

Many thank for your reply on my posts. I manage to change my code to use OLEDB2.1, my code as below :-

 

CONNECT TO [Provider=ACTOLEDB2.1;Data Source=C:\Documents and Settings\Paul Yeo.TDS\My Documents\ACT\ACT Data\Databases\ACT2011Demo.PAD;User ID=chris huffman;Persist Security Info=True;Initial Catalog="";Extended Properties="";Location="";Mode=ReadWrite] (XPassword is YKLAOYEHXJOSDbUGSJMUWXC);

SQL SELECT *
FROM COMPANY;

SQL SELECT *
FROM ACTIVITY;

 

And i get error msg below if i add SQL SELECT* FROM ACTIVITY, ( if with out activity , only read COMPANY table , i don't get any error msg ).

 

SQL error: A .NET Framework error occurred during execution of user-defined routine or aggregate "PRC_CLR_GET_ACTIVITIES":
System.Data.SqlClient.SqlException: Invalid column name 'INVITATION'.
Invalid column name 'IS_INVITATION'.
System.Data.SqlClient.SqlException:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)
   at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Act.Database.ManagedReportProvider.ActivityDataProvider.CreateTempTab
Script line: SELECT *  FROM ACTIVITY
SQL SELECT *
FROM ACTIVITY

 

Hope you can advise me where i go wrong , so i can move forward.

 

Paul

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Access Activity info using OLEDB2 access 2011 ACT

Are you using the demo db? I believe the demo db has a defect missing that stored process. Create a new db and execute against that db- you should be fine.
Copper Contributor
Posts: 84
Country: Singapore

Re: Access Activity info using OLEDB2 access 2011 ACT

Hi Allen ,Thank you for your fast reply.

 

You are right , after i create a new database file , it work fine , no more error. 

 

Now my next question is i am not able get the activity information link with company.

 

I try (1) :-

 

SQL SELECT *
FROM COMPANY;

SQL SELECT *
FROM ACTIVITY;

 

I am not able to get the activity like meeting regarding information link with company.

 

I try (2) :-

 

SQL SELECT *
FROM ACTIVITY;

SQL SELECT *
FROM "COMPANY_ACTIVITY";

My aim is want get activity link with company.

 

All current activity make by my sales staff , i need to know the activity is link to which company ?

 

Pls take note that my data base is created without using companies. meaning each contact have indivdual company name.

 

Hope you can share with me , what i should should do in order link the activity to company ?

 

Paul

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Access Activity info using OLEDB2 access 2011 ACT

The company_activity table contains the foreign keys linking company records and activity records so if you want to get all the activities for a given company the SQL is as such:

 

Select * from activity inner join company_activity on company_activity.activityid=activity.activityid inner join company on company_activity.companyid=company.companyid

 

This will show all activites associated to a company if you want to filter to a specific company then add:

 

where company.company = 'put in a company name'

 

 

Solution
Accepted by topic author paulyeo11
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 84
Country: Singapore

Re: Access Activity info using OLEDB2 access 2011 ACT

Hi Allen, thank you for the code you post to me , i manage to figure out and i create the below code , but i am not able to link the activity for example meeting regarding offer our company product , link to particular contact in the act database., hope you can tell me why i still cannot link the activity with company , where i go wrong ?

 

 

// get the company information. most impt i need to get the company name.

 

SQL SELECT
 "ACCESS_LEVEL", "ADDRESS_1", "ADDRESS_2", "ADDRESS_3",
    "BILLING_ADDRESS_1", "BILLING_ADDRESS_2", "BILLING_ADDRESS_3",
    "BILLING_CITY", "BILLING_COUNTRY", "BILLING_STATE",
    "BILLING_ZIP_CODE", CITY, COMPANY, "COMPANY_DESCRIPTION",
    COMPANYID, COUNTRY, "CREATE_DATE", DIVISION, DIVISIONS
FROM COMPANY;

 

// below get the activity information.

 

SQL SELECT "ACCESSOR_ACTIVITYID", ACCESSORID, ACKNOWLEDGED, "ACTIVITY_TYPE",
    ACTIVITYID, ACTIVITYPRIORITYID, ACTIVITYSERIESAPPLIEDID,
    ACTIVITYTYPEID, ALARMED, "ALL_DAY_BANNER", "BANNER_COLOR",
    "BASE_RECORD", CLEARED, DELETED, DETAILS,
    DURATION, "EDIT_DATE", "END_DATE_TIME", EVENT, "EXTERNAL_ID",REGARDING
FROM ACTIVITY;

 

// below link the above 2 table , using activityid and companyid.

SQL SELECT *
FROM "COMPANY_ACTIVITY";

 

Copper Contributor
Posts: 84
Country: Singapore

Re: Access Activity info using OLEDB2 access 2011 ACT

Below is the data structure of ACT. I have problem now the activity is not link to company , pls advise me how to link them.

 

Paul

Nickel Elite Contributor
Posts: 937
Country: USA

Re: Access Activity info using OLEDB2 access 2011 ACT

you posted earlier:

 

Pls take note that my data base is created without using companies. meaning each contact have indivdual company name.

 

I missed this note otherwise I would have understood what you are saying - that you don't actually have any company records you just have a company name on the contact record.  just replace company in the below statement with contact - you want to link to the contact record and order by contact.company field.

 

 

Copper Contributor
Posts: 84
Country: Singapore

Re: Access Activity info using OLEDB2 access 2011 ACT

Hi Allen Many thank for your help during those time I need. Now it work fine. So I must said you are a very good staff in ACT. Paul