none
How to migrate wsdl from vb to vba? RRS feed

  • Question

  • I am trying to add a module to Access 365 to check FedEx package delivery dates, which will then be incorporated into the company's main Access program. FedEx has sample code downloadable from their Developer Resource Center for Visual Basic, which i downloaded (and fixed according to their notes) and it runs well. So, i know have working VB code.

    Step two is to move it into Access (new database, for now), in the form of a VBA module. The sample VB code has a Web Reference, which seems to point to the provided wsdl file (it points to a D drive that i'm guessing was on the developer's computer?, but there is what seems to be the relevant wsdl file in the project subdirectories) , so i guessed this is what i wanted in VBA as well. However, while there is a way to add a reference, there does not seem to be a way to add a web reference, nor does the add reference dialog list wsdl in the file types drop down. I do not know what to do next.

    On a re-read, this whole thing sounds confusing. Perhaps that reflects my own confusion on the matter. How do i add support for a web service when all i have is a WSDL file?

    Thursday, June 13, 2019 5:52 PM

Answers

  • I had to do something similar for Salesforce to Excel.  VBA is an old language and doesn't support a reference to a WSDL.  What I ended up doing is writing a C# .NET Core 2.0 program with WSDL reference that gets the data from Salesforce and saves it to a database.  I run the program from VBA and then Excel VBA creates a report.  The other option I looked at and got working was writing a C# .NET COM DLL.  You add a reference to the DLL in VBA and  call it.  Took a few weeks to figure it out.  This works well if you have a small amount of info to get on each call.  Found a book an old book 'COM and .NET Interoperability by ANDREW TROELSEN'  that really helped.  I think it was chapter 11 and 12.
    Friday, June 14, 2019 12:59 PM

All replies

  • I had to do something similar for Salesforce to Excel.  VBA is an old language and doesn't support a reference to a WSDL.  What I ended up doing is writing a C# .NET Core 2.0 program with WSDL reference that gets the data from Salesforce and saves it to a database.  I run the program from VBA and then Excel VBA creates a report.  The other option I looked at and got working was writing a C# .NET COM DLL.  You add a reference to the DLL in VBA and  call it.  Took a few weeks to figure it out.  This works well if you have a small amount of info to get on each call.  Found a book an old book 'COM and .NET Interoperability by ANDREW TROELSEN'  that really helped.  I think it was chapter 11 and 12.
    Friday, June 14, 2019 12:59 PM
  • Thank you for the reply.

    Calling the DLL sounds better to me, though it isn't my decision. Going with this idea, maybe it's easier to just call it as an exe, as pass information back and forth via a text file.

    Friday, June 14, 2019 1:29 PM
  • In addition to mogulman's answer:

    I would use the executable with job request/job result tables. Then I would handover a job ID and let the executable read the necessary data from your database and write the results back.

    Friday, June 14, 2019 1:36 PM
  • I'm going to ask the other guy what he wants to to do with his program. I think we can use one of the options mentioned here. Thank you all.
    Monday, June 17, 2019 10:08 AM
  • It was decided to connect to the database. So, i created a data source via View->Other Windows->Data Sources  Add New->Database->Dataset, but i am not really sure how to use it.

    I quickly typed in a test of:

           Dim cc As New xxxDataSetTableAdapters.yyyTableAdapter
            For Each row As DataRow In cc.GetData
                MsgBox(row.ToString)
            Next

    Upon execution, the For Each throws an exception:

    Name Value Type
    $exception {"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."} System.InvalidOperationException


    The access 365 database being connected to is 32 bit. I installed the 2016 32-bit executable and restarted visual studio, but that did not seem to help.

    How do i connect to the database?

    Wednesday, June 19, 2019 5:32 PM
  • Checking Data Platform Development > ADO.NET Managed Providers i ran the powershell command: 

    (New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION

    Even after rebooting (and receiving the same error) the output is:

    SOURCES_NAME               SOURCES_DESCRIPTION
    ------------               -------------------
    SQLOLEDB                   Microsoft OLE DB Provider for SQL Server
    MSDataShape                MSDataShape
    ADsDSOObject               OLE DB Provider for Microsoft Directory Services
    Windows Search Data Source Microsoft OLE DB Provider for Search
    MSDASQL                    Microsoft OLE DB Provider for ODBC Drivers
    MSDASQL Enumerator         Microsoft OLE DB Enumerator for ODBC Drivers
    SQLOLEDB Enumerator        Microsoft OLE DB Enumerator for SQL Server
    MSDAOSP                    Microsoft OLE DB Simple Provider

    So, it would seem the reason for the failure is the provider is not installed. Assuming that is true, i just need to figure out why.

    ---

    Edit: Finally understood step 4. VB.Net needed to be set to output a 32-bit executable. Now it works. So strange.
    Wednesday, June 19, 2019 7:20 PM