Solved

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

Posted on 2009-05-19
21
1,767 Views
Last Modified: 2013-11-29
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.
0
Comment
Question by:richardhubbard
  • 10
  • 6
21 Comments
 
LVL 1

Author Comment

by:richardhubbard
ID: 24471539
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
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24503031
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
0
 
LVL 1

Author Comment

by:richardhubbard
ID: 24505280
See comment above, which I would like to follow through to see if we can find the solution.
0
 
LVL 1

Author Comment

by:richardhubbard
ID: 24505443
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
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24506207
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?
0
 
LVL 1

Author Comment

by:richardhubbard
ID: 24506587
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
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24509079
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.



                       
0
 
LVL 1

Author Comment

by:richardhubbard
ID: 24509679
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

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24509893
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)

0
 
LVL 1

Author Comment

by:richardhubbard
ID: 24510142
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

0
 
LVL 1

Author Comment

by:richardhubbard
ID: 24510344
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
0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 24510520
Richard,
With a zero array (like is used in your code) the results get passed thru the array one at time using a >>>for each / next loop.

          see this link for an excellent tutorial on manipulating arrays using Access VBA.
                   http://www.aspfree.com/c/a/Microsoft-Access/Advanced-Ideas-using-VBA/

From your post previous to your most recent post, it appears that registering the dll like I told you seems to have helped.  And now that you are reviewing the arguments/parameters that are required to be passed to each function, I think you will be able to resolve your problems.

HTH (this is not a name abbreviation;this stands for Hope This Helps)  
0
 
LVL 1

Author Comment

by:richardhubbard
ID: 24511630
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  . . . .)
0
 
LVL 1

Author Closing Comment

by:richardhubbard
ID: 31583093
Many thanks for your help. Not quite got to the solution I need, but enough there to see the end in sight.
TDH
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24512632
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.  
0
 
LVL 1

Author Comment

by:richardhubbard
ID: 24512723
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
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now