Community
Showing results for 
Search instead for 
Do you mean 
Reply

SDK and VBA

Accepted Solution Solved
New Member
Posts: 7
Country: Canada
Accepted Solution

SDK and VBA

My customer has a MS Access database that stores detailed information about customers, shipments, contracts, etc. There is one simple requirement: to click a button on the Access form, and have Act 2011 (which is running in the other window) sychronize with the contact currently active on the Access form.  The Access database stores the contactID for the Act Contact, so the lookup itself should be a trivial matter.

This has actually already been completed by a prior consultant for the existing Act 2000 version which they want to phase out.  They wish to complete the upgrade to Act 2011, and this link from Access to Act is a must-have.  Quite trivial, in Act2000:

 

   Set objApp = CreateObject("ACTOLE.APPOBJECT")
   Set objViews = objApp.Views
   Set objContactView = objViews.Create(1, "CL")

   objContactView.CreateLookup Me.ActID

I have been fumbling and stumbling all over this forum and the rest of the internet trying to get some actual DOCUMENTATION on the Act 2011 SDK, and there doesn't appear to be anything useful.  Somehow or other, I'm sure that there's a simple function call to navigate the Act 2011 UI to a specfic contact, but from what I've been able to gather, only a DotNet application will be capable of utilizing the SDK. I've tried to add a reference in the VBA code to Act.Framework.dll, and it barks at me.  Of course, it's entirely possible that that is not the correct dll, but I have no way of knowing...

My question is: Can this be done from VBA, within Access, which is obviously not Dot Net?  Do I need to write a DotNet app in Visual Studio, then call THAT app from within VBA, or am I missing something pathetically obvious?

Thank you in advance for any guidance you can provide.

Kevin


Accepted Solutions
Solution
Accepted by topic author kchester69
‎09-25-2015 03:20 AM
Silver Super Contributor
Posts: 2,328
Country: USA

Re: SDK and VBA

Yes it can be done.  There is an old sample application in the 2005 or 2006 documentation that shows one way to do it.  Basically you set up interprocess communication by writing a plugin that listens for a request from your application.  That request can be simply what is the name of the currently open ACT! database or you could use it to retrieve whatever other information you wanted by putting routines in it to accept a ContacID from you and pass you back that contact's information.  I recommend using Windows Communication Foundation to do it if you are using ACT! 2010 or later.  The sample in the documentation obviously doesn't use WCF it uses .net remoting.

 

Stan


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!

View solution in original post


All Replies
Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: SDK and VBA

Employee
Posts: 1,163
Country: USA

Re: SDK and VBA

Hello kchester and welcome to the community.

 

The documentation for the 2011 SDK can be found here. Fill out the form and click submit and and you can select any version of the SDK documentation to download from 2005 to the current version. I think the architecture guide will help answer a lot of question you have regarding how to go about integrating with Act.

 

Mike it correct,  you'll be creating a .net project to accomplish this.

Matthew Wood
Act! SDK Support
Community Moderator
New Member
Posts: 7
Country: Canada

Re: SDK and VBA

Ok, I'm really getting bogged down here. 

 

So, to reiterate:

 

a) User is interacting with an MS Access application.  At certain points they want to click a button on the Access form and have the currently running Act 2011 Application Contacts Window "find" the contact record that matches the contact currently displayed in the Access application.  This will be accomplished with the unique "ContactID" field of the Act database, which is also stored in the Access database for this precise purpose.

 

b) Because Access is not a .Net development environment, I need to build a .Net app that issues the command (whatever that might be) that causes the UI to "find" a contact record.  This app, then, will be called by the VBA code in the Access application, which will obviously have to pass the ContactID as a parameter.

 

I created a .net app for this purpose using Visual Studio.  Of course, there's no documentation for the SDK, so I've had to go on a little Easter Egg hunt for code snippets that I can piece together. 

 

Logically, the first thing my .net app needs to do is get a reference to the currently running Act Application Instance.  A little hunting and pecking led me to the posting "must be simple" (http://community.act.com/t5/Sage-ACT-Developer-s-Forum/must-be-simple/m-p/80425), specifically poster mwood's comment that "Framework applications do not have access to the currently running ActApplication".

 

Sounds like a show-stopper to me.  I therefore ask: is what I'm attempting to do even possible?

 

Thank you one and all for any assistance you can provide. 

New Member
Posts: 7
Country: Canada

Re: SDK and VBA

Thank you mwood for the link, this is the documentation I was looking for.  I was given the architecture reference by someone, and I thought that was all there was. 

 

My earler question still stands though: is what I'm attempting to do possible?

 

Thanks,

Kevin

Solution
Accepted by topic author kchester69
‎09-25-2015 03:20 AM
Silver Super Contributor
Posts: 2,328
Country: USA

Re: SDK and VBA

Yes it can be done.  There is an old sample application in the 2005 or 2006 documentation that shows one way to do it.  Basically you set up interprocess communication by writing a plugin that listens for a request from your application.  That request can be simply what is the name of the currently open ACT! database or you could use it to retrieve whatever other information you wanted by putting routines in it to accept a ContacID from you and pass you back that contact's information.  I recommend using Windows Communication Foundation to do it if you are using ACT! 2010 or later.  The sample in the documentation obviously doesn't use WCF it uses .net remoting.

 

Stan


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!
New Member
Posts: 7
Country: Canada

Re: SDK and VBA

This is hopelessly complicated and I give up.  It's a shame there's not some real examples to help newbies figure out how all this works.  Thanks to those of you who've thrown a couple of morsels of info my way, alas not nearly enough to get me off the ground.

Bronze Elite Contributor
Posts: 2,546
Country: New_Zealand

Re: SDK and VBA

Kevin,

I suggest you take a look at our  Web 2.0 API for ACT!  as described in this community.

Using an API there is no requirement to have a working knowledge of ACT!'s SDK to achieve what you want, if you or a developer you choose, has a reasonable knowledge of XML and HTTP Web requests that is all that is needed.

 

Note that while the API is 100% functional, it is in beta as  we await release of new license SKU from Sage ACT! so that use of the API is compliant with the Sage ACT! EULA.   

Graeme Leo
Xact Software - consultants and developers
Follow us on Twitter and check out our Blog


New Member
Posts: 7
Country: Canada

Re: SDK and VBA

I found an acute shortage of examples of how to utilize the Act! SDK, and specifically, no examples of accessing it via VBA code, such as from Excel or Access.

 

Here's what I've learned about the process, I hope it saves someone else a week's worth of time, because that's about how long it took me to piece all this together.  Note that my client's need is a little odd, I'm not sure why they maintain contacts in both Act! and Access, but they do, and that was my project.  Obviously, these techniques can be utilized to integrate Act! and VBA for less trivial exercises.

 

1)      An Act! Plugin, written in VB.Net, which hosts a wcf service:

Imports System.Windows.Forms
Imports System.Drawing.Design
Imports System.Drawing
Imports System.ComponentModel.Design
Imports Act.Framework
Imports Act.UI
Imports System.ServiceModel
Imports System.ServiceModel.Description

Public Class ActClass
    Implements IPlugin
    Const SERV_ADDRESS = "net.tcp://localhost:7891/Service/"
    Const MEX_ADDRESS = "http://localhost:7892/mex"

    Private Shared ActApp As ActApplication
    Private oServiceHost As ServiceHost = Nothing
    Private lServCreated As Boolean = False

    Public Sub New()
    End Sub

    Public Sub OnLoad(ByVal App As ActApplication) Implements IPlugin.OnLoad
        ActApp = App  'Store a reference to ActApplication
        AddHandler ActApp.ViewLoaded, AddressOf ActApp_ViewLoaded
    End Sub

    Private Sub ActApp_ViewLoaded(ByVal Sender As Object, ByVal e As Act.UI.ViewEventArgs)
        'Start the WCF Service so that it is available to calling apps
        Create_Service()
    End Sub

    Public Sub OnUnLoad() Implements IPlugin.OnUnLoad
        '//
    End Sub

    Public Shared Function GetContactData() As String
        'Returns details of the contact currently active in Act!
        Dim strContactInfo As String = ""
        Dim curContact As Act.Framework.Contacts.Contact

        Try
            curContact = ActApp.ApplicationState.CurrentContact

            'Since it's a nightmare trying to pass arrays from vb.net to vba,
            'instead we'll return a tab delimited string.  The calling application
            'is responsible for parsing out the individual fields.
            With curContact
                strContactInfo = .ID.ToString & vbTab & .Company & vbTab & .FullName
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.BUSINESS_LINE1", True)
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.BUSINESS_LINE2", True)
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.BUSINESS_CITY", True)
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.BUSINESS_STATE", True)
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.BUSINESS_POSTALCODE", True)
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.BUSINESS_COUNTRYNAME", True)
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.BUSINESS_PHONE", True)
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.FAX_PHONE", True)
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.ALTERNATE_PHONE", True)
                strContactInfo = strContactInfo & vbTab & .Fields("TBL_CONTACT.MOBILE_PHONE", True)
                'strContactInfo = strContactInfo & vbTab & .Fields("Contact2", False)  <-Not sure
            End With

        Catch ex As Exception
            MsgBox(ex.Message)
            strContactInfo = ""
        End Try

        Return strContactInfo
    End Function

    Public Shared Function LookupContact(ByVal strContactID As String) As Boolean
        'Reposition the UI to the ContactID specified
        Dim oGuid As Guid
        Dim contactKeys(1) As Guid
        Dim lSuccess As Boolean

        Try
            lSuccess = True
            oGuid = New Guid(strContactID)
            contactKeys(0) = oGuid
            Dim myRecordbyID = ActApp.ActFramework.Contacts.GetContactsByID(Nothing, contactKeys)
            ActApp.UIContactManager.ShowContact(myRecordbyID(0))

        Catch ex As Exception
            lSuccess = False
        End Try

        Return lSuccess
    End Function

    Private Sub Create_Service()
        Dim baseAddress As Uri = New Uri(SERV_ADDRESS)
        Dim smb As ServiceMetadataBehavior

        'Setup the WCF service to start responding to requests
        If lServCreated = False Then
            Try
                oServiceHost = New ServiceHost(GetType(WcfService.Service), baseAddress)

                'Check to see if the service host already has a ServiceMetadataBehavior
                smb = oServiceHost.Description.Behaviors.Find(Of ServiceMetadataBehavior)()
                If (smb Is Nothing) Then
                    smb = New ServiceMetadataBehavior()
                End If

                'We'll use tcp, so disable http
                smb.HttpGetEnabled = False
                smb.MetadataExporter.PolicyVersion = PolicyVersion.Policy15
                oServiceHost.Description.Behaviors.Add(smb)

                'Add MEX endpoint
                oServiceHost.AddServiceEndpoint(ServiceMetadataBehavior.MexContractName, _
                    MetadataExchangeBindings.CreateMexHttpBinding(), MEX_ADDRESS)

                'Add application endpoint
                oServiceHost.AddServiceEndpoint(GetType(WcfService.IService), New NetTcpBinding(), "")

                'Start answering the phone
                oServiceHost.Open()
                lServCreated = True

            Catch ex As Exception
                MsgBox(ex.Message)
                oServiceHost.Abort()
            End Try

        End If
    End Sub
End Class

<ServiceContract()> _
Public Interface IService
    <OperationContract()> _
    Function GetCurrentActContact() As String
    <OperationContract()> _
    Function LookupContact(ByVal strContactID As String) As Boolean
End Interface

Public Class Service
    Implements IService

    Public Function GetCurrentActContact() As String Implements IService.GetCurrentActContact
        Return ActClass.GetContactData()
    End Function

    Public Function LookupContact(ByVal strContactID As String) As Boolean Implements IService.LookupContact
        Return ActClass.LookupContact(strContactID)
    End Function
End Class

 

2)      Some VBA code that requests contact data from Act!, as well as requesting that Act! move to a different record:

Option Compare Database
Option Explicit

'We'll keep this object around for the duration of this form
Private objWcfService As Object
  
'Error Handling
Private Const AUTOMATION_ERROR = -2147221020
Private Const CONNECTION_CLOSED = -2147467259
Private Const SERVER_ERROR = -2146233087

'------------------------------------------------------------------------
Private Function WcfConnect() As Boolean
  'Wcf Service is running inside Act! Plugin to respond to events from VBA
  Dim strWcfSvcAddress As String
  Dim lSuccess As Boolean
  
  On Error GoTo ErrHandler
  lSuccess = True
  
  'Set up some constants to talk to Wcf service
  Const MEX_ADDRESS = """http://localhost:7892/mex"""
  Const WCF_ADDRESS = """net.tcp://localhost:7891/Service/"""
  Const NAME_SPACE = """http://tempuri.org/"""
  Const INTERFACE_NAME = "IService"
    
  If objWcfService Is Nothing Then
    'Create object pointing to our Wcf Service
    'Build connection string
    strWcfSvcAddress = "service:mexAddress=" & MEX_ADDRESS & "," & _
      "address=" & WCF_ADDRESS & "," & _
      "contract=""" & INTERFACE_NAME & """," & _
      "contractNamespace=" & NAME_SPACE & "," & _
      "binding=""NetTcpBinding_" & INTERFACE_NAME & """," & _
      "bindingNamespace=" & NAME_SPACE
    
    DoCmd.OpenForm "frmConnecting"
    DoEvents
    Set objWcfService = GetObject(strWcfSvcAddress)
    DoCmd.Close acForm, "frmConnecting"
  End If
  
ProcExit:
  WcfConnect = lSuccess
  Exit Function
  
ErrHandler:
  Select Case Err.Number
    Case AUTOMATION_ERROR, CONNECTION_CLOSED, SERVER_ERROR
      MsgBox "Unable to communicate with Act!" & vbCrLf & vbCrLf & _
        "Please make sure Act is running and that the Wcf plugin is available", _
        vbCritical, "Cannot communicate with Act!"
      Set objWcfService = Nothing
      
    Case Else
      MsgBox "Error " & Err.Number & " " & Err.Description, vbCritical, "Error communicating with Act!"
      
  End Select
  
  lSuccess = False
  Resume ProcExit
  
End Function

'------------------------------------------------------------------------
Private Sub cmdLookup_Click()
  Dim lSuccess As Boolean
  Dim strActID As String
  
  On Error GoTo ErrHandler
  
  If Not WcfConnect() Then GoTo ProcExit
  
  'Attempt to lookup this contact in Act!
  'Note that the GUID of the Act record is stored in the Access database
  strActID = Me.ActID
  lSuccess = objWcfService.LookupContact(strActID)
  
  If Not lSuccess Then MsgBox "Unable to find this contact in Act!", vbInformation, "Contact not found"
  
ProcExit:
  Exit Sub
  
ErrHandler:
  Select Case Err.Number
    Case AUTOMATION_ERROR, CONNECTION_CLOSED
      MsgBox "Unable to communicate with Act!" & vbCrLf & vbCrLf & _
        "Please make sure Act is running and that the Wcf plugin is available", _
        vbCritical, "Cannot communicate with Act!"
      Set objWcfService = Nothing
      
    Case Else
      MsgBox "Error " & Err.Number & " " & Err.Description, vbCritical, "Error during Lookup"

  End Select
  
  Resume ProcExit
End Sub

'------------------------------------------------------------------------
Private Sub cmdUpdate_Click()
  Dim strContactInfo As String
  Dim arrContactInfo 'Will become array of strings
  Dim re As RegExp, strActID As String, varFindDupe As Variant
  Dim rst As DAO.Recordset
  
  On Error GoTo ErrHandler
  
  If Not WcfConnect() Then GoTo ProcExit
  
  'Return tab-delimited string containing contact information for active Act! contact
  strContactInfo = objWcfService.GetCurrentActContact()
  If strContactInfo = "" Then
    MsgBox "Unable to retrieve information from Act!", vbCritical, "Cannot retrieve data"
    GoTo ProcExit
  End If
  
  'Split string into composite fields, update Access fields accordingly
  arrContactInfo = Split(strContactInfo, vbTab)
  
  'Process each field in the array:
  'Yes, it's kludgy, but I couldn't figure out how to pass a more complex data type
  '(even an array) between vb.net and vba.

  Me.ActID = arrContactInfo(0)
  Me.Company = arrContactInfo(1)
  Me.Contact = arrContactInfo(2)
  Me.Address1 = arrContactInfo(3)
  Me.Address2 = arrContactInfo(4)
  Me.City = arrContactInfo(5)
  Me.Province = arrContactInfo(6)
  Me.PostalCode = arrContactInfo(7)
  Me.Country = arrContactInfo(8)
  Me.Phone = arrContactInfo(9)
  Me.Fax = arrContactInfo(10)
  Me.Phone2 = arrContactInfo(11)
  Me.Cellular = arrContactInfo(12)
  'Me.Contact2 = "" '<-not sure
    
ProcExit:
  Set rst = Nothing
  Exit Sub
  
ErrHandler:
  Select Case Err.Number
    Case AUTOMATION_ERROR, CONNECTION_CLOSED, SERVER_ERROR
      MsgBox "Unable to communicate with Act!" & vbCrLf & vbCrLf & _
        "Please make sure Act is running and that the Wcf plugin is available", _
        vbCritical, "Cannot communicate with Act!"
      Set objWcfService = Nothing
      
    Case Else
      MsgBox "Error " & Err.Number & " " & Err.Description, vbCritical, "Error during update"
      
  End Select
  
  Resume ProcExit
End Sub

'------------------------------------------------------------------------
Private Sub Form_Close()
  'Get rid of object
  Set objWcfService = Nothing
End Sub

 

 

My thanks to others on the forum for giving me the breadcrumbs to follow to piece this together.  I was going to attach files to this message, rather than pasting all that code in there, but it doesn't look like that's an available feature on this forum.

 

I welcome any suggestions or observations about this posting.  kchester shifted2 lethbridgeit unshifted> ca.

 

New Member
Posts: 6
Country: United_Kingdom

Re: SDK and VBA

Hi There,

 

Sorry for resurrecting an old thread, but I have just tried implementing this example, as I also need to communicate with ACT! from VBA.

 

I have taken both your code blocks, and used the first to create a project in Visual Studio Express 2010 (Visual Basic). I added all the references to the project until no errors were shown in VS (I also added 'MessageBox.Show("Loaded")' to the 'OnLoad' function for debugging purposes). I then built the resulting project and copied the resultant dll to [ACT Directory]/Plugins.

 

I put the second bank of code into a form created in MS Access (2003) and run the form, which also compiles without errors.

 

I then opened ACT! and clicked the 'Update' button in Access when ACT! had loaded and I had navigated to a contact. I noticed that my MessageBox had not appearred, and Access returned the 'Cannot communicate with Act!' message (due to AUTOMATION_ERROR).

 

From reading this thread http://community.act.com/t5/Sage-ACT-Developer-s-Forum/Sample-CustomMenuPlugin-IPlugin-not-found-New... I checked the XML file specified, and sure enough my newly created XML is in the list of 'Not Loaded' Plugins.

 

Being that I am coming at this with no ACT! experience, and very limited .Net experience, any pointers that I can get will be greatfully recieved!