Community
Showing results for 
Search instead for 
Do you mean 
Reply

ODBC Lookup from SQL Server, results on ACT form?

New Member
Posts: 9
Country: United States

ODBC Lookup from SQL Server, results on ACT form?

Hi, all. I'm a database developer with expertise in SQL Server and Access, but not ACT. First time anyone has asked me to do any ACT work. A client I have is using ACT and wants a very simple lookup on one of his contact forms from the organization's SQL Server. That is, he enters a person's primary key number (in the SQL db) as part of the contact record, and a read-only text box on the ACT form reflects some details from the person's record in the SQL Server. Can this be done as some sort of lookup control on the ACT form that has a SQL statement in it, referring the to the value of the ACT field?

 

Thanks for input. 

Moderator
Posts: 4,395
Country: USA

Re: ODBC Lookup from SQL Server, results on ACT form?

You can accomplish this using the ACT! Reader Utility, but it requires the Premium version of ACT!.

 

What is the ACT! Reader Utility

How To Create An ODBC Connection For Use With The ACT! Reader Utility 

ACT! Reader Utility Database Report Documentation 

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: ODBC Lookup from SQL Server, results on ACT form?

New Member
Posts: 9
Country: United States

Re: ODBC Lookup from SQL Server, results on ACT form?

Thank you for your thoughts on this. I'm not sure my goal was entirely clear. Looks like both the Reader Utility and the OLEDB solution are about access to ACT!'s back-end, and that's not what I'm about here. I'm not trying to read data from within ACT!'s SQL back-end. I'm trying to get a lookup from a separate, non-related SQL Server db to show up on an ACT! form based on a related field within the ACT! record (which is the primary key in a table in the separate SQL Server).

 

If the ACT! record has a numeric field called (let's say) person number, the query to the separate SQL Server would be something like:

 

SELECT person_status from Person where person_id = [this ACT! record's person number field]

 

Then show the person_status on a read-only text box on the ACT! form. 

 

Make sense?

 

Grateful for your thoughts and ideas.

 

--Paul 

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: ODBC Lookup from SQL Server, results on ACT form?

Ahhh... for that you would need to use ACT!'s SDK and add a tab to the ACT! layout that used ACT! SDK Events to determine the current contact and then read and display the appropriate information from your database.
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: ODBC Lookup from SQL Server, results on ACT form?

New Member
Posts: 9
Country: United States

Re: ODBC Lookup from SQL Server, results on ACT form?

That seems to be just what I was looking for. I'll give it a shot -- many thanks.
New Member
Posts: 9
Country: United States

Re: ODBC Lookup from SQL Server, results on ACT form?

Does anyone know the proper connection string to a SQL Server 8.0 DB? I know that this isn't quite right:

 

DSN=Membership Reporting;UID=nameofuser;PWD=pw;DATABASE=msr 

New Member
Posts: 9
Country: United States

Re: ODBC Lookup from SQL Server, results on ACT form?

Here's the answer: 

 

Provider=SQLNCLI10;Server=[server ip];Database=msr;Uid=[username]; Pwd=[pw]; 

New Member
Posts: 4
Country: South_Africa

Re: ODBC Lookup from SQL Server, results on ACT form?

Does anyone know which table to find the Record Manager for an Opportunity in the Act Tables. I can't seem to find it under:

  • TBL_Contact
  • TBL_Contact_Opportunity
  • TBL_Opportunity
  • TBL_Opportunity_ProductService
  • TBL_Product_Service

Which is what I am using to create an ODBC query to Excel for advanced Sales Reports. But the sales person for each opportunity is the Record Manager.