08-04-2009 03:35 AM
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
08-10-2009 03:48 AM
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
08-10-2009 08:52 AM
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
08-11-2009 12:35 AM
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
08-11-2009 08:56 AM
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
08-11-2009 12:09 PM
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
08-12-2009 01:14 PM
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
08-12-2009 08:21 PM
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:
Hope this helps.
Bill Blakey
ACT! Development Team
Sage Software
08-24-2009 07:44 AM
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