Community
Showing results for 
Search instead for 
Do you mean 
Reply

OLEDB Contact query

Tuned Listener
Posts: 23
Country: United Kingdom

OLEDB Contact query

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

Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: OLEDB Contact query

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 

 

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Tuned Listener
Posts: 23
Country: United Kingdom

Re: OLEDB Contact query

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

Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: OLEDB Contact query

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 

 

 

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Tuned Listener
Posts: 23
Country: United Kingdom

Re: OLEDB Contact query

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

Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: OLEDB Contact query

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 

 

 

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Tuned Listener
Posts: 23
Country: United Kingdom

Re: OLEDB Contact query

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
 

Copper Super Contributor
Posts: 138
Country: United States

Re: OLEDB Contact query

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. 

 

Bill Blakey
ACT! Development Team
Sage Software

Tuned Listener
Posts: 23
Country: United Kingdom

Re: OLEDB Contact query

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