Link to home
Start Free TrialLog in
Avatar of richardhubbard
richardhubbardFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Help needed with MS Access, VBA, call API using SOAP webservices

I need some help with getting some APIs to work with MS Access and using

In the visual basic window I have selected Tools and Webservices

Imported url http://api.connector.com/api.asmx?wsdl

Which gives  15 class modules including
clsws_API
clsof_Factory_API
struct_ListAddressbooks

etc etc

The correct references have been added for SOAP and XML.

When I run

Public Sub testAPI()
Dim var As Variant
Dim ExampleVar As New clsws_API

var = ExampleVar.wsm_ListAddressBooks("apiuser-4e1a5b439342@gmail.com", "changsha00")
Debug.Print var

End Sub

Or

Public Sub testAPI()
Dim ExampleVar As New clsws_API

Debug.print  ExampleVar.wsm_ListAddressBooks("apiuser-4e1a5b439342@gmail.com", "changsha00")

End Sub

It returns something (sorry this is where my understanding of the process ends) but is the wrong type for either debug.print or the variant var. (runtime error 13  Type mismatch is what is reported.)

I have been told by the company, (who say they do not support VBA, )  
The var variable is actually an object so it will need splitting up before being output and wont return through the Debug.Print

I am also working off a document found at http://www.ctt.org/documents/api_amended_cttmail.pdf

Is anyone able to help with taking whatever is returned by the examplevar.wsm_listaddressbook(,) to use it further in the code.
Avatar of richardhubbard
richardhubbard
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Many thanks for your help with this.  I will make a note of your comments and re-post with different detail and information.  This is the first time Expert Exchange has not been able to provide a solution so I am doing very well. Excellent service for which I very grateful, it has got me out of many holes.

RIchard
Avatar of puppydogbuddy
puppydogbuddy

Richard,
Just for kicks, I took a look at this:
  firstly, the url you posted is incorrect.  The url should be:
        http://apiconnector.com/ 
   secondly, you did not provide any backround info as to what you are trying to accomplish by using this site.
   thirdly, note the following info posted at the site:
APIConnector for integration with our email marketing system. You may only access this system when the account you are using has been granted API permission. Furthermore, you may not use your main account - a managed login must be used.

HTH
See comment above, which I would like to follow through to see if we can find the solution.
Greetings HTH/puppydogbuddy  (please correct me if I have address you incorrect)

Many thanks for having a look. I do appreciate your time with this issue.

The site provides an emailing function which enables our charity to track how email are opened, links clicked, forwarded unsubsribe etc etc.

The API will allow our MS Access database to interact with the site, sending and receiving information directly. Sorry if I am stating the obvious.

The link http://api.connector.com/api.asmx?wsdl enables me to install the class modules. When I try the same process using the URL http://apiconnector.com/    the webservices are not found. I assume setting up the webservices for .NET are different to VBA in MS Access.

The username and password I have included in the example are the username and password provided by the site as directed by the instructions. When I run the code (I have attached a zip of the mdb file I have been using to test the process) it does not reject them, it is what ever is returned that causing the error statement.

For information I use almost exactly the same code with another site and that works fine. So I know the process does work, but for what ever reason this site is returning something different and there is type mismatch error.

I hope this provides a some more information to help bottom the issue.

Many thanks again I hope it gives the required "kick".

Richard


apitest.zip
Hi Richard,
I downloaded your file. Unfortunately, I only have Access 2000 and can't read your file because it must be version 2003  or later.  All may not be lost if you can either, re-send in 2000 Format , or post your  code.
1. how does the code differ from the code that works at your other site?
 2. wat type of data is supposed to be received and returned from the site?
3. csn you go to the code editor; then select Tools>references from the Access command menu. Any references missing?  Compare references to references of working site.....any different?
Greetings

Thanks again for your time and quick response.

Attached is the same file in Access 2000. (converted from 2003, to I trust it works ok for you.)

Re the other site, there are some differences, but for the most part the code appears exactly the same.  

With respect to the type of data, it returns either strings or boolean results which are accepted by the VBA code in the programme.
The link below runs through the code running. Not sure how helpful this will be
http://goview.com/?id=9306fe4d-122c-4c1e-81c5-c370402e0829

Re References. - None missing. The code in apitest works fine, but hits the error when it tries to use the response that is returned by the site.

I have also attached the Word Doc I used when setting up this site.

It is 11pm here (Newcastle UK) so I will not able to post again until tomorrow.

Thank you again for having a look at this.  I am sure it is worth far more than 500 pionts, but I don't know how to offer any more.

Richard

apitest2k.zip
NetworkCreator-Services.doc
Richard,
It appears that your problem is that the mssoap30.dll has not been entered into your windows registry via regsrvr32. Here is how things went down on my computer.

The dll would not initialize (load) and therefore, could not be entered into my windows registry ( via regsvr32.exe.  Since you have Office/Access 2003, you should be able to register the dll.  If you get the dll registered, I think that will solve your problems.See the sequence of events that led up to this point.

Uploaded your file unto my computer ok, but could not compile the following line: <<<Private sc_API As SoapClient30>>>  due to missing reference library: Microsoft Soap Type Library v3.0

I was able to go to the internet and download Version 3.0 (mssoap30.dll), which must be some kind of updgrade that came with Office 2003, not with Office 2000. Got everything compiled.

Tried to execute your code via the command buttons on your form.  Got a runtime error on this code line contained in the procedure   Public Function ListCampaigns() As Variant
:
>>>>Set p = x.wsm_ListCampaigns("apiuser-4e1a5b439342@gmail.com", "changsha00")
Runtime error (-214722105(800401f3y:
WSDL service                            Invalid string
WSDL reader                              invalid string
Client                                           invalid string
the progID ms soap generic custom mapper3.0 is invalid

Based on the above, it appeared that the mssoap30.dll just needed to be entered into the windows registry. When I tried to register the dll via regsrvr32.exe, I got the following message:
       dynamic link library initialization routine failed.
I think it failed because the dll is an Office 2003 dll  that is not valid with Office 2000.



                       
Greetings
I ran regsvr32.exe and registered mssoap30.dll.

When I ran the function, it returned error 424, object required. Which is the same as I have got previously.

When I step through the code, it appears to be fine, until MS Access tries to "read" what ever is returned. (Not sure if I am using the correct language) and that is when the error occurs.

What is the best way to check the dll has registered correctly?

Thank you again for your help with this.  It is really strange the API to another service works fine and their code is almost identical. The only difference is where the user name and password is used.

Any other thoughts and suggestions would be gratefully received.

Richard

Hi Richard,
Error 424 usually means that a declared object variable was used without initializing it properly via a set statement.  In reviewing the code that the error seems to apply to (your set statement for ListCampaigns -see below), ListCampaigns is different then your set statement for getCampaign.  ListCampaigns is missing the third argumemt that appears in getCampaign.  Correct and re-run.

Public Function ListCampaigns() As Variant>>>>>>>>>>>>>>>has error
Set p = x.wsm_ListCampaigns("apiuser-4e1a5b439342@gmail.com", "changsha00")

Public Function getCampaign() As Variant>>>>>>>>>>>>>>>>>>appears to be ok
Set p = x.wsm_GetCampaign("apiuser-4e1a5b439342@gmail.com", "changsha00", 2)

My understanding of the code this that the ListCampaigns returns an array with all the Campaigns held in the system. Therefore a third arguement is not required.

GetCampaign returns information about a particular campaign. I have realised there is no Campaign 2 in our account, there is a 51190. Also note the "On Error" if taken out returns the same error statement,
Hold everything.
I have just run the code again to check the error statement and guess what
It works.  The HTML of the campaign is returned. (and other information if the p. statement is changed.
I am sure I did exactly the same thing just a few minutes ago and and it did not work.

However, going back to ListCampaigns that is still returning "Object Not Set", but given GetCampaign is working, I guess it now a case of working out how to make the right declaration - struct_APICampaign I guess is not the right one.  

I will keep you posted, but happy to allocate the points because clearly something has happened which has sorted something that was not working before.

Thank you so much for your time. But if you can see anything else, please let me know.

Richard

Greetings

A final question I hope. If my assumption is correct and the ListCampaigns returns an array, how should I display the results in VBA.  Arrays is not something I have used much, so the obvious is not obvious to me at this stage.
Many thanks

Richard
ASKER CERTIFIED SOLUTION
Avatar of puppydogbuddy
puppydogbuddy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Final Greetings

Thank you for your help through this.  Not quite got the array sorted, but I hope I can get that from the company now I have the basics running.

Points awarded.

TDH                                (That did help - I do feel a bit of a  . . . .)
Many thanks for your help. Not quite got to the solution I need, but enough there to see the end in sight.
TDH
Hi Richard,
Glad I was able to get you to the point that you have the basics running. In looking over the word doc you sent over, it appears that some of your arrays are field arrays, where each array element contains a value for a field like name, address,  etc....so your array looks like the following:
(John Doe, 320 Australia Way, etc) .  If that is the case, you may need to parse the array to obtain, for example, all the names.   If you do need to parse the array, see this link to an excellent parsing function tha  you can use>>>>> http://allenbrowne.com/func-10.html

HTH (LOL).  Thanks for the points and grade.  
Hi
The code from AllenBrowne looks a really useful.  I have not tried it yet, but any attempt I have made to put the output from the ListCampaign function into any variable in Access has failed - include declaring a variant. The variant accepts the output but then I have not been able to do anything, with it.

I shall try the above first but need to leave my computer for a couple of hours.

May thanks again for all your help with this

Richard