Community
Showing results for 
Search instead for 
Do you mean 
Reply

Connecting ACT to MS Access

Copper Contributor
Posts: 28
Country: United_Kingdom

Connecting ACT to MS Access

This post offers a solution to this problem which interests me greatly as I have a large Access platform system which currently uses Outlook as a simple CRM data source.  I'd like to use ACT as the new source of contacts.

 

The post above appears to offer a solution to the problem of the user selecting a contact in the Access platform solution and then wanting to look at ACT held data - for example letters sent, notes made, email activity and opportunities.  The post preamble suggests that it works by the Access system passing a contactID to a service (WCF) which is running and is started via an ACT plugin which then receives this contactID and, via a lookup, displays the ACT contact record.  So the user is seeing Access platform data (say accounting or order detail) along side (but in separate windows) the ACT data.  Change the contact in Access and ACT magically displays the new contact.  This would be really impressivel.

 

But -- 

(a)  This looks like WEB ACT - WCF is a web service so is it true this approach will only work when ACT is run in a browser ?  Does this means that this solution would not display the contact in a desktop form but in a browser ?

(b) The final post on this thread revealed that the poster could not make it work.  Is the approach here (i.e using WCF ) considered to be  valid ?

 

Are there any other ways of achieving a seamless link between ACT and Access - for example loading the contact ID in Access into the Windows clipboard and then (automatically ?) using this in the lookup ? 

 

Any help to understand the issues assocaited with the post solution ( or any other solution ) would be appreciated. Thanks.

 

Silver Super Contributor
Posts: 2,328
Country: USA

Re: Connecting ACT to MS Access

I think in the very last post he said he got it working.  Using WCF is a valid method for doing it and it doesn't require the ACT! for Web version.  While WCF can be used to communicate over the web it can also be used on a local machine and can even be used inside a single program to facilitate communication between two different routines in the program.  The beauty of it is that if you set it up properly it doesn't matter where the client and the server are.  It will work within a program, on the local machine or over the internet.  Take another look at his sample code.  I'm confident that you can get it working to do what you want to do.  Using WCF is probably the best approach although not necessarily the easiest or quickest.  It is much better than using marshalling which is the easier quicker way for most people since it's been around for a while.  You might want to check some of the other methods that you can use in WCF for communication protocols depending on where the Access database resides.


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!
Copper Contributor
Posts: 28
Country: United_Kingdom

Re: Connecting ACT to MS Access

Thank you for your re-assurances and for confirming it will work with desktop ACT.  That's good.

 

I hope you can help with a few questions ?

 

What is the significance of the  values for MEX_ADDRESS,  WEB_ADDRESS  and NAME_SPACE ?  I.e http://localhost:7892:mex etc.

 

What configuration changes, if any,  need to be made to the Desktop install of ACT  -

 

The only examples I can find for WCF relate to its use in a SOAP style situation - i.e  retrieving data via a webservice.  Where can I look for some information or examples of the non-web use of WCF ?

 

The last post ( not by the original poster ) shows he hit problems which may have remained unsolved. I can see that happening to me if I simple copy this code and try and run it without really understanding what its doing.  WCF seems a poweful methodology and I'm keen to get to grips with it  but I'd like to acquire a little more understanding. Hence the questions above !

 

The .Net code runs as an addin.  I've never built an ACT addin - is there a simple example you can point me to showing how I take the sample code (when I understand what its doing ) and build the code file which ACT will recognise as an add in.

 

Thanks again for you help.

 

Silver Super Contributor
Posts: 2,328
Country: USA

Re: Connecting ACT to MS Access

I can try to answer them.  You probably want to invest in a good book on WCF coding though to really appreciate what it is.

 

The MEX_ADDRESS is the address of the MEX (meta data exchange) endpoint.  You can sort of think of it as a phone extension at an office.  You can call the office but that doesn't get you to the person that you want to talk to.  Once they switch you to that persons extension you can establish a conversation.  You can assign your own port numbers.  You just need to use a port that isn't being used for anything else currently.

 

He doesn't actually use WEB_ADDRESS it is WCF_ADDRESS.  In this case he is using that constant for the address of the net.tcp endpoint.  He probably could have called it WCF_TCP_ADDRESS or something similar to make it clearer.  You can actually have several different types of services running and listening for your conversation simultaneously.  If we go back to the office metaphor you can call the person you want to talk to or you could fax them or you could email them.  You choose the method that works best for the information that you want to relay.  For example you might need to fax them a contract so you'd use the fax endpoint.

 

The NAME_SPACE constant references tempuri.org which is a default namespace used by Microsoft in their Visual Studio product to make sure that the web service (in this case) namespace is unique.  To go back to the office metaphor you would might have a namespace reference for IBM.  When you call IBM and ask for Jack at extension 7892 it would be entirely different that calling ABC and asking for Jack at extension 7892.

 

You can use SOAP even though you are running on a local machine.  There are a lot of examples on the web using WCF.  The original poster used TCP in his program.  I don't want to recommend one off hand since I don't have an example handy that I know works but Microsoft has some in the help files for Visual Studio that will probably work fine.

 

I'm pretty sure the original poster got it working.  I think he sent me a personal email saying he had.

 

There are quite a few examples in this forum of addins that work with ACT!.  There are also several included with the SDK.  I haven't tried any of them recently so they might have to be updated to work with ACT! 2013.  ACT! 2013 uses .NET 4.0 which required that most addins be updated.

 

This is a pretty challenging project for your first one with ACT!.  You might want to get in touch with someone over there who you can spend a little time with to get some training.  I'm pretty sure Vivek would be happy to help you out.  His time would be billable of course but it would be well worth the investment.  I'm guessing it will take you several days to get this working on your own and several hours to get it working with Vivek's help.  He works at www.caldere.com.

 

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!
Bronze Elite Contributor
Posts: 2,546
Country: New_Zealand

Re: Connecting ACT to MS Access

Hmmm complex.

Why not have a .dll that exposes itself as a COM component then Access can work to that directly. 

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


Copper Contributor
Posts: 28
Country: United_Kingdom

Re: Connecting ACT to MS Access

Thanks for your input.  This is currently speculative work as I have no ACT client at present - but 2 clients who could benefit from an Access-ACT linked solution.  The recommendation towards paid help would certainly appeal if this was part of income generating work.

 

I hadn't realised WCF is a VS "component" so I'm now using those unthumbed pages of my VS books to learn more about WCF inspired by the introductory tuition from this post.  My plan is WCF and ACT plugin as two separate exercises and once I have mastered these separately then to look at combining them.

 

I already have an Access Managed Add-in  (.tlb - the .dll for some reason is not accepted as an Access Reference) which allows me to create new contacts.  But the WCF route is prompted by something more sophisticated -  clicking an Access button retrieves the ContactID for the ACT displayed contact so the user sees ACT data and external data related to the displayed contact side by side. To do this you need some communication mechanism between ACT and Access.  So far the only offering here is WCF.

 

Thanks again to everyone for their help so far.

Silver Super Contributor
Posts: 2,328
Country: USA

Re: Connecting ACT to MS Access

There are lots of ways to do what you want to do.  Using WCF is simply the best way to do it.  Basically what you do is create a plugin in ACT! that starts up the WCF listener service.  Then any application including your Access application can talk to it.  You can create the WCF listener service so that it supports multiple types of communications allowing you to access the ACT! database from the same pc, across the network or across the internet.  If you didn't want to go to that much trouble you could simply create a plugin in ACT! that wrote the current contact id to a text file every time the contact changed.  Another option would be to write a plugin that used IPC to communicat.  Another option would be to use marshalling.  There is actually a marshalling sample in some of the older SDK documentation.  I don't think that it worked without some modification but I did manage to get it working when I played with it a while back.

 

I've toyed with the idea of selling a listener service as an addon but I haven't done it because anyone that was capable of writing the client part of the service is capable of writing the server part of the service.  I suspect I'd sell about a half dozen copies and that would be about it.  :-)

 

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!
Copper Contributor
Posts: 28
Country: United_Kingdom

Re: Connecting ACT to MS Access

Thanks Stan for your continued help.

 

Yes I'll look at these other methods but I think WCF is the way to go and I can see benefits in getting to grips with this as I'm sure this is the way forward - not just for ACT but there is an increasing need in Access projects to use .NET technology to cope with requirements where VBA is not quite up to the mark.  Thanks again.

Silver Super Contributor
Posts: 2,328
Country: USA

Re: Connecting ACT to MS Access

It's more than that.  It's actually the way forward with .NET programming in any client / server scenario.  It's a truly elegant evolution of the model.

 

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!