OLEDB Contact query

[JoeBo]

Hi

I am using the code below to retrieve contact information from Act contacts, this works but is really slow on the network.
I have downloaded the ACTREADER and am using this successfully to return lists of opportunities which run really quickly and would like to do the same with the contact details, but cannot find the details.  What oledb query would return this information?

ActFwk.LogOn("\\Mypath", "Administrator", "MyPassword")
          
 Dim cContact As Contact
            Dim cField As ContactFieldDescriptor
            Dim cList As Act.Framework.Contacts.ContactList
            Dim ContactG As New Guid(ContactID)
            Dim Guids(0) As Guid
            Guids(0) = ContactG

            cList = ActFwk.Contacts.GetContactsByID(Nothing, Guids)

            cContact = cList(0)

            cField = ActFwk.Contacts.GetContactFieldDescriptor("TBL_CONTACT.BUSINESS_EMAIL")

regards

JoeBo

[vivek]

Hi Joe,

Maybe this simple Select statement might be what you are looking for?

SELECT Contact.[Contact ContactID], Contact.[Contact Business_Email]FROM VRP_Contact

 HTH

Vivek 

[JoeBo]

Hi Vivek

Thanks for the reply to my question, I tried your suggested query bt get the errror:

System.Data.OleDb.OleDbException was caught
  ErrorCode=-2147217900
  Message="The multi-part identifier "Contact.Contact Business_Email" could not be bound.
The multi-part identifier "Contact.Contact ContactID" could not be bound."
  Source="Microsoft OLE DB Provider for SQL Server"
 

regards

Joe

[vivek]

Hi Joe,

Hmmm, sorry about that looks like that SELECT statement only works on the OLEDB provider views. Try:

Select  VRP_CONTACT.[CONTACT CONTACTID],  VRP_CONTACT.[CONTACT Business_Email]From  VRP_CONTACT

 HTH

Vivek 

 

[JoeBo]

Hi Vivek

I have tried the qry string without success I get the following!

System.Data.OleDb.OleDbException was caught
  ErrorCode=-2147217900
  Message="Invalid column name 'CONTACT Business_Email'."
  Source="Microsoft OLE DB Provider for SQL Server"
 

Regards

Joe

[vivek]

Hi Joe,

Ok the error means we have got the field name wrong for the Business_Email field. If you check the name of the field you can just substitute it. The SQL statement itself is correct. e.g. the Field we want is User1 we would write

SELECT vrp_contact[contact user1] FROM vrp_contact

 HTH

Vivek 

 

[JoeBo]

Hi Vivek

Thanks for sticking with me on this issue, I am still getting errors:

System.Data.OleDb.OleDbException was caught
  ErrorCode=-2147217900
  Message="Invalid column name 'vrp_contact'.

Where do I get the schema for the vrp table?

Regards

Joe
 

[Bill]

Joe - I think you're mixing access methods here.  You mentioned the ACTREADER account, but some of the query examples are referencing the ACT! OLE DB Reporting Provider views that begin with "VRP" (View for Reporting Provider).  TWO different things that do not mix.

If you're using the ACTREADER account/login, you will simply pick the Microsoft SQL Server Provider (OLE DB or Native Provider) and supply the ACTREADER credentials.  You will have full read-only access to the base tables (TBL_).

If you're using the ACT! OLE DB Reporting Provider, you'll provide a PAD file and ACT! user credentials.  You will then have access to the Reporting Views that are prefixed with "VRP".

It's a little out-dated, but there is a KB Article (22924) that contains a Data Dictionary of the base schema:

http://kb.sagesoftwareonline.com/cgi-bin/sagesoftwareonline.cfg/php/enduser/std_adp.php?p_faqid=2292...

Hope this helps. 

[JoeBo]

Hi Bill

I have only just returned from holiday and read your post, thanks for the lead I have been able to get the results I wanted.

Regards

joe